Access Date/Time field causes Concurrency Violation

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

I'm using the OleDBDataAdapter and a Typed dataset in a databound form
in C#.

The database is Access and the table contains a date/time field. The
database data already exists and the date field has both a date and a
time (e.g. 4/1/2004 12:47:29 AM). If I include the date field when I
configure the adapter, updates fail with "Concurrency violation: the
UpdateCommand affected 0 records" If I leave the date field out when
configuring the adapter, the update works fine (except, of course,
that I can't change the date field.)

The failure does not occur if the field contains just a date and not
the time component. Additionally, the error occurs if the field just
contains the time component.

It seems like the time component is not supported.

Any ideas? I have seen other posts with this exact problem; however,
no one has posted a resolution.
 
Access has a hard time doing field by field comparisons when the data type
is a Double (or something with a lot of decimals.) Access thinks
(incorrectly) that the 2 values are not an exact match because they differ
in the 15th decimal place or something. Thus the concurrency violation -
access thinks someone changed the record.

The recommended solution has been to add a Timestamp field (this is NOT a
Date/Time value!) to SQL Server tables. Then Access can compare the current
Timestamp with the one in the table and determine if the record was changed.
No more field by field comparison needed. (I guess you can use the same idea
for other DB systems.)

I guess a lot depends on how much code you want to write and how much
flexibility you need.
If your Update command is created dynamically, then you could have users
change different fields w/o a concurrency violation.

I recommend David Sceppa's book on ADO.Net - it covers all this and more in
great detail.
One of the best references I ever read.
 
Back
Top