DBConcurrencyException in Update()

[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?

 

[Miha Markic [MVP C#]]

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

 

[Bart Mermuys]

Hi,

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


------------------>

 

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

 

[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/

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.

 

[Marina Levit [MVP]]

Thank you!

Hi Marina,

Congrats on new shiny MVP title!

 

[latin & geek via DotNetMonster.com]

thanks a LOT everyone!

Thank you!

Hi Marina,

[quoted text clipped - 30 lines]