Hi,

I've been working with DataSet and SQL Server 2000. There is a problem that I can't solve. Maybe anyone out there has any explaination for this behavior.

To explain this behavior, I have to describe how to produce it. These are the steps that should be taken:

1. Create a Table(SQL Server).
2. Add a field called NameID. Make it a primary key with autoincrement enabled.
3. Add another field called Name.
4. In a Windows Form, insert a DataAdapter and link it to the table created in step 1.
5. Generate a dataset.
6. Insert a datagrid and bind it to the dataset generated.
7. Insert 2 buttons to the Windows Form.
8. Add the following code.

Code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
	DataSet1.Clear
	SqlDataAdapter1.Fill(DataSet1)
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
	Dim dsChanges As DataSet

	dsChanges = DataSet1.GetChanges
	SqlDataAdapter1.Update(dsChanges)

	DataSet1.Merge(dsChanges)
	DataSet1.AcceptChanges()
End Sub
9. Run the application.
10. Add a new row (using the DataGrid). Type "AAA" in the "Name" column.
11. Notice the "NameID" column. It should be 1.
12. Delete the row by selecting it and press DELETE (on your keyboard).
13. Add another new row. Type "BBB" in the "Name" column. The "NameID" column should be 2.
14. Add another new row. Type "CCC" in the "Name" column. The "NameID" column should be 3.
15. Your DataGrid should look like this,

Code:
NameID	Name
2       BBB
3       CCC
16. Click Button2 to save the changes.
17. You will see there 3 rows on the DataGrid instead of 2 rows which is before you clicked Button2.
18. The new DataGrid should look like this,

Code:
NameID	Name
2       CCC
3       CCC
1       BBB
Explaination
This happen because the next value for the autoincrement field before the test is 1 (both dataset and database). We then added a new row in the dataset and then deleted it. When the dataset update with the database, the database is expecting value 1 for the "NameID" but the dataset is sending value 2 for the "NameID" for the first row. As for that, the dataset adjust itself to contain the following values,

Code:
NameID	Name
1       BBB
2       CCC
When we merge the 2 datasets (the one sent for update and the one on the form), it did not remove the obsolete row which contains "NameID"=3. So we get

Code:
NameID	Name
2       CCC
3       CCC
1       BBB
Now, how do I solve this? I tried the following code,

Code:
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
	Dim dsChanges As DataSet

	dsChanges = DataSet1.GetChanges
	SqlDataAdapter1.Update(dsChanges)

	DataSet1.Clear
	SqlDataAdapter1.Fill(DataSet1)
	DataSet1.AcceptChanges()
End Sub
But it will be slow if there are a lot of rows.

Any advice? Thanks.