G
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 OLEDB
atabase 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
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 OLEDB
atabase 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=Falsecm = 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