Intermittent concurrency violation in single-user operations

ElrondCT
ElrondCT used Ask the Experts™
on
I'm struggling with an intermittent error in my VB .NET 2003 application. I have the app set up for single-user operations; optimistic currency has been turned off for all DataAdapters, and I check to make sure any .pdb file of the same name as the Access .mdb file that's being opened can be deleted, to make sure no one else is using the data file at the same time. I'm using a database that contains three tables, a parent and two child tables.

Occasionally, I am getting a concurrency error:

Concurrency violation: the UpdateCommand affected 0 records

The error seems to always occur on one of the child tables. using the following command:

                  dsCurrent = CType(dsEZ13A.GetChanges, dsEZ13)
                  If dsCurrent.Rent.Count <> 0 Then
                        result = DA.odbdaRent.Update(dsCurrent)
                    End If

Trying the update again, once I get the error, is pointless; the error will recur as long as I try. If I abandon the changes, shut down the program, and go back and make the same change, it always works. I think I've only noticed the problem when making changes to an existing record, rather than when entering new records.

The fact that it's intermittent (I haven't been able to define a series of steps to cause the error) means it'll be tough to try out suggestions, but I'd appreciate any thoughts on what might be going on.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
When you get an error report of "Concurrency violation" via VB.NET from Access I'm afraid it doesn't always mean that there is actually a concurrency violation.  Something is wrong, but it may be a syntax error or data error rather than a concurrency error as such.

I think you need to write some debugging code.  If you declare your dataadapter WithEvents, you can hook into its RowUpdating event.  If you put - e.g. Debug.WriteLine - code in that to output both the commandtext and the values that are going into all the parameters for the command concerned you can, when all is going smoothly, simply ignore it.  But as soon as you get an error reported you could inspect it.  Doing it this way will show you what is actually being sent rather than what you expect to be sent.  There may be some difference there.  Another thing to watch for is strings that are too long for the Access field to which they are directed.  And - unless your set-up prevents this - you could try, while your application is still in the same state, entering that command text, with the same values in the parameter placeholders, directly into Access itself.

It's an approach, rather than an answer, I'm afraid.  But it might help you to find an answer.

Roger
That certainly seems like a reasonable way to go. I haven't worked with the Debug class before, so I'll need to do a bit of reading on that. No need for you to describe it further at this point; if I hit a wall, I'll let you know.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial