Ah, this is concurrency 101. It's an age old question that has been asked (and answered) since the first time two people tried to sit on the same rock in the garden of Eden. We've discussed this here on this list many times as well.
When you use a disconnected approach to data access (as ADO.NET encourages you to do) you have to understand that the data you've fetched is simply a copy of the data stored in the database. When you do an update (as when you execute the Update method) the data in the database is changed--unless you add rules that say "Ok, if someone else changed the data since I got my copy, don't post my change". This is the default behavior for ADO.NET (as dictated by the CommandBuilder). Yes, there are other rules and ways to determine if a row has changed since you last fetched it but let's just keep it simple for now. So, if someone has changed the row, your Update did not go through. If you requery (run the SELECT again) you'll see the changes made by the other user(s). But there is nothing to stop yet another user from making more changes before you make your change. This is typical of a heavily used database--add more users and you get more collisions.
In my new book, I discuss how to design systems and databases where the users don't share the same rows. I design my data intersections with traffic lights and bridges instead of positioning ambulances nearby to pick up the dead and injured. While I don't know how your database is designed or the problem you're trying to solve with it, there are generally accepted practices we've come up with over the years to deal with concurrency issues.
1.. If you have to design a system where more than one user has access to the rows, you have to be prepared for collisions. In ADO.NET these are manifested as the Update method being told that the "rows affected" value was not 1.
2.. If your update can fail because you permit multiple access to the rows you must decide (in your design) what to do about it. You can
1.. Force through your update. In this approach you might just remove all WHERE clause tests to see if the row has changed. This is called "Last Update wins".
2.. You can fetch the changed row and let the user decide what to do. I don't like this approach as the data can change again a dozen times while Betty or Bob figures out what to do.
3.. You can make a logical decision about what to do based on business rules. For example, accounts from Texas have a lower priority than those from California but no changes are permitted to accounts from Cleveland.
4.. There are other variations on this list... but you get the idea.
3.. If possible revisit 1. See if your design can be modified to prevent collisions instead of cleaning up after them.
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
