Updating MS Sql 2000 DB with C# Dataset

[paulhopkins]

Hi,

I am trying to update my SQL 2000 Database with a modified dataset but am having trouble doing so. I have taken a look through the MS documentation etc but can't seem to solve the problem. The project is being written using the .NET compact framework for deployment on a PDA but I am sure the procedure is similiar, if not the same, as a windows app.

I have a list of items, which have a column in the dataset named 'picked' and when a button is clicked the status of that column is changed to 'y' and this is done for each item in turn. The dataset is filled in another form and passed through....

I modify the DS with the following code:

DataRow drItemsUpdate = dtItems.Rows.Find(itemNo);
drItemsUpdate.BeginEdit();
drItemsUpdate["Picked"] = "y";
drItemsUpdate.EndEdit();

I have confirm, printing in a text box, the change has been made to the dataSet.

Once all the items have been iterated through I then want to update the dataset to the DB.

This is the code I use to try and do this

string cmdUpdateSelect = "update Items set Picked = 'y' where itemNo = "+itemNo;

SqlConnection conn = new SqlConnection(conString);
SqlDataAdapter adptDsUpdate = new SqlDataAdapter();
adptDsUpdate.UpdateCommand = new SqlCommand(cmdUpdateSelect,conn);
SqlCommandBuilder cbDsUpdate = new SqlCommandBuilder(adptDsUpdate);
conn.Open();

adptDsUpdate.Update(dsItems, "Items");

This execute the code with no problems but does not update the database! Where am I going wrong? I am not totally sure what the update command does?! Surely, the dataset would compare with the database and make the changes where the values were inconsistant??? Maybe it's not that easy!

Any help would be truely appreciated.
Thanks in advance

Paul

 

[Korach]

Use DataSet.AcceptChanges() before you save to the DB.