Hello,

in a network-winforms-application based on a access database I've got
the following problem:

user A starts software
user B starts software

user A deletes row in db
user B changes data

application of user B makes an update dataadapter.update(datatable).
Now I get an concurrency violation.

Now I try to handle this error - what I want to do is simply refresh
user B's datatable with datadapter.fill(datatable). But nothing happens
- the datatable is not updated. I already tried .clear before update -
without success.

Can you help me with this issue please?

Thanks Alfred

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.=20

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.=20

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.=20
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

--=20
____________________________________
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.
__________________________________

[quoted text, click to view]