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 then the connection is closed.

The data is then manipulated in later forms and I now want to write those changes back to the DB, all examples seem to do the fill and update on the same page.

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

Paul,

I find the command builder to be a complete nightmare to work with. For it to work you need to provide a 'select' statement to a data adapter, and command builder will then (hopefully) deduce the relevant insert, update and delete commands. You seem to be trying to get the command builder to work from an update command seed - I don't think this will work!

It's more long winded but the best way is to create a new dataset comprising the rows changed or added from your original dataset. You then need to loop through the new dataset and apply the relecvant update, insert or delete commands.

Good luck,

Dave