DBConcurrencyException in Update()

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

Miha
Thu Feb 02 04:33:45 CST 2006

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

Bart
Thu Feb 02 06:46:37 CST 2006

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


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

Marina
Thu Feb 02 09:36:25 CST 2006

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

Miha
Thu Feb 02 09:56:21 CST 2006

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

Marina
Thu Feb 02 14:01:25 CST 2006

Thank you!

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

latin
Fri Feb 03 00:21:22 CST 2006

thanks a LOT everyone! :)

Marina Levit [MVP] wrote:
>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