Problem with DataSet

[programatix]

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.

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,

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,

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,

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

NameID	Name
2       CCC
3       CCC
1       BBB

Now, how do I solve this? I tried the following 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.

[Thelonius]

I'm not quite sure what results you are trying to display. are you wanting to only show

nameid name
--------- -------
1 BBB
2 CCC

after update?

[programatix]

Yes! Because that is the true data that exist in the database!

[Thelonius]

Try this:

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

SqlDataAdapter1.Update(DataSet1.GetChanges)

DataSet1.Clear()
SqlDataAdapter1.Fill(DataSet1)
DataSet1.AcceptChanges()

End Sub


Any particular reason why you using two seperate datasets?

[programatix]

Gee, I already mentioned that I solved the problem using the code like the one you have given. And I also mentioned that it would be slow if there are a lot of rows in the table.

As for that, I'm searching for answer on whether the behavior is by design or a bug and if there is another way to solve it.

Thanks.

[Thelonius]

I don't know whether or not this is by design or not. If VB.NET is true to what MS says it is, those datasets are in-memory XML, which has it's limitations once you start getting a large amount of data, so that may be why you have a slow down with a large number of records. Try calling a stored procedure to do your updating. That's always going to be faster than an ADO.NET update.

[programatix]

There should not be any speed problem if I can use the Merge method. The thing here is, the Merge method seems to have problem when we add a new row and immediately delete it before we sent the DataSet for update.

Anyway, since nobody has an answer to this problem, I'll have to clear the dataset before merging with the updated dataset.

Thanks.

[programatix]

I found the solution. It's here!
http://support.microsoft.com/default...40&ID=KB;EN-US