Concurrency violation and DateTime

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?

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

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.

On Wed, 21 Jun 2006 07:07:20 +0200, "Cor Ligthert [MVP]"
<notmyfirstname@planet.nl> wrote:

>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,

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

"Jim Brown" <jb@applicationsplus.com> schreef in bericht
news:2e9i92din6a3rafh342lndsftr9vpe0o35@4ax.com...
> 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.
>
> On Wed, 21 Jun 2006 07:07:20 +0200, "Cor Ligthert [MVP]"
> <notmyfirstname@planet.nl> wrote:
>
>>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
>>

On Tue, 20 Jun 2006 12:23:02 -0700, Jim Brown <jb@applicationsplus.com.(donotspam)> wrote:

¤ 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)

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).

--
Patrice

"Jim Brown" <jb@applicationsplus.com> a écrit dans le message de news:
2e9i92din6a3rafh342lndsftr9vpe0o35@4ax.com...
> 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.
>
> On Wed, 21 Jun 2006 07:07:20 +0200, "Cor Ligthert [MVP]"
> <notmyfirstname@planet.nl> wrote:
>
>>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
>>

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

"Patrice" <scribe@chez.com> schreef in bericht
news:%23WsPcTTlGHA.2112@TK2MSFTNGP04.phx.gbl...
> 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).
>
> --
> Patrice
>
> "Jim Brown" <jb@applicationsplus.com> a écrit dans le message de news:
> 2e9i92din6a3rafh342lndsftr9vpe0o35@4ax.com...
>> 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.
>>
>> On Wed, 21 Jun 2006 07:07:20 +0200, "Cor Ligthert [MVP]"
>> <notmyfirstname@planet.nl> wrote:
>>
>>>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
>>>
>
>

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

http://www.experts-exchange.com/Pro...Q_21538974.html
has a discussion that I beleive is relevant.


"Jim Brown" <jb@applicationsplus.com.(donotspam)> wrote in message
news:E8AB3280-E2A2-4C4D-AD8D-6E9C8D900F07@microsoft.com...
> 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?
>

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" <jb@applicationsplus.com.(donotspam)> wrote in message
news:E8AB3280-E2A2-4C4D-AD8D-6E9C8D900F07@microsoft.com...
> 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?
>

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

On Wed, 21 Jun 2006 14:55:28 -0400, "Marina Levit [MVP]"
<someone@nospam.com> wrote:

>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" <jb@applicationsplus.com.(donotspam)> wrote in message
>news:E8AB3280-E2A2-4C4D-AD8D-6E9C8D900F07@microsoft.com...
>> 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?
>>
>