BorisMatthews
asked on
Custom TableAdapter Update Command
I have a vb.net form in Visual Sudio 2010 that is bound to an SQL table via a autogenerated (wizard) TableAdabpter and DataSet.
When change data I would like to customise the Update command to only update thos fields that have changed since the record was disconnected.
Is there a way of generating a custom SQL statment for the TableAdapter Update command?
Another option I considered was to use a direct update statment using ado.net to update the underlying database and not use the TableAdapter update command at all (and then call accept changes and reload the data...).
When change data I would like to customise the Update command to only update thos fields that have changed since the record was disconnected.
Is there a way of generating a custom SQL statment for the TableAdapter Update command?
Another option I considered was to use a direct update statment using ado.net to update the underlying database and not use the TableAdapter update command at all (and then call accept changes and reload the data...).
ASKER
Yes, I am aware of that. The question is can I customuse the update command so that it only updates the FIELDS with updated data.
Yes. You can catch the row updating event and execute your own code. Or you can loop through the rows yourself. Is there really a need for this?
ASKER
The reason I want to do this is to prevent a user over writing updates that will occur via bulk actions by other users. Certain fields can ne viewed and updated by a user, record by record, that will also be updated via the bulk updates. So if the user updates all fields then they will reset these fields to the values that they had when it wad disconnected from the database.
Why dont you change the update command so that it only includes the fields you want to allow?
http://www.codeproject.com/KB/database/TableAdapter.aspx
http://www.codeproject.com/KB/database/TableAdapter.aspx
ASKER
Yes, I have seen this article and have implemented the concept for dynamically creating the select command but was unclear how to use this same concept for a custom update command, any pointers would be welcomed.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help. I have managed to get this working by looping through the columns in the updated row and comparing the original and updated versions compiled an sql statment which I then used to update the tableadapter.adapter.Updat eCommand property, I then called the update method.
The TableAdapter only updates rows which have been modified. It uses the RowState property of each row to determine if the row has been modified.