DataTable row data merged when updating the database

[Wavey]

Hi All,

I have a problem with updating an Access database from a datatable. I have two rows of data in my database at the moment for testing, the first row is an ID number (primary key), the second is a name and the third is a price.

I can get this data out of the database, store it in a datatable in a dataset and display the name in a listbox with the name and price displayed in textboxes.

If I change the data for the second row data in the app, the updates go into the database OK. Then when I make changes to the first row data, I get odd things happening to the data in the database. I basically merges the names into one and puts it in the database. i.e. if the name in row 1 is aaaa and row 2 is bb, If I change row 1 to aaaa1 I now get bbaaa1 in the database.

This only happens if I change the data in row 2 before row 1 data.

As far as I can tell by debugging in the RowUpdating event handler, the data in the rows is correct in the datatable, and there are no errors in the rows.

the code I use for the update is

adapterProductDetails.Update(dtProductDetails);

It looks like there is some sort of buffer that isn't being cleared out somewhere, but I don't know what I have to do to clear it. Could it be that because I am using an access database that it can't handle multiple updates being sent to it, I am using an access query (stored proc) with parameters for the updates.

Any thoughts?

Thanks

[Wavey]

Hi All,

Just a bit more information

I write to the access database with the following code

 
DataRow editRow1 = ds.Tables["TraderNames"].Rows.Find("1");
DataRow editRow2 = ds.Tables["TraderNames"].Rows.Find("2");
editRow2["TraderName"] = "Bob";
editRow1["TraderName"] = "AAAAAAA";

update.Parameters.Clear();
update.Parameters.Add("@TName", OleDbType.Char, 20, "TraderName");
update.Parameters.Add("@TNum", OleDbType.Char, 20, "TraderNum");
update.Parameters.Add("@ID", OleDbType.Char, 20, "TraderID");

int numRows = adapter.Update(ds.Tables["TraderNames"]);

The Update Query I use is

UPDATE Traders SET TraderName = [@TName], TraderNum = [@TNum]
WHERE TraderID=[@ID];

Before I do the update the database holds this information

TraderID	TraderName	TraderNum
1	David	20
2	Hamish	11

And after I do the update I see this in the Database

TraderID	TraderName	TraderNum
1	AAAAAAA	20
2	BobAAAA	11

Where I would expect it to hold

TraderID	TraderName	TraderNum
1	AAAAAAA	20
2	Bob	11

As I can see that the data held in the C# DataSet is what I think it should be I assume that it's something to do with access parameters not cleaning up properly in between updates.

Anyone seen anything like this before?