DBConcurrencyException on dataset update (because of datetime primary key ?)

[rc]

Hello,

I'm writing a VB .NET application that manipulates data in an Access
database.
I fill a Dataset with data extracted from a table of my database,
modifiy one field and try to update with an OleDbCommandBuilder which
throws a DBConcurrencyException.

I think it's because of the primary key of my Access Table which is a
Date/Time field.
I tried to update with a OleDbCommand and wrote the update query but
with the same result.
I also tested to fix the OleDbType of parameters, without success.

However most of rows are updated successfully, this exception occurs
randomly and there is no significant resemblances between rows which
triggers this exception...

Some help would be very welcome.
Thanks you in advance.

 

[Cor Ligthert [MVP]]

RC,

If two the same datetimes can occur than the answer is simple in my opinion.

Cor

 

[rc]

No, the problem isn't unique primary key value (it's not the same
exception), and there is no same values in my table (thanks to
milliseconds...).
And this exception can occur with only one record in the table...

 

[Miha Markic [MVP C#]]

Did you actually look at the sql statement being used?
I guess there are lot of "where" conditions and if at least one fails then
you'll get no rows updated meaining concurrency exception.

 

[Guest]

I got DBConcurrencyException when updating from a dataadapter and it was
because of some null values on bit field. Are you using a .xsd? try to put
default values on all your fields.

Just a guess?

 

[rc]

Miha Markic, I looked at sql statements and they were similar when it
updated successfully and when it thrown exception...

joeblast, I don't use XSD but there is no Null values in my table.

I'm really stuck with this exception, so I have uploaded an example of
my problem in an Access DataBase :
http://protechnologies.fr/test/TEST.MDB
There is only one table, two fields and two records. I'm trying to
update T2 value, the first record is updated successfully but the
second isn't...
Note that it could be updated with DAO without problems (and theses
records have been created with DAO... Maybe the problem/bug !?)

Thanks you in advance.

 

[Cor Ligthert [MVP]]

Rc,

We are more interested in the piece of code with which you do this,
Or do you use the designer than what version do you use

Cor

 

[rc]

Ok, here is an example of the piece of code with which I do this (for
the database I've uploaded).

("T1" is a datetime field and the primarykey of the table "TEST1"
I would like to update all T2 values of the table.)

Private x_Con As New OleDbConnection
Private x_PatDb As String = "...\TEST.MDB"

Public Sub OpeCon()
x_Con.ConnectionString = "Jet OLEDB:Global Partial Bulk
Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase Locking Mode=1;Data
Source=""" & x_PatDb & """;Mode=Share Deny None;Jet OLEDB:Engine
Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System
database=;Jet OLEDB:SFP=False;persist security info=False;Extended
Properties=;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet
OLEDBon't Copy Locale on Compact=False;User ID=Admin;Jet OLEDB:Global
Bulk Transactions=1"
x_Con.Open()
End Sub

Public Sub CloCon()
x_Con.Close()
End Sub


Public Sub main()
Dim z_DatSet As New DataSet
Dim z_Row As DataRow
Dim z_TabNam As String
Dim z_Ada As OleDbDataAdapter
Dim z_CmdBld As OleDbCommandBuilder
Try
OpeCon()
z_TabNam = "TEST1"
z_Ada = New OleDbDataAdapter("SELECT * from " & z_TabNam &
" where T2 = 0", x_Con)
z_CmdBld = New OleDb.OleDbCommandBuilder(z_Ada)
z_Ada.FillSchema(z_DatSet, SchemaType.Source, z_TabNam)
z_Ada.Fill(z_DatSet, z_TabNam)
If z_DatSet.Tables(0).Rows.Count > 0 Then
For Each z_Row In z_DatSet.Tables(0).Rows
z_Row("T2") = 1
Next
z_Ada.Update(z_DatSet, z_TabNam)
End If
Catch ex As Exception
MsgBox(ex.ToString)
Finally
CloCon()
End Try
End Sub

 

[Miha Markic [MVP C#]]

Here we go again with commandbuilder.
I strongly suggest you to create explicit insert/update/delete statements
(perhaps with VS.NET wizard).
So you always know what it is doing...

 

[Cor Ligthert [MVP]]

RC,

I got the same error, I assume that it is because the notation of a DateTime
in a Access database is different from the internal notation in Net. In Net
is it in ticks from a duration of 100 nanoseconds from 1-1-1 while it is in
Access ticks from a duration of 1000/3 milliseconds after the start of the
Gregorian calendar in the british empire. I have that somewhere in date I
thought it was 1-1-1753. (Not that it is important, they are different in
ticks and can that as well after recalculation, some are will be good some
will be wrong) This is exact the behaviour you told .

I have tested your sample with string as first and than it goes very well.

I have made the code from you more cleaner in my idea. Maybe you want to use
it.

\\\
Dim conn As New OleDb.OleDbConnection _
("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\test1\Test.mdb")
Dim da As New OleDb.OleDbDataAdapter _
("SELECT * from Test1 where T2 = 0", conn)
Dim ds As New DataSet
Dim cmb As New OleDb.OleDbCommandBuilder(da)
da.Fill(ds, "Test1")
If ds.Tables(0).Rows.Count > 0 Then
For Each dr As DataRow In ds.Tables(0).Rows
dr("T2") = 1
Next
Try
da.Update(ds, "Test1")
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End If
///

Could not help but better is of course to know it so far.

Therefore I hope it helps anyway,

If you want a solution for that, than tell first if the data has to be in
sequence of the datetime.

Cor

 

[rc]

Hello Cor,

I suspected this exception occurs because of milliseconds, thanks a lot
for this precious information about notations.
After many tests, I think it's a bug in DAO support in framework 1.1,
because if you try to insert and update datetime values (in a primary
key) through DAO or ADO in the framework 2.0 there is no problems.
However if you insert datetime values in a primary key in the framework
1.1 with DAO and then try to update with ADO, the exception can occurs.

This example of code will always throw an exception in framework 2.0
whereas in framework 1.1 the MsgBox("bug") appears after some tests.

Public Sub main()

While True
Try

Dim zDb_AS As DAO.Database
Dim zRs_His As DAO.Recordset
Dim z_Dat As Date
Dim z_DatSet As New DataSet
Dim z_Row As DataRow
Dim z_TabNam As String
Dim z_Ada As OleDbDataAdapter
Dim z_CmdBld As OleDbCommandBuilder

z_Dat = Now

zDb_AS = OpeMdb(x_PatDb)
zRs_His = zDb_AS.OpenRecordset("TEST1",
DAO.RecordsetTypeEnum.dbOpenTable)
zRs_His.AddNew()
zRs_His.Fields("T1").Value = z_Dat
zRs_His.Fields("T2").Value = 0
zRs_His.Update()
zRs_His.Close()
zDb_AS.Close()

OpeCon()
z_TabNam = "TEST1"
z_Ada = New OleDbDataAdapter("SELECT * from " &
z_TabNam & " where T2 = 0", x_Con)
z_CmdBld = New OleDb.OleDbCommandBuilder(z_Ada)
'z_Ada.FillSchema(z_DatSet, SchemaType.Source,
z_TabNam)
z_Ada.Fill(z_DatSet, z_TabNam)
z_Row = z_DatSet.Tables(0).NewRow
z_Row.Item(0) = z_Dat
z_Row.Item(1) = 0
z_DatSet.Tables(0).Rows.Add(z_Row)
z_Ada.Update(z_DatSet, z_TabNam)
MsgBox("Bug")
CloCon()

Catch ex As Exception
'MsgBox(ex.ToString)
CloCon()
End Try
End While

End Sub


So it seems to have been fixed in framework 2.0, but in my case data to
update has been inserted with DAO and framework 1.1
So I think there are two solutions :
- If I fill a dataset with all table records, delete them from the
database and then insert them again, they will be updated correctly ?
Could there be any errors when converting ticks (same values etc...) ?
- Modify table structure and add an auto increment column (but it
implies many changes in my application code).

What do you think about the first solution ? (Or have you another
solution ?)

Thanks you.

 

[Cor Ligthert [MVP]]

rc,

I am not sure because it is Access.

Therefore I would just make ISO DateTimeStrings from your key.

Just one routine who makes from a datetime a string
yyyyMMddhhmmss

While it can of course as well with this single instruction.
http://msdn.microsoft.com/library/d...globalizationdatetimeformatinfoclasstopic.asp

And back with the single instruction datetime.parseexact
http://msdn.microsoft.com/library/d...l/frlrfsystemdatetimeclassparseexacttopic.asp

Where you don't have to give a culture in this case because you are not
using a month in characters. (null or nothing does the job which is than
default).

I hope this helps,

Cor




"rc" <[email protected]> schreef in bericht