Concurrency violation with Access database

[Rick Lederman]

Using the ado.net code below to write records in an Access database, the
first three records update correctly but the last three all cause a
"Concurrency violation" error. I know that there are 6 records in the
Access database. Any ideas on what I'm doing wrong?

Rick Lederman, (e-mail address removed)

OLEDBConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
sCDatabaseName

cnFAMax = New OleDbConnection(OLEDBConnectionString)

cnFAMax.Open()

Dim i As Short

Dim MyTime As Date

sSql.Remove(0, sSql.Length)

sSql.Insert(0, "SELECT MY_EMPLOYEES.TIME_IN, MY_EMPLOYEES.EMPLOYEE_NUMBER
FROM MY_EMPLOYEES ORDER BY MY_EMPLOYEES.EMPLOYEE_NUMBER;")

Dim cmd As New OleDbCommand

cmd.CommandText = sSql.ToString

Dim da As New OleDbDataAdapter(sSql.ToString, cnFAMax)

Dim ds As New DataSet

da.Fill(ds, "My_Employees")

Dim cmdbuilder As New OleDbCommandBuilder(da)

da.InsertCommand = cmdbuilder.GetInsertCommand

da.DeleteCommand = cmdbuilder.GetDeleteCommand

da.UpdateCommand = cmdbuilder.GetUpdateCommand

For i = 0 To 5 ' I know that there are six records in this table

Select Case i

Case Is = 0

MyTime = Now

Case Is = 1

MyTime = DateAdd(DateInterval.Minute, -10, Now)


Case Is = 2

MyTime = DateAdd(DateInterval.Minute, -16, Now)

Case Is = 3

MyTime = DateAdd(DateInterval.Minute, -17, Now)

Case Is = 4

MyTime = DateAdd(DateInterval.Minute, -22, Now)

Case Is = 5

MyTime = DateAdd(DateInterval.Minute, -18, Now)

End Select

ds.Tables("My_Employees").Rows(i).Item("TIME_IN") = MyTime

Try

da.Update(ds, "My_Employees") ' the error pops up here with the
third & 4th & 5th update. Records 0 to 2 work fine


Catch ex As Exception

MsgBox("Record " & i.ToString & " " & ex.Message)

End Try

Next

 

[MSFT]

Hi Rick,

Thank you for using the community. As I understand, your code generate an
"Concurrency violation" error when update an Access Database. I have
studied your code and I haven't found any problems with it. Therefore, I
suspect the problem should be related to the underlying database. You may
open the database in MS Access, open the Table in Design View and check the
column "TIME_IN". Have you set Validation Rule or Validation Text for it?
Is it a primary key? If it has some Validation Rule, the updated Date value
from your code may not be valid and generate such an error.

Luke
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Rick Lederman]

Luke,

There is no Validation Rule, and no Validation Text, it is simply a
Date/Time field. The Primary Key field is the EMPLOYEE_NUMBER field. The
only data that my code should be changing is the TIME_IN field. Notice that
I can change the TIME_IN in the first three records but can't change it in
the last three but there should be no difference. I just tried changing the
SORT order and now can only change two records ... The records that will not
change CAN be changed if I only do that record ... for example I changed the
TIME_IN value of record 5 by itself & it works fine.

None of this makes any sense. Are there bugs in ADO.NET that can cause this
problem? If that is a possibility I could file a MSDN subscription incident

Thanks,

Rick

 

[MSFT]

Hi Rick,

I have searched the database and I haven't found a similar bug record. I am
very interested on the problem. Is it possible for you to create a small
project which can reproduce the problem and post it in the newsgroup with
the database file? I will perform more research on the project. You also
can send it directly to: (e-mail address removed).

I am looking forward to your message.

Luke
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[MSFT]

Hi Rick

I checked the project and database. Finally I found the problem is in the
database file. Especially, a record whose In_time is 2003-12-9 17:59:15. I
open the database in Access and change it to different value and then
change it back, then all code work correctly. I think this field may get
corrupt in your database.

Best regards,

Luke Zhang
Microsoft Developer Support Engineer
(e-mail address removed)