Difficult Concurrency Exception

[Guest]

I'm having a difficult DBConcurrency Exception. A form consistst of 2 textboxes and a checkbox, bound through a currency manager to the table 'DateType' columns ID, DateType and Active. The DataAdapter is daDT, the instance of the dataset is dsDT1

All the code for the form is below.

There are Edit, Add, Delete, Update(end edit) SubmitChanges(update dataadapter), Cancel and Close buttons. The scheme was taken from Chapter 13 of Microsoft Press' ADO.NET Core Reference

If I open the form, Add a row, Update, Submit, everyhting is OK. If I delete the newly added, then Submit, I get a Concurrency error. My handler clears and refills the dataset in this case. The Added is still there. If I then delete the Added and Submit, OK. Or if I close the form, reopen and delete, OK. If I then add another new row, submit then delete, OK.

Similarly, If I open the form, add a row, edit it, then submit, concurrency error. After the refresh, if I edit again, OK. Or delete, OK.

It's only when I open the form and add a row for the first time that I get the concurrency error. Any help would be greatly appreciated

polynomial5

Option Strict O
Imports System.Dat
Imports System.Data.OleD
Imports System.i
Imports Microsoft.VisualBasi

Public Class frmDateTyp
Inherits System.Windows.Forms.For
Dim cm As CurrencyManage
Public DTypeOpened As Boolean = Fals


Private Sub frmDateType_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Loa

