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

12 Apr 2006 8:12 AM

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.

12 Apr 2006 8:57 AM

Cor Ligthert [MVP]

RC,

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

Cor

Show quote

"rc" <rcha***@protechnologies.fr> schreef in bericht
news:1144829557.449393.270550@t31g2000cwb.googlegroups.com...
> 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.
>

12 Apr 2006 9:14 AM

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

12 Apr 2006 12:22 PM

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.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Show quote

"rc" <rcha***@protechnologies.fr> wrote in message
news:1144829557.449393.270550@t31g2000cwb.googlegroups.com...
> 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.
>

12 Apr 2006 1:26 PM

joeblast

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?

Show quote

"rc" wrote:

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

12 Apr 2006 2:38 PM

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.

12 Apr 2006 3:11 PM

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

Show quote

"rc" <rcha***@protechnologies.fr> schreef in bericht
news:1144852696.811578.245950@e56g2000cwe.googlegroups.com...
> 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.
>

12 Apr 2006 3:32 PM

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 OLEDB:Database 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
OLEDB:Don'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

12 Apr 2006 4:41 PM

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

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Show quote

"rc" <rcha***@protechnologies.fr> wrote in message
news:1144855967.092538.288310@g10g2000cwb.googlegroups.com...
> 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 OLEDB:Database 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
> OLEDB:Don'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
>

12 Apr 2006 6:49 PM

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

Show quote

"rc" <rcha***@protechnologies.fr> schreef in bericht
news:1144855967.092538.288310@g10g2000cwb.googlegroups.com...
> 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 OLEDB:Database 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
> OLEDB:Don'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
>

13 Apr 2006 9:30 AM

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.

13 Apr 2006 9:58 AM

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/default.asp?url=/library/en-us/cpref/html/frlrfsystemglobalizationdatetimeformatinfoclasstopic.asp

And back with the single instruction datetime.parseexact
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/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

Show quote

"rc" <rcha***@protechnologies.fr> schreef in bericht

news:1144920651.108629.17660@g10g2000cwb.googlegroups.com...
> 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.
>