none
SQLCommandBuilder - UPDATE from DataTable only "Appends" Database Table RRS feed

  • 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.


    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 = "<<<MySQLConnectionString>>>"
            '
            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

        Private Function ShowResult(ByVal result As DataTable)

            Dim newForm As New Form()
            newForm.Width = 600
            newForm.Height = 300
            Dim grid As New DevExpress.XtraGrid.GridControl()
            grid.Dock = DockStyle.Fill
            grid.DataSource = result
            newForm.Controls.Add(grid)
            grid.ForceInitialize()
            CType(grid.FocusedView, DevExpress.XtraGrid.Views.Grid.GridView).OptionsView.ShowGroupPanel = False
            newForm.ShowDialog(Me)
            Return newForm

        End Function


    • Edited by LHendren Thursday, August 27, 2015 1:47 PM
    Thursday, August 27, 2015 1:46 PM

Answers

  • You say that you only want to update the Name fields, yet you are adding rows to a DataTable instead of updating existing ones. Consequently, those rows have a RowState of "Added" and the .Update() assumes you want to Insert them. Not what you intended, obviously.

    You have two choices (the second option might be better in your scenario):

    1) SELECT from your database first (into a DataTable).
       Then update the Names in each row of that DataTable.
       Then update the database from that DataTable.
       Do *NOT* do the AcceptChanges (I believe that the Adapter.Update(table) does the table.AcceptChanges() for you, after the Update completes).

    2) The other option is to Fill a different DataTable than the one you Added the rows to.
       Since you have a Primary Key, you can then do a Merge.
       The Filled DataTable will be updated by the Merge and update the Rows that match the PrimaryKey (and their RowState will now be "Modified").
       The adapter.Update() will now correctly update the database instead of adding new rows.

    I'd do it a little differently than you have, but the end result is what you're after. Here's your code, tweaked a bit, and including the Merge, etc.:  
      

    Dim table As New DataTable()
    Dim tableAdd As New DataTable()
    
    Using New SqlCommandBuilder(adapter)
        ' Fill the DataAdapter with the values in the DataTable.
        '
        adapter.Fill(table)  
        ShowResult(table)    'displays datatable correctly 
        
        ' I think you'll need to set the PK here at this point
        ' I'm not sure if the VB syntax is correct (I use C#), but it should be close
        '
        table.PrimaryKey = New DataColumn() { table.Columns("Dex_Row_Id") }
        
        ' Now, it's a simple matter of cloning the table to get the schema (including PK)
        ' This clones only the schema, not the data, so it will be an empty table.
        '
        tableAdd = table.Clone()
        
        ' Add data to the DataTable
        ' The only thing though is that you'll have to include the PK from the database
        ' in these added rows. I didn't realize when I first started writing this that
        ' the numbers were the dog's weight, not the PK. Sorry, this may not actually be
        ' what you want to do. But, maybe it will help you understand what's happening?
        AddDogRow(tableAdd, 57, "June", "Shar Pei")
        AddDogRow(tableAdd, 130, "Tucker", "Bullmastiff")
        AddDogRow(tableAdd, 92, "Maggie", "Anatolian Shepherd Dog")
        AddDogRow(tableAdd, 25, "Charles", "Cavalier King Charles Spaniel")
        AddDogRow(tableAdd, 7, "Candy", "Yorkshire Terrier")
    
        ShowResult(tableAdd)    'displays datatable correctly
    
        ' Now, here comes the Merge
        '
        table.Merge(tableAdd)
        ShowResult(table)    'displays datatable with the above dogs updated!!
        
        cnSQL1.Open()
    
        ' Update the SQL database table with the values.
        '
        adapter.Update(table)
    
        ' dbo.Dogs2 should still only have 5 rows, but be updated with the new dogs
    End Using
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by LHendren Saturday, September 5, 2015 7:38 PM
    Saturday, September 5, 2015 4:53 PM

All replies

  • Hi LHendren,

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

    I suggest you adding "table.AcceptChanges()" just before "adapter.Update(table)", this method should be executed after the value changing in table.

    Regards,
    Youjun Tang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    • Edited by Youjun Tang Friday, August 28, 2015 10:01 AM
    Friday, August 28, 2015 10:01 AM
  • Youjun, thank you for your reply and help.

    However, adding "table.AcceptChanges()" just before "adapter.Update(table)" does not change the database table Dogs2.  Here is what is see:

    Using New SqlCommandBuilder(adapter)
                        '
                        ' Fill the DataAdapter with the values in the DataTable.
                        '
                        adapter.Fill(table)
                        ShowResult(table)         'displays datatable + original table data - total of 10 rows
                        ' Open the connection to the SQL database.
                        '
                        cnSQL1.Open()
                        ' Update the SQL database table with the values.
                        table.AcceptChanges()
                        '
                        adapter.Update(table)    ' dbo.Dogs2 now has 5 rows  (the original 5 rows)
    End Using

    Friday, August 28, 2015 12:10 PM
  • You say that you only want to update the Name fields, yet you are adding rows to a DataTable instead of updating existing ones. Consequently, those rows have a RowState of "Added" and the .Update() assumes you want to Insert them. Not what you intended, obviously.

    You have two choices (the second option might be better in your scenario):

    1) SELECT from your database first (into a DataTable).
       Then update the Names in each row of that DataTable.
       Then update the database from that DataTable.
       Do *NOT* do the AcceptChanges (I believe that the Adapter.Update(table) does the table.AcceptChanges() for you, after the Update completes).

    2) The other option is to Fill a different DataTable than the one you Added the rows to.
       Since you have a Primary Key, you can then do a Merge.
       The Filled DataTable will be updated by the Merge and update the Rows that match the PrimaryKey (and their RowState will now be "Modified").
       The adapter.Update() will now correctly update the database instead of adding new rows.

    I'd do it a little differently than you have, but the end result is what you're after. Here's your code, tweaked a bit, and including the Merge, etc.:  
      

    Dim table As New DataTable()
    Dim tableAdd As New DataTable()
    
    Using New SqlCommandBuilder(adapter)
        ' Fill the DataAdapter with the values in the DataTable.
        '
        adapter.Fill(table)  
        ShowResult(table)    'displays datatable correctly 
        
        ' I think you'll need to set the PK here at this point
        ' I'm not sure if the VB syntax is correct (I use C#), but it should be close
        '
        table.PrimaryKey = New DataColumn() { table.Columns("Dex_Row_Id") }
        
        ' Now, it's a simple matter of cloning the table to get the schema (including PK)
        ' This clones only the schema, not the data, so it will be an empty table.
        '
        tableAdd = table.Clone()
        
        ' Add data to the DataTable
        ' The only thing though is that you'll have to include the PK from the database
        ' in these added rows. I didn't realize when I first started writing this that
        ' the numbers were the dog's weight, not the PK. Sorry, this may not actually be
        ' what you want to do. But, maybe it will help you understand what's happening?
        AddDogRow(tableAdd, 57, "June", "Shar Pei")
        AddDogRow(tableAdd, 130, "Tucker", "Bullmastiff")
        AddDogRow(tableAdd, 92, "Maggie", "Anatolian Shepherd Dog")
        AddDogRow(tableAdd, 25, "Charles", "Cavalier King Charles Spaniel")
        AddDogRow(tableAdd, 7, "Candy", "Yorkshire Terrier")
    
        ShowResult(tableAdd)    'displays datatable correctly
    
        ' Now, here comes the Merge
        '
        table.Merge(tableAdd)
        ShowResult(table)    'displays datatable with the above dogs updated!!
        
        cnSQL1.Open()
    
        ' Update the SQL database table with the values.
        '
        adapter.Update(table)
    
        ' dbo.Dogs2 should still only have 5 rows, but be updated with the new dogs
    End Using
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by LHendren Saturday, September 5, 2015 7:38 PM
    Saturday, September 5, 2015 4:53 PM
  • Bonnie, thank you.  I learned a lot from your suggestion.  Since my original post, I had changed the code to update a temporary table with the datatable; then update the database table with the temporary table rows.

    Your method using merge is much easier and less lines of code.  BTW, the line "table.PrimaryKey = New DataColumn() { table.Columns("Dex_Row_Id") } " is correct for VB. 

    Once I applied the code from the test application to my real-world application, it works exactly the way I need it to work.

    thank you again.

    Saturday, September 5, 2015 7:40 PM
  • You're welcome!  Glad I could help!  =0)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, September 5, 2015 8:28 PM