cnDT.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase Locking Mode=1;Jet OLEDBatabase Password=;Data Source=""" + System.IO.Path.GetDirectoryName(System.Windows.Forms.Application.ExecutablePath) + "\HasbaraSample.mdb"";Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False
cm = CType(BindingContext(DsDT1, "DateType"), CurrencyManager
AddHandler cm.ItemChanged, AddressOf cm_ItemChange
AddHandler cm.PositionChanged, AddressOf cm_PositionChange

Tr
daDT.Fill(DsDT1, "DateType"
DsDT1.DateType.Columns(0).DefaultValue = Tru
Catch ex As OleDbExceptio
Dim errorMessages As Strin
Dim i As Integer =
errorMessages += "Index #" & i.ToString() & ControlChars.Cr
& "Message: " & ex.Errors(i).Message & ControlChars.Cr
& "NativeError: " & ex.Errors(i).NativeError & ControlChars.Cr
& "Source: " & ex.Errors(i).Source & ControlChars.Cr
& "SQLState: " & ex.Errors(i).SQLState & ControlChars.C
End Tr

SetEditMode(False

End Su

Private Sub DisplayPosition(
lblPosition.Text = cm.Position + 1 & " of " & cm.Coun
dgrd.CurrentRowIndex = cm.Positio
'dgrd.NavigateTo(cm.Position, "DateType") ' "DateType"
End Su

Private Sub cm_ItemChanged(ByVal sender As Object, ByVal e As ItemChangedEventArgs
DisplayPosition(
End Su

Private Sub cm_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs
DisplayPosition(
End Su

Private Sub btnMoveFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveFirst.Clic
cm.Position =
End Su

Private Sub btnMovePrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMovePrevious.Clic
cm.Position -=
End Su

Private Sub btnMoveNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveNext.Clic
cm.Position +=
End Su

Private Sub btnMoveLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveLast.Clic
cm.Position = cm.Count - 1
End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
cm.AddNew()
SetEditMode(True)
End Sub

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
If cm.Count > 0 Then
cm.RemoveAt(cm.Position)
Else
MessageBox.Show("No Item to Delete!", "Delete Item", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
End Sub

Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click


Me.Close()

End Sub

Private Sub btnSubmitChanges_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmitChanges.Click
If DsDT1.HasChanges Then
DTypeOpened = False
Try
Dim intModified As Integer
intModified = daDT.Update(DsDT1, "DateType")
Dim strOutput As String
strOutput = "Modified " & intModified & " item(s)"
MessageBox.Show(strOutput, "Update succeeded!", MessageBoxButtons.OK, MessageBoxIcon.Information)
DTypeOpened = True
Dim s As String = ControlChars.CrLf
Catch ex As OleDbException
If ex.Errors(0).SQLState = "3022" Then
MsgBox(ex.Errors(0).Message & s & "Please try again")
DsDT1.Clear()
daDT.Fill(DsDT1, "DateType")
Exit Try
Else
Dim errorMessages As String
errorMessages += "Message: " & ex.Errors(0).Message & ControlChars.CrLf _
& "NativeError: " & ex.Errors(0).NativeError & ControlChars.CrLf _
& "Source: " & ex.Errors(0).Source & ControlChars.CrLf _
& "SQLState: " & ex.Errors(0).SQLState & ControlChars.CrLf _
& "The form will be closed"
MsgBox(errorMessages)
Me.Close()
End If
Catch ex As DBConcurrencyException
MsgBox(ex.Message & s & "The dataset will be refreshed." & s & "Then you can navigate to the row and update it again.")
DsDT1.Clear()
daDT.Fill(DsDT1, "DateType")
Exit Try
Catch ex As Exception
MsgBox(ex.GetType.ToString & s & ex.Message & s & ex.HelpLink & s & ex.StackTrace & s & ex.Source & s & "The form will be closed") '& s & ex.TargetSite)
Me.Close()

End Try
Else
MessageBox.Show("No changes to submit!", "SubmitChanges", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If


End Sub

Private Sub SetEditMode(ByVal blnEdit As Boolean)
'txtID.ReadOnly = Not blnEdit
txtDateType.ReadOnly = Not blnEdit
chkActive.Enabled = blnEdit

btnMoveFirst.Enabled = Not blnEdit
btnMovePrevious.Enabled = Not blnEdit
btnMoveNext.Enabled = Not blnEdit
btnMoveLast.Enabled = Not blnEdit

btnCancel.Enabled = blnEdit
btnUpdate.Enabled = blnEdit
btnEdit.Enabled = Not blnEdit
btnAdd.Enabled = Not blnEdit
btnDelete.Enabled = Not blnEdit
btnSubmitChanges.Enabled = Not blnEdit
End Sub




Private Sub btnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEdit.Click
If cm.Count > 0 Then
SetEditMode(True)
Else
MessageBox.Show("No Item to Edit!", "Edit Item", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
End Sub



Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
cm.EndCurrentEdit()
SetEditMode(False)
End Sub

Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
cm.CancelCurrentEdit()
SetEditMode(False)
End Sub




Private Sub dgrd_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgrd.MouseDown
Dim myGrid As DataGrid = CType(sender, DataGrid)
Dim hti As System.Windows.Forms.DataGrid.HitTestInfo
hti = myGrid.HitTest(e.X, e.Y)
Dim iRow As Integer
Select Case hti.Type
Case System.Windows.Forms.DataGrid.HitTestType.Cell, System.Windows.Forms.DataGrid.HitTestType.RowHeader
iRow = hti.Row
cm.Position = iRow
Case Else
End Select

End Sub

End Class

 

[David Sceppa]

This exception generally occurs if the DataRow are out of synch with
the contents of the corresponding row in the database. Are you generating
any data while inserting it into your database - auto-increment values,
defaults, etc.? If so, you'll need to re-fetch the contents of the row
after performing the insert by handling the DataAdapter's RowUpdated event
as described in Chapter 11.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.

 

[Guest]

David

Thank you very much for your reply. I've already used @@IDENTITY many times so here is what I did

At the class level I added Dim iID As Intege

In form_load I added AddHandler daDT.RowUpdated, AddressOf OnRowUpDate

I added the procedur

Private Sub OnRowUpDated(ByVal sender As Object, ByVal args As OleDb.OleDbRowUpdatedEventArgs

Dim cmd1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT @@IDENTITY", cnDT

If args.StatementType = StatementType.Insert The
'Retrieve the identity value and store it in the ID colum
iID = CInt(cmd1.ExecuteScalar

MsgBox(iID.ToString

End I

End Su

Then I started the application and opened frmDateTyp

I added ss. At the bottom of the grid a row appeared with the ID = 10. I clicked the Submit button. The msgbox reported 151. However, in the form the ID was still 10. So when I deleted it there was no surprise I received the concurrency error

After the clear and refill, ss showed an ID of 151. I added sss. It showed an ID of 152. When I clicked submit the message box showed 152. No concurrency error

How do I use the above to prevent the concurrency error in the first place? I don't understand why when I open the form and immediately add a row, I get the wrong ID, but thereafter I get the right ID

Thank you very much

polynomial5d

 

[David Sceppa]

It sounds like you retrieved the new identity value, but did not apply
it to the DataRow. You need to set the value of the ID column in your
DataRow to the value you retrieved in order for it to be used when
submitting subsequent changes to the DataRow.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.