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...).
BorisMatthewsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CodeCruiserCommented:
>When change data I would like to customise the Update command to only update thos fields that have changed since the record was disconnected.

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.
0
BorisMatthewsAuthor Commented:
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.
0
CodeCruiserCommented:
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?
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

BorisMatthewsAuthor Commented:
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.
0
CodeCruiserCommented:
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
0
BorisMatthewsAuthor Commented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BorisMatthewsAuthor Commented:
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.UpdateCommand property, I then called the update method.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.