Concurrency violation and DateTime

[Guest]

I’m trying to design a front end program in VB.NET for a MS Access database.
I’m getting Concurrency Violation errors when updating if there is a DateTime
field in my table that has been edited in Access. Seems to be when there is a
Time value in the column.

This is even happening from forms generated by the Data Form Wizard, so I
don't think its code I'm writing.

What am I missing here?

 

[Cor Ligthert [MVP]]

Jim,

I'm trying to design a front end program in VB.NET for a MS Access
database.
I'm getting Concurrency Violation errors when updating if there is a
DateTime
field in my table that has been edited in Access. Seems to be when there
is a
Time value in the column.

If somebody else has in the meantime that your program is busy changed any
field that is in your datatable using MS - Access and you want to update
that, than that means a connurrency violation in the standard procedures of
the OleDbDataadapter. It checks if the old value is the same as it it was
when read.

Cor

 

[Jim Brown]

This is happening when my VB.net program is the only user of the
database.

The Time values seem to be different between Access and VB. If I clear
the date or put in just mm/dd/yyyy values everything is fine. However
when in production one of the DateTime fields needs to be a full
mm/dd/yyyy hh:nn:ss value. Also both the Access and VB program would
be in use, hense my problem.

 

[Cor Ligthert [MVP]]

Jim,

Yes the time and dates between Access and VB can be fractional different in
the milleseconds part.

However I don't see how this can affect your problem.

Can you show us a piece of code where you handle the datetime.

Cor

 

[Paul Clement]

¤ I’m trying to design a front end program in VB.NET for a MS Access database.
¤ I’m getting Concurrency Violation errors when updating if there is a DateTime
¤ field in my table that has been edited in Access. Seems to be when there is a
¤ Time value in the column.
¤
¤ This is even happening from forms generated by the Data Form Wizard, so I
¤ don't think its code I'm writing.
¤
¤ What am I missing here?

Check the parameter type for this column generated by the DataAdapter. It might be
System.Data.OleDb.OleDbType.DBDate but it should be System.Data.OleDb.OleDbType.Date.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Patrice]

Not sure, but I've seen something similar caused by optimistic locking when
the server side and client side precision doesn't match for datetime values.

The scenario is :
- a datetime value is retrieved from the DB
- as the precision is bigger than the client side precision, it is truncated
- the where clause used for optimistic locking can' t find the original row
as the datetime value was truncated. This is seen as if someone else changed
the record.

It was with SQL Server/ADO but it looks like it could be something similar
with Access/ADO.NET.

See the statement used by the adapter, the original value kept by the
application and double check you have the same value in your Access DB (be
aware also of the format that could prevent to see the whole field if you
see the DB using the Access UI).

 

[Cor Ligthert [MVP]]

To add to Patrice,

I never noticed this but maybe is it something that the date is given as Now
somewhere.

The DateTime in Net is accurate to100 nanoseconds, in Access it is accurate
to 1000/3 milliseconds.

Cor

 

[Jim Hughes]

You may want to look into DateTime.ToOADate for use in your VB.Net program.

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21538974.html
has a discussion that I beleive is relevant.

 

[Marina Levit [MVP]]

I am wondering if this can be related to conversion to/from UTC time
somehow. Maybe something is converting it to UTC, and that causes the
mismatch.

 

[Jim Brown]

I'm getting the same problem with a form auto generated by the Data
Form Wizard (VB.NET version 2002).

In my own code I'm just calling the DataAdapter.Update(dataset)
method. So I think that comments I've seen elsewhere in this thread
about the precision difference between Access and VB.NET's handling of
time values is the answer.

Here is the update logic in my own form.

Private Sub DataUpdate()
Dim intUpdates As Integer

Me.BindingContext(Me.DsIssueLog1,"tblIssueLog").EndCurrentEdit()
If Me.DsIssueLog1.HasChanges = False Then Exit Sub

Try
intUpdates = Me.OleDbDataAdapter1.Update(Me.DsIssueLog1)
Catch ex As Exception
MsgBox("Error during data update: " & ex.Message)
End Try
'--MsgBox(intUpdates & " updates made")
End Sub

 

[Jim Brown]

Where do I go to check that in Visual development program and can it
be changed?

 

[Paul Clement]

¤ Where do I go to check that in Visual development program and can it
¤ be changed?
¤

If you're working with VS 2003 try searching for DBDate. You should find it in the Windows Form
Designer Region.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Jim Brown]

You were right. In the "Windows Form Designer" code region I found
where my dates are defined as DBDATE's rather that DATE's which would
keep the time values?

So the code generator for my version 2002 VB.NET was not right. Any
idea about the new 2005 version?

 

[Paul Clement]

¤ You were right. In the "Windows Form Designer" code region I found
¤ where my dates are defined as DBDATE's rather that DATE's which would
¤ keep the time values?
¤
¤ So the code generator for my version 2002 VB.NET was not right. Any
¤ idea about the new 2005 version?
¤

Yes, I believe this was fixed in the 2005 version.


Paul
~~~~
Microsoft MVP (Visual Basic)