vb.net 2008 Express to SQL - Dataset does not show changes in DB

[gengel]

Good day, I have tried various methods to update an SQL database, but every time the database is updated but not the dataset. I have done it with addrow and accept changes and with execute no query, but the dataset does not register the changes made. I have to MAIN.data.DS.Tables.Remove("Orders") and then add the datatable back to the dataset with the data adapter: Example 1:

Function Add_Orders_Row() As Boolean
Try
Dim RowISAdded As Boolean = False
If txtQuantity.Text <> "" And txtProductionNo.Text <> "" _
And txtWeek.Text <> "" And txtPart.Text <> "" _
And cboProductCode.Text <> "" And txtPart.Text <> "" _
And txtWorksOrderNo.Text <> "" Then
Dim dt As DataTable = MAIN.data.DS.Tables("Orders")
Dim dr As DataRow = dt.NewRow
dr.Item("OrderStatus") = cboOrderStatus.Text
dr.Item("SamsungRef") = "NA"
dr.Item("ProductCode") = cboProductCode.Text
dr.Item("ProductSerialNo") = txtProductSerialNo.Text
dr.Item("ProductionNo") = txtProductionNo.Text
dr.Item("WeekNo") = txtWeek.Text
dr.Item("PartNo") = CType(txtPart.Text, Integer)
dr.Item("OrderDate") = dtpOrderDate.Text
dr.Item("QuantityOrder") = CType(txtQuantity.Text, Integer)
dr.Item("QuantityScanned") = 0
dr.Item("WorksOrderNo") = txtWorksOrderNo.Text
If txtFirstSerial.Text = "" Then
dr.Item("FirstSerialNo") = "<SCAN>"
dr.Item("LastSerialNo") = "<CALC>"
Else
dr.Item("FirstSerialNo") = txtFirstSerial.Text
dr.Item("LastSerialNo") = txtLastSerial.Text
End If

            dr.Item("timenow") = Date.Now
            dt.Rows.Add(dr)
            'Update new row to database
            Dim qry As String = "SELECT \* FROM Orders"
            If MAIN.data.Conn.State = ConnectionState.Closed Then MAIN.data.Conn.Open()
            Dim da1 As New SqlClient.SqlDataAdapter
            da1.SelectCommand = New SqlClient.SqlCommand(qry, MAIN.data.Conn)
            Dim cb1 As SqlClient.SqlCommandBuilder = New System.Data.SqlClient.SqlCommandBuilder(da1)
            da1.Update(MAIN.data.

[Dave Kreskowiak]

Read up on what AcceptChanges does first. By calling that, you're telling the dataset that all changes that need to be written out the database HAVE been written out to the database, even if you didn't call Update first.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007, 2008
But no longer in 2009...

[gengel]

Hi, Thanks for your reply. I only added the accept changes later when I noticed that the ADO.net dataset-datatable is not updating. The SQL database contains the new data that was added, but the change is not reflected back to the Dataset-datatable. This is what I am doing now to update the data set

Function Load_Table(ByVal SQLString As String, ByVal TableName As String) As Boolean

    Dim SQLstr As String
    Dim tableISadded As Boolean = False
    Try
        'open connection
        If SQLcon.State = ConnectionState.Closed Then
            SQLcon.Open()
        End If
        SQLstr = SQLString
        Dim cmd As SqlCommand = New SqlCommand(SQLstr, SQLcon)
        Dim da As New SqlDataAdapter(SQLstr, SQLcon)
        da.Fill(pDS, TableName)
        da.FillSchema(pDS, SchemaType.Mapped)
        For Each dt As DataTable In pDS.Tables
            If dt.TableName = TableName Then
                tableISadded = True
            Else
                tableISadded = False
            End If
        Next
        Return tableISadded
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Return False
    Finally
        'close connection
        SQLcon.Close()
    End Try

End Function

           'Remove the orders table
            MAIN.data.DS.Tables.Remove("Orders")
            ''Reload datatable to dataset. 
            MAIN.data.Load\_Table("SELECT \* FROM Orders", "Orders")

[Dave Kreskowiak]

You're not saying what pDS is. You're also not showing what happens to pDS before Load_Table gets called.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007, 2008
But no longer in 2009...

[gengel]

Hi, Hope this helps. Datalayer class

Dim pDS As New DataSet

Public Property DS() As DataSet
    Get
        Return pDS
    End Get
    Set(ByVal value As DataSet)
        pDS = value
    End Set
End Property

Sub MakeConnect()
SQLcon.ConnectionString = "Data Source=192.168.10.24\BDMS;" & _
"Initial Catalog=FLATPAN;" & _
"User ID=flatpan;" & _
"Password=*******"
Try
SQLcon.Open()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

End Sub

Function Load_Table(ByVal SQLString As String, ByVal TableName As String) As Boolean

    Dim SQLstr As String
    Dim tableISadded As Boolean = False
    Try
        'open connection
        If SQLcon.State = ConnectionState.Closed Then
            SQLcon.Open()
        End If
        SQLstr = SQLString
        Dim cmd As SqlCommand = New SqlCommand(SQLstr, SQLcon)
        Dim da As New SqlDataAdapter(SQLstr, SQLcon)
        da.Fill(pDS, TableName)
        da.FillSchema(pDS, SchemaType.Mapped)
        For Each dt As DataTable In pDS.Tables
            If dt.TableName = TableName Then
                tableISadded = True
            Else
                tableISadded = False
            End If
        Next
        Return tableISadded
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Return False
    Finally
        'close connection
        SQLcon.Close()
    End Try

On the orders form call the Load_Table function to add the table to the dataset. This works.

Private Sub Orders_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Load the orders table and lu products table
MAIN.data.Load_Table("SELECT * FROM Orders", "Orders") 'THIS IS THE DA FILL FOR ORDERS
MAIN.data.Load_Table("SELECT * FROM LU_Products", "LU_Products")
MAIN.data.Load_Table("SELECT * FROM Status_Orders", "Status_Orders")
Calculate_Total_Rows(0, "LOAD") 'SOME FORM ACTIONS
Fields_Load(rowCounter) 'NOT PART OF UPDATE ONLY TO VIEW DATA
Fields_State("LOCK") 'LOCK FIELDS SO USER CANT CHANGE
End Sub

Here add a new row to the ORDERS table. This works, but cannot see th

[Dave Kreskowiak]

This is hard to follow. Why are you apparently keeping the entire database, or at least entire tables, in memory? This is a design that will offer a performance gain for a small amount of time. The machine will eventually start swapping your app out to disk, slowing performance down dramatically.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007, 2008
But no longer in 2009...

[William Winner]

If you're using ADO.Net, I believe you have to set up the TableMapping before the Update will work. See: Table Mapping in ADO.Net[^]

[William Winner]

As far as the second example...I don't think this is affecting it, because it doesn't look like you use it again, but you misspelled UNITS in the Select Statement.

Dim da As New SqlClient.SqlDataAdapter
da.SelectCommand = New SqlClient.SqlCommand("SELECT * FROM UNTIS", MAIN.data.Conn)

[William Winner]

Hmm...ok, my first reply may not be your problem. I may have misunderstood your problem. Let me see if I understand now. In the first example, I still believe it's because you haven't set up the TableMapping. Though, I have to say, your code is a little hard to read and I believe a little non-standard. As to the second example, TableMapping isn't your issue because you're running ExecuteNonQuery. That section of code should update the database, but it won't update your DataTable in memory. You have to understand the basics of ADO.Net. When the DataTable is created, it basically makes a copy of the database in memory. It is not linked at all to the underlying Database. That's why you have to run the Update. If the TableMapping is set up properly, then, when the Update is called, it tells the DataAdapter to find the matching Table in the matching DataSet and update the cells based on their RowState property. If the RowState indicated Insert, then the SQL Insert is called. If the RowState says Modify, then it runs the SQL Update. If it says Delete, then it runs the SQL Delete. But, there is no static link between your DataTable and the DataSet. When you run the SQL Insert, you also will have to refresh your DataTable, or insert the data into the DataTable as well.

[gengel]

Hi, I am a little confused now. the code that I am using is based on several references and websites. I thought that was the idea of ADO.net. Work with data offline. What is the corrct way of doing this. I have tried to simplify my code below.

Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class Example
Dim SQLcon As New SqlConnection
Dim pDS As New DataSet

Sub MakeConnect()
    SQLcon.ConnectionString = "Data Source=192.168.10.24\\BDMS;" & \_
                                    "Initial Catalog=FLATPAN;" & \_
                                    "User ID=flatpan;" & \_
                                    "Password=\*\*\*\*\*\*\*\*\*"
    Try
        SQLcon.Open()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try

End Sub

Sub Load\_Table()
    Dim SQLstr As String = "SELECT \* FROM Orders"
    Try
        'open connection
        If SQLcon.State = ConnectionState.Closed Then
            SQLcon.Open()
        End If
        Dim cmd As SqlCommand = New SqlCommand(SQLstr, SQLcon)
        Dim da As New SqlDataAdapter(SQLstr, SQLcon)
        da.Fill(pDS, "ORDERS")
        
        'Added this to see if it helped
        da.FillSchema(pDS, SchemaType.Mapped)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    Finally
        'close connection
        SQLcon.Close()
    End Try
End Sub

Sub Add\_New\_Record()
    Try
        Dim dt As DataTable = pDS.Tables("Orders")
        Dim dr As DataRow = dt.NewRow
        dr.Item("OrderStatus") = "A"
        dr.Item("SamsungRef") = "B"
        dr.Item("ProductCode") = "C"
        dr.Item("ProductSerialNo") = "D"
        dr.Item("ProductionNo") = "E"
        dt.Rows.Add(dr)

        'Update new row to database
        Dim qry As String = "SELECT \* FROM Orders"
        If SQLcon.State = ConnectionState.Closed Then SQLcon.Open()
        Dim da1 As New SqlClient.SqlDataAdapter
        da1.SelectCommand = New SqlClient.SqlCommand(qry, MAIN.data.Conn)
        Dim cb1 As SqlClient.SqlCommandBuilder = New System.Data.SqlClient.SqlCommandBuilder(da1)
        da1.Update(pDS, "Orders")

        'Had to add this to register change in dataset
        pDS.Tables.Remove("Orders")
        'Reatach datatable to dataset. Refresh primary keys

[gengel]

Sorry this should just be Load_Table 'Reatach datatable to dataset. Refresh primary keys MAIN.data.Load_Table("SELECT * FROM Orders", "Orders")

[gengel]

I think I found the problem. Not sure why this makes a difference? da.FillSchema(pDS, SchemaType.Source, TableName)

[William Winner]

You are correct...ADO.Net works with data offline. That's what I was saying. When you run an ExecuteNonQuery like in your second example, the DataSet that you filled previously will not reflect those changes because the DataSet is an offline version and the ExecuteNonQuery is updating the Online Version. The FillSchema should do the trick...though I'm not as familiar with it...it looks like it's there to set up the TableMapping for you. That will work for the first example. Let me show you an example of code that I run to add a new row to a database called "1 Populations"

    Dim openedConnectionString As Boolean = False
    If Not connectionString.State = ConnectionState.Open Then
        connectionString.Open()
        openedConnectionString = True
    End If

    Dim insertionDS As New DataSet
    Dim newDataAdapter As OleDbDataAdapter

    Dim sql As String = "SELECT \* FROM \[" & ActiveProject.Identifier & " Populations\] ORDER BY ID;"
    newDataAdapter = New OleDb.OleDbDataAdapter(sql, connectionString)

    'Create mapping
    newDataAdapter.TableMappings.Add("Table", "Populations")
    With newDataAdapter.TableMappings(0).ColumnMappings
        .Add("ID", "ID")
        .Add("Name", "Name")
        .Add("Char ID", "Char ID")
        .Add("Stratum", "Stratum")
        .Add("PopLevel", "PopLevel")
    End With

    'Make sure field names are bracketed
    Dim cb As New OleDbCommandBuilder(newDataAdapter)
    cb.QuotePrefix = " \["
    cb.QuoteSuffix = "\] "

    'Add Table
    newDataAdapter.Fill(insertionDS)

    'Get last ID number to increment
    Dim lastIndex As Integer = 0

    With insertionDS.Tables("Populations")
        If .Rows.Count <> 0 Then
            lastIndex = .Rows(.Rows.Count - 1).Item("ID")
        End If

        'Insert record
        Dim newRow As DataRow = .Rows.Add

        newRow.BeginEdit()
        newRow.Item("ID") = lastIndex + 1
        newRow.Item("Name") = Population
        newRow.Item("Char ID") = ""
        newRow.Item("Stratum") = Stratum
        newRow.Item("PopLevel") = Level
        newRow.EndEdit()
    End With

    'Save changes
    newDataAdapter.InsertCommand = cb.GetInsertCommand
    newDataAdapter.Update(insertionDS)

    newDataAdapter.Dispose()

    'If we opened the connectionstring, then close it