DBConcurrencyException in Update()

2 Feb 2006 9:49 AM

latin & geek via DotNetMonster.com

hi.
im at wits end! my update command isnt working properly. im able to edit and
delete data successfully with the same command builder, but when i add a new
row and update, i get an "affected zero records" message.

Dim cb2 As New OleDb.OleDbCommandBuilder(da2)
            dv.AllowNew = False
            gnrow = ds.Tables("exp").NewRow()
            ds.Tables("exp").Rows.Add(gnrow)
            da2.Update(ds, "exp")
            max1 = ds.Tables("exp").Rows.Count
            'assigning the new record to the open employee record
            ds.Tables("exp").Rows(max1 - 1).Item("emp") = wonly1
            da2.Update(ds, "exp")                        ------------------>
this is where the program seizes up.

will someone please tell me what to do to fix it?

--
it's all latin & geek to me! ;-)

Message posted via http://www.dotnetmonster.com

2 Feb 2006 10:33 AM

Miha Markic [MVP C#]

Avoid using builder. Instead create commands yourself or with a tool such as
CodeSmith.

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

"latin & geek via DotNetMonster.com" <u16975@uwe> wrote in message

news:5b4581b59c493@uwe...
Show quote

> hi.
> im at wits end! my update command isnt working properly. im able to edit
> and
> delete data successfully with the same command builder, but when i add a
> new
> row and update, i get an "affected zero records" message.
>
>
> Dim cb2 As New OleDb.OleDbCommandBuilder(da2)
>            dv.AllowNew = False
>            gnrow = ds.Tables("exp").NewRow()
>            ds.Tables("exp").Rows.Add(gnrow)
>            da2.Update(ds, "exp")
>            max1 = ds.Tables("exp").Rows.Count
>            'assigning the new record to the open employee record
>            ds.Tables("exp").Rows(max1 - 1).Item("emp") = wonly1
>            da2.Update(ds,
>        ------------------>
> this is where the program seizes up.
>
>
> will someone please tell me what to do to fix it?
>
> --
> it's all latin & geek to me! ;-)
>
> Message posted via http://www.dotnetmonster.com

2 Feb 2006 12:46 PM

Bart Mermuys

Hi,

"latin & geek via DotNetMonster.com" <u16975@uwe> wrote in message

news:5b4581b59c493@uwe...

> hi.
> im at wits end! my update command isnt working properly. im able to edit
> and
> delete data successfully with the same command builder, but when i add a
> new
> row and update, i get an "affected zero records" message.

Probely because your pk is autonumber. You need to retrieve the autonumber
after each insert.

Private WithEvents da2 As OleDbDataAdapter
Private cb2 As OleDbCommandBuilder
Private cmdGetIdentity as OleDbCommand

Private Sub SetupIdentityCmd()
    cmdGetIdentity = new OleDbCommand( _
         "SELECT @@IDENTITY", con)
End Sub

Private Sub SetupAdapter()
   da2 = new OleDbDataAdapter("SELECT * FROM ...", con)
   cb2 = new OleDbCommandBuilder(da2)
End Sub

Private Sub Test()
SetupIdentityCmd()
SetupAdapter()

gnrow = ds.Tables("exp").NewRow()
ds.Tables("exp").Rows.Add(gnrow)
da2.Update(ds, "exp")

'assigning the new record to the open employee record
max1 = ds.Tables("exp").Rows.Count
ds.Tables("exp").Rows(max1 - 1).Item("emp") = wonly1
da2.Update(ds, "exp")

End Sub

Private Sub da2_RowUpdated(ByVal sender As Object, ByVal e As
OleDbRowUpdatedEventArgs) Handles da2.RowUpdated
If e.Status = UpdateStatus.Continue AndAlso _
   e.StatementType = StatementType.Insert Then
     e.Row("ID") = CInt(cmdGetIdentity.ExecuteScalar)
     e.Row.AcceptChanges()
End If
End Sub

See
..http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp
(paragraph "Microsoft Access/JET Issues" )

HTH,
Greetings

        ------------------>
Show quote

> this is where the program seizes up.
>
>
> will someone please tell me what to do to fix it?
>
> --
> it's all latin & geek to me! ;-)
>
> Message posted via http://www.dotnetmonster.com

2 Feb 2006 3:36 PM

Marina Levit [MVP]

I am guessing you have some auto increment field or something. The first
Update calls adds the row and populates the field - but not in your dataset.
You need to make sure you get the correct value in there also, in this case.
Or just use GUID's, which is what I prefer since you can just assign the
value in the client program.

Additionally, I don't really see the point in inserting a new row, just to
immediately make a change to it and run an update? Why not finish setting
all the fields, and then call Update to insert it. Save yoruself a trip to
the database server.

"latin & geek via DotNetMonster.com" <u16975@uwe> wrote in message

news:5b4581b59c493@uwe...
Show quote

> hi.
> im at wits end! my update command isnt working properly. im able to edit
> and
> delete data successfully with the same command builder, but when i add a
> new
> row and update, i get an "affected zero records" message.
>
>
> Dim cb2 As New OleDb.OleDbCommandBuilder(da2)
>            dv.AllowNew = False
>            gnrow = ds.Tables("exp").NewRow()
>            ds.Tables("exp").Rows.Add(gnrow)
>            da2.Update(ds, "exp")
>            max1 = ds.Tables("exp").Rows.Count
>            'assigning the new record to the open employee record
>            ds.Tables("exp").Rows(max1 - 1).Item("emp") = wonly1
>            da2.Update(ds,
>        ------------------>
> this is where the program seizes up.
>
>
> will someone please tell me what to do to fix it?
>
> --
> it's all latin & geek to me! ;-)
>
> Message posted via http://www.dotnetmonster.com

2 Feb 2006 3:56 PM

Miha Markic [MVP C#]

Hi Marina,

Congrats on new shiny MVP title!

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

Show quote

"Marina Levit [MVP]" <someone@nospam.com> wrote in message
news:OVV$u5AKGHA.3352@TK2MSFTNGP12.phx.gbl...
>I am guessing you have some auto increment field or something. The first
>Update calls adds the row and populates the field - but not in your
>dataset. You need to make sure you get the correct value in there also, in
>this case. Or just use GUID's, which is what I prefer since you can just
>assign the value in the client program.
>
> Additionally, I don't really see the point in inserting a new row, just to
> immediately make a change to it and run an update? Why not finish setting
> all the fields, and then call Update to insert it. Save yoruself a trip to
> the database server.
>
> "latin & geek via DotNetMonster.com" <u16975@uwe> wrote in message
> news:5b4581b59c493@uwe...
>> hi.
>> im at wits end! my update command isnt working properly. im able to edit
>> and
>> delete data successfully with the same command builder, but when i add a
>> new
>> row and update, i get an "affected zero records" message.
>>
>>
>> Dim cb2 As New OleDb.OleDbCommandBuilder(da2)
>>            dv.AllowNew = False
>>            gnrow = ds.Tables("exp").NewRow()
>>            ds.Tables("exp").Rows.Add(gnrow)
>>            da2.Update(ds, "exp")
>>            max1 = ds.Tables("exp").Rows.Count
>>            'assigning the new record to the open employee record
>>            ds.Tables("exp").Rows(max1 - 1).Item("emp") = wonly1
>>            da2.Update(ds, ------------------>
>> this is where the program seizes up.
>>
>>
>> will someone please tell me what to do to fix it?
>>
>> --
>> it's all latin & geek to me! ;-)
>>
>> Message posted via http://www.dotnetmonster.com
>
>

2 Feb 2006 8:01 PM

Marina Levit [MVP]

Thank you!

Show quote

"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:OpUU3EBKGHA.3200@tk2msftngp13.phx.gbl...
> Hi Marina,
>
> Congrats on new shiny MVP title!
>
> --
> Miha Markic [MVP C#]
> RightHand .NET consulting & development www.rthand.com
> Blog: http://cs.rthand.com/blogs/blog_with_righthand/
>
> "Marina Levit [MVP]" <someone@nospam.com> wrote in message
> news:OVV$u5AKGHA.3352@TK2MSFTNGP12.phx.gbl...
>>I am guessing you have some auto increment field or something. The first
>>Update calls adds the row and populates the field - but not in your
>>dataset. You need to make sure you get the correct value in there also, in
>>this case. Or just use GUID's, which is what I prefer since you can just
>>assign the value in the client program.
>>
>> Additionally, I don't really see the point in inserting a new row, just
>> to immediately make a change to it and run an update? Why not finish
>> setting all the fields, and then call Update to insert it. Save yoruself
>> a trip to the database server.
>>
>> "latin & geek via DotNetMonster.com" <u16975@uwe> wrote in message
>> news:5b4581b59c493@uwe...
>>> hi.
>>> im at wits end! my update command isnt working properly. im able to edit
>>> and
>>> delete data successfully with the same command builder, but when i add a
>>> new
>>> row and update, i get an "affected zero records" message.
>>>
>>>
>>> Dim cb2 As New OleDb.OleDbCommandBuilder(da2)
>>>            dv.AllowNew = False
>>>            gnrow = ds.Tables("exp").NewRow()
>>>            ds.Tables("exp").Rows.Add(gnrow)
>>>            da2.Update(ds, "exp")
>>>            max1 = ds.Tables("exp").Rows.Count
>>>            'assigning the new record to the open employee record
>>>            ds.Tables("exp").Rows(max1 - 1).Item("emp") = wonly1
>>>            da2.Update(ds, ------------------>
>>> this is where the program seizes up.
>>>
>>>
>>> will someone please tell me what to do to fix it?
>>>
>>> --
>>> it's all latin & geek to me! ;-)
>>>
>>> Message posted via http://www.dotnetmonster.com
>>
>>
>
>

3 Feb 2006 6:21 AM

latin & geek via DotNetMonster.com

thanks a LOT everyone! :)

Marina Levit [MVP] wrote:
Show quote

>Thank you!
>
>> Hi Marina,
>>
>[quoted text clipped - 30 lines]
>>>>
>>>> will someone please tell me what to do to fix it?

--
it's all latin & geek to me! ;-)

Message posted via http://www.dotnetmonster.com