The Merge method may create duplicate records when you try to update records with an AutoIncrement field

When you update records with an AutoIncrement field, the Merge method of the DataSet object may create duplicate records.

To work around this behavior:

• Check and update each row manually instead of using the AcceptChanges method on the DataSet.
• Use the RowUpdated event of the DataAdapter object and skip the duplicate rows.

  If e.StatementType = StatementType.Insert Then e.Status = UpdateStatus.SkipCurrentRow
  					

This behavior is by design.

When you update records with a DataAdapter, you can extract the changes to a separate DataSet (DiffGram). This is necessary if you want to send the changes to a Web service, or if you want to complete the updates in a single transaction and roll back on error. If your table contains an AutoIncrement field or an Identity column, or contains other columns where the server automatically generates a value, you should merge the updated rows back to the original DataSet.

The DataSet object matches records based on the key value. If you update the key value, there may not be a match in the original DataSet, and there will be duplicate records. Also, the record could be matched to a different record entirely.

The following code describes how to add several records with an AutoIncrement field to a DataTable object. Use GetChanges to get a DiffGram, update the DiffGram, retrieve new AutoIncrement values, and then merge the updated DiffGram back into the main DataSet.

Steps to Reproduce the Behavior

1. Start Visual Studio .NET.
2. Create a new Windows program in Visual Basic .NET. By default, Form1 is added to the project.
3. Verify that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not.
4. Place a Button control on Form1. Change the Name property of the button to btnTest.
5. Use the Imports statement on the System and System.Data namespaces so that you are not required to qualify declarations in those namespaces later in your code. Add the following code to the "General Declarations" section of Form1:

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

6. Create a SqlConnection object and SqlDataAdapter object by dragging the NorthWind.Orders table from the "Server Explorer" into the form.
7. Copy and paste the following code into the btnTest_Click event:

   
           Dim DS, DS2 As DataSet
           Dim R As DataRow
           Dim I As Integer
           DS = New DataSet()
           ' Get schema for empty Orders DataTable
           SqlDataAdapter1.FillSchema(DS, SchemaType.Mapped, "Orders")
           'Set the AutoIncrement property for the Primary key
           DS.Tables!Orders.Columns!OrderID.AutoIncrement = True
           Debug.WriteLine("Filled with Orders. Table count: " & DS.Tables.Count)
   
           'Add 5 new records
           With DS.Tables(0)
               For I = 1 To 5
                   R = .NewRow()
                   R!CustomerID = "ALFKI"
                   R!EmployeeID = 1
                   R!OrderDate = #1/1/2000#
                   .Rows.Add(R)
                   Debug.WriteLine("Added: " & R!OrderID & " " & R!CustomerID & " " & R!OrderDate)
               Next I
           End With
   
           'Isolate changes
           DS2 = DS.GetChanges
           debug.WriteLine("Before the update")
           For Each R In DS2.Tables(0).Rows
               Debug.WriteLine(R!OrderID & " " & R!CustomerID & " " & R!OrderDate)
           Next R
           'Update SQL server and get updated row values
           SqlDataAdapter1.Update(DS2)
           Debug.WriteLine("After the update")
           For Each R In DS2.Tables(0).Rows
               Debug.WriteLine(R!OrderID & " " & R!CustomerID & " " & R!OrderDate)
           Next R
           DS.AcceptChanges()
           'Merge new records back into main dataset
           DS.Merge(DS2, False)
           DS.AcceptChanges()
           Debug.WriteLine("After the merge")
           For Each R In DS.Tables(0).Rows
               Debug.WriteLine(R!OrderID & " " & R!CustomerID & " " & R!OrderDate)
           Next R
   					

8. Save your project. On the Debug menu, click Start to run your project.
9. Click the btnTest button. On the View menu, click Output window.
   
   Notice at each step there are only five records involved. However, after the Merge [and AcceptChanges], there are now ten records.

   Filled with Orders. Table count: 1
   Added: 0 ALFKI 1/1/2000     <-------- original 5 records added - using default AutoIncrementSeed
   Added: 1 ALFKI 1/1/2000
   Added: 2 ALFKI 1/1/2000
   Added: 3 ALFKI 1/1/2000
   Added: 4 ALFKI 1/1/2000
   Before the update
   0 ALFKI 1/1/2000     <--------- diffgram prior to DataAdapter.Update
   1 ALFKI 1/1/2000
   2 ALFKI 1/1/2000
   3 ALFKI 1/1/2000
   4 ALFKI 1/1/2000
   After the update
   11299 ALFKI 1/1/2000    <------------ diffgram after DataAdapter.Update 
   11300 ALFKI 1/1/2000
   11301 ALFKI 1/1/2000
   11302 ALFKI 1/1/2000
   11303 ALFKI 1/1/2000
   After the merge
   0 ALFKI 1/1/2000      <------------ these records should no longer exist
   1 ALFKI 1/1/2000
   2 ALFKI 1/1/2000
   3 ALFKI 1/1/2000
   4 ALFKI 1/1/2000
   11299 ALFKI 1/1/2000    <-------- these should have replaced them and not been appended.
   11300 ALFKI 1/1/2000
   11301 ALFKI 1/1/2000
   11302 ALFKI 1/1/2000
   11303 ALFKI 1/1/2000
   					

10. Copy and paste the following line of code into the RowUpdated event handler of SqlDataAdapter1.

            If e.StatementType = StatementType.Insert Then e.Status = UpdateStatus.SkipCurrentRow
    					

11. Save your project. On the Debug menu, click Start to run your project again.
12. Click the btnTest button. On the View menu, click Output window. Now you can see that only the required data is present after the merge.

    After the merge
    11299 ALFKI 1/1/2000
    11300 ALFKI 1/1/2000
    11301 ALFKI 1/1/2000
    11302 ALFKI 1/1/2000
    11303 ALFKI 1/1/2000
    					

For additional information about ADO.NET, click the article number below to view the article in the Microsoft Knowledge Base:
For additional information about related topics, click the article numbers below to view the articles in the Microsoft Knowledge Base: