Multiple Update Commands on a Table Adapter / Multiple TableAdapters on same dataset
Posted on 2010-09-16
Using Visual Basic.net in Visual Studio 205
I have an application that uses a Table Adapter to fill a typed table within a dataset which is then used as the datasource for a datagrid, allowing for user editing and then using the Table Adapter Update command to save the changes.
All works well. However now I want to be able to also load the data in a summary form (i.e. grouping like rows together using GROUP BY in the Select command).
I can create 2 Select (Fill) commands, but the Update command for saving changes made to summary lines needs to be different to the one I already use.
For example, the current Update Command is in the form
UPDATE Products Set ProductType = @ProductType, Details = @Details WHERE PartId = @Original_PartID
if I am working with Summary data I need something like:
UPDATE Products Set Details = @Details WHERE ProductType = @Original_ProductType
(i.e. change to single row in dataset could update multiple rows in SQL table)
Using a TableAdapter I don't seem to be able to access the Update Command to change it. If I create a new query in the Table Adapter it doesn't take either a datatable or a datarow as an argument. Instead it wants all the individual fields passed as parameters (in reality there are actually many columns to update).
I tried creating two separate table adapters but am having problems updating the summary lines. I am getting a "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records" error, which I suspect is down to my Update Command, although in debuging it looks correct, the data seems correct and if I try the update manually in SQL management studio it works.
So, which is the best approach? Can I do this with a single table adapter? Also is there anyway I can see the actual SQL Update command sent for any record in the dataset (rather than just the parameterised command as this seems OK)