SQLCommandBuilder - UPDATE from DataTable only "Appends" Database Table

Question

I am trying to UPDATE database table rows using SQLCommandBuilder with datatable rows using the following test code. One table with a primary key column and one datatable to keep it simple.

Using the following code, the dbo.Dogs2 table is "appended" with the datatable rows - therefore doubling the number of rows rather than just updating the changed row(s)

If I add the code table.AcceptChanges() just before the Dim builder As New SqlCommandBuilder(adapter), the database table dbo.Dogs2 remains unchanged.

If I add the code table.AcceptChanges() just before the adapter.Update(table), the database table dbo.Dogs2 remains unchanged.

Public Class Form1

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        ' dbo.Dogs2 database table columns are exactly like datatable columns with exception of dog names
         ' only UPDATING the "Name" field (no Inserts or deletes)
         ' orginal dog names "Name" in Dogs2.dbo are Sharpy, Bully, Shep, Charlie, and Yorky
         ' new dog names "Name" in Dogs2.dbo are June, Tucker, Maggie, Charles, and Candy
         ' Dex_Row_Id is the primary key with Identity Increment set to 1

        ' Create a DataTable with five columns.
         '
         Dim table As New DataTable()
         table.Columns.Add("Weight", GetType(Integer))
         table.Columns.Add("Name", GetType(String))
         table.Columns.Add("Breed", GetType(String))
         table.Columns.Add("Size", GetType(Char))
         table.Columns.Add("Date", GetType(DateTime))
         table.Columns.Add("Dex_Row_Id", GetType(Integer))
         '
         ' Add data to the DataTable
         '
         AddDogRow(table, 57, "June", "Shar Pei")
         AddDogRow(table, 130, "Tucker", "Bullmastiff")
         AddDogRow(table, 92, "Maggie", "Anatolian Shepherd Dog")
         AddDogRow(table, 25, "Charles", "Cavalier King Charles Spaniel")
         AddDogRow(table, 7, "Candy", "Yorkshire Terrier")

        ShowResult(table)    'displays datatable correctly    (this is a DevExpress.com Reference/Extension)
         '
         ' Create new SqlConnection, SqlDataAdapter, and builder.
         '
         Dim cnString As String = "<<<SQLConnectionString>>>"
         '
         Using cnSQL1 As New SqlConnection
             cnSQL1.ConnectionString = cnString

            Using adapter = New SqlDataAdapter("SELECT * FROM Dogs2", cnSQL1)

                ShowResult(table)  'displays datatable

                Dim builder As New SqlCommandBuilder(adapter)
                 adapter.UpdateCommand = builder.GetUpdateCommand()
                 builder.RefreshSchema()

                Using New SqlCommandBuilder(adapter)
                     '
                     ' Fill the DataAdapter with the values in the DataTable.
                     '
                     adapter.Fill(table)  

                    ShowResult(table)  'displays datatable + original table data

                    ' Open the connection to the SQL database.
                     '
                     cnSQL1.Open()

                    ' Update the SQL database table with the values.
                     '
                     adapter.Update(table)

                    ' dbo.Dogs2 now has 10 rows  (the 5 rows from the dataset + the original 5 rows)

                End Using

            End Using

        End Using

    End Sub

Steve · Answer 1 · 2015-08-28T19:49:34.393

You are using incorrectly the adapter. You should first load the rows from the database, then update the retrieved rows and finally call the update.

 ' REMOVE THE CODE BEFORE THIS '
 ' Create new SqlConnection, SqlDataAdapter, and builder.'

 Dim cnString As String = "<<<SQLConnectionString>>>"
 Dim table = New DataTable() ' Leave it emtpy and without schema'
 Using cnSQL1 As New SqlConnection
    cnSQL1.ConnectionString = cnString
    Using adapter = New SqlDataAdapter("SELECT * FROM Dogs2", cnSQL1)
        Dim builder As New SqlCommandBuilder(adapter)
        adapter.UpdateCommand = builder.GetUpdateCommand()
        ' no need of this -> builder.RefreshSchema()'
        Using New SqlCommandBuilder(adapter)
            adapter.Fill(table)  
            ShowResult(table)  'displays original table data'

            ' no need of this -> cnSQL1.Open()'
            ' NOW YOU COULD CHANGE THE ROWS, FOR EXAMPLE'
            table.Rows(0)("Weight") = 99

            ' Update the SQL database table with the values.'
            adapter.Update(table)
        End Using
    End Using
End Using

When you pass an existing table to the Fill method of the Adapter, the existing record are not removed and thus your table is filled with data from the database and from your manual creation of the table (Of course the Adapter build for you the table columns. Moreover, the rows added manually to your table are marked with the DataRowState.Added while the row modified by your code will be marked with the DataRowState.Changed. This state helps the Update command to decide which action to perform on every row present in the table (Of course the rows that are not changed maintain the initial DataRowState.Unchanged

Finally, calling AcceptChanges doesn't mean that the rows will be updated on the database table. Only the DataRowState flag is reset to the DataRowState.Unchanged

Steve, thank you for the helpful information. I modified my test code per your answer, and the database table does update correctly. — TCIslandTime, Aug 29 '15 at 17:48
In my real-world application, I am generating a DataTable via the DevExpress.com spreadsheet extension in VS2013. So for that case, I have my data (datatable) already to use in updating the database table. In other words, my datatable is filled prior to the "Dim cnString As String = connectionString" line of code. Am I correct is assuming the only way to succeed is to use parameters? — TCIslandTime, Aug 29 '15 at 18:00
Sorry but it is not clear at all. If your table has already rows without using an adapter to fill it and you want to change them and write your changes back you need to loop over your rows, check the DataRowState for each row and apply an UPDATE command directly without using the adapter Fill method. You could get the UpdateCommand from the builder and use it filling the required parameters — Steve, Aug 29 '15 at 19:00
Steve, I populate the spreadsheet via myTableAdapter.FillBy_myQuery(myDataTable). I add values to empty columns (in my case “TestResults”). Using DevExpress’ exporter.Export(), the spreadsheet range is exported to a datatable containing the added “TestResults”. This is the datatable I wish to use to update the database table. Never adding or deleting rows – only updating existing rows in the database table with “TestResults”. — TCIslandTime, Aug 29 '15 at 21:20

SQLCommandBuilder - UPDATE from DataTable only "Appends" Database Table

1 Answers1