Microsoft KB Archive/313540

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

Article ID: 313540

Article Last Modified on 3/9/2006

-

APPLIES TO


 * Microsoft .NET Framework 1.1 Service Pack 1

-



This article was previously published under Q313540



This article refers to the following Microsoft .NET Framework Class Library namespaces:
 * System.Data
 * System.Data.OleDb
 * System.Data.SqlClient



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



WORKAROUND
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 



STATUS
This behavior is by design.



MORE INFORMATION
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
 Start Visual Studio .NET. Create a new Windows program in Visual Basic .NET. By default, Form1 is added to the project.</li> Verify that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not.</li> Place a Button control on Form1. Change the Name property of the button to btnTest .</li>  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 &quot;General Declarations&quot; section of Form1: Imports System Imports System.Data Imports System.Data.OleDb Imports System.Data.SqlClient </li> Create a SqlConnection object and SqlDataAdapter object by dragging the NorthWind.Orders table from the &quot;Server Explorer&quot; into the form.</li>  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, &quot;Orders&quot;) 'Set the AutoIncrement property for the Primary key DS.Tables!Orders.Columns!OrderID.AutoIncrement = True Debug.WriteLine(&quot;Filled with Orders. Table count: &quot; & DS.Tables.Count)

'Add 5 new records With DS.Tables(0) For I = 1 To 5 R = .NewRow R!CustomerID = &quot;ALFKI&quot; R!EmployeeID = 1 R!OrderDate = #1/1/2000# .Rows.Add(R) Debug.WriteLine(&quot;Added: &quot; & R!OrderID & &quot; &quot; & R!CustomerID & &quot; &quot; & R!OrderDate) Next I       End With

'Isolate changes DS2 = DS.GetChanges debug.WriteLine(&quot;Before the update&quot;) For Each R In DS2.Tables(0).Rows Debug.WriteLine(R!OrderID & &quot; &quot; & R!CustomerID & &quot; &quot; & R!OrderDate) Next R       'Update SQL server and get updated row values SqlDataAdapter1.Update(DS2) Debug.WriteLine(&quot;After the update&quot;) For Each R In DS2.Tables(0).Rows Debug.WriteLine(R!OrderID & &quot; &quot; & R!CustomerID & &quot; &quot; & R!OrderDate) Next R       DS.AcceptChanges 'Merge new records back into main dataset DS.Merge(DS2, False) DS.AcceptChanges Debug.WriteLine(&quot;After the merge&quot;) For Each R In DS.Tables(0).Rows Debug.WriteLine(R!OrderID & &quot; &quot; & R!CustomerID & &quot; &quot; & R!OrderDate) Next R                   </li> Save your project. On the Debug menu, click Start to run your project.</li>  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 </li>  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 </li> Save your project. On the Debug menu, click Start to run your project again.</li>  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 </li></ol>

<div class="references_section">