G
Guest
I created a group of 12 pretty typical databound forms around a currency manager. They all worked perfectly, patterned after chapter 13 of ADO.NET, Core Reference, published by Microsoft Press. In one case, the DataAdapter daDT filled the dataset instance dsDT1 with one table, DateType. The table had three columns: ID(autoincrement), DateType(string), and Active(boolean - default true). Two textboxes and a checkbox. The user could view the rows one by one with the usual <<<>>>, and edit, add, delete, update(the dataset, which was merely ending the edit), and updating the ms access database(submitChanges button). Also a cancel button. Pretty run of the mill
Nevertheless, it acted quirky from time to time. One MVP took it upon himself to help me. It befuddled him as well. Finally he asked me to email the solution to time. After several days debugging, he found the problem. daDT.fill(dsDT1.DateType) caused the quirks. Instead I should use daDT.Fill(dsDT1, "DateType"). That solved the quirks
Then I decided to make the form more visually appealing. I added a datagrid called dgrd whose datasource in the property box was dsdt1.datetype. I used the mousedown event and the hittest method to synchronize changes in the grid to the currency manager , and the handler for cm.positionchanged to synchronize the grid with changes in the position in the currency manager. Not hard, just adapted from the April 2004 msdn dvd
Everything worked but the Add button. If I clicked Add, the textbox for DateType wouldn't clear. A row would be added to the grid with <null> in the DateType column, and various exceptions would follow depending on the button I clicked next
I submitted a question more than a week ago and Cor Ligthert endeavored to help me. I thank him for that. We tried dataviews three different ways. In the form_load event I set the dgrd.DataSource = (DsDT1("DateType")), but the IDE wouldn't accept it. Cor suggested dgrd.DataSource = DsDT1.Tables("DataType"). It didn't help
The problem has to be a defect in ADO.NET similar to the previous one. Rather than eliminate the grid, which adds delightful visual cues to the user, can somebody figure out a workaround
Code follows. Thanks. polynomial5
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
'Dim dv As New DataView(DsDT1.Tables("DateType")
'cm = CType(BindingContext(dv), CurrencyManager
'dgrd.DataSource = DsDT1.Tables("DataType") ' or something like that
'dgrd.DataSource = d
'dgrd.DataSource = (DsDT1("DateType")) ',"DateType"
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.Cr
End Try
SetEditMode(False)
End Sub
Private Sub DisplayPosition()
lblPosition.Text = cm.Position + 1 & " of " & cm.Count
dgrd.NavigateTo(cm.Position, "")
End Sub
Private Sub cm_ItemChanged(ByVal sender As Object, ByVal e As ItemChangedEventArgs)
DisplayPosition()
End Sub
Private Sub cm_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs)
DisplayPosition()
End Sub
Private Sub btnMoveFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveFirst.Click
cm.Position = 0
End Sub
Private Sub btnMovePrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMovePrevious.Click
cm.Position -= 1
End Sub
Private Sub btnMoveNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveNext.Click
cm.Position += 1
End Sub
Private Sub btnMoveLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveLast.Click
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()
DsDT1.Tables("DateType").Rows.Add(DsDT1.Tables("DateType").NewRow)
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 = 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
'Case System.Windows.Forms.DataGrid.HitTestType.None
' Console.WriteLine("You clicked the background.")
'Case System.Windows.Forms.DataGrid.HitTestType.Cell
' MsgBox("You clicked cell at row " & hti.Row & ", col " & hti.Column)
' iRow = hti.Row
' cm.Position = iRow
'Case System.Windows.Forms.DataGrid.HitTestType.ColumnHeader
' Console.WriteLine("You clicked the column header for column " & hti.Column)
'Case System.Windows.Forms.DataGrid.HitTestType.RowHeader
' MsgBox("You clicked the row header for row " & hti.Row)
'Case System.Windows.Forms.DataGrid.HitTestType.ColumnResize
' Console.WriteLine("You clicked the column resizer for column " & hti.Column)
'Case System.Windows.Forms.DataGrid.HitTestType.RowResize
' Console.WriteLine("You clicked the row resizer for row " & hti.Row)
'Case System.Windows.Forms.DataGrid.HitTestType.Caption
' Console.WriteLine("You clicked the caption")
'Case System.Windows.Forms.DataGrid.HitTestType.ParentRows
' Console.WriteLine("You clicked the parent row")
End Select
End Sub
End Class
Nevertheless, it acted quirky from time to time. One MVP took it upon himself to help me. It befuddled him as well. Finally he asked me to email the solution to time. After several days debugging, he found the problem. daDT.fill(dsDT1.DateType) caused the quirks. Instead I should use daDT.Fill(dsDT1, "DateType"). That solved the quirks
Then I decided to make the form more visually appealing. I added a datagrid called dgrd whose datasource in the property box was dsdt1.datetype. I used the mousedown event and the hittest method to synchronize changes in the grid to the currency manager , and the handler for cm.positionchanged to synchronize the grid with changes in the position in the currency manager. Not hard, just adapted from the April 2004 msdn dvd
Everything worked but the Add button. If I clicked Add, the textbox for DateType wouldn't clear. A row would be added to the grid with <null> in the DateType column, and various exceptions would follow depending on the button I clicked next
I submitted a question more than a week ago and Cor Ligthert endeavored to help me. I thank him for that. We tried dataviews three different ways. In the form_load event I set the dgrd.DataSource = (DsDT1("DateType")), but the IDE wouldn't accept it. Cor suggested dgrd.DataSource = DsDT1.Tables("DataType"). It didn't help
The problem has to be a defect in ADO.NET similar to the previous one. Rather than eliminate the grid, which adds delightful visual cues to the user, can somebody figure out a workaround
Code follows. Thanks. polynomial5
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
'Dim dv As New DataView(DsDT1.Tables("DateType")
'cm = CType(BindingContext(dv), CurrencyManager
'dgrd.DataSource = DsDT1.Tables("DataType") ' or something like that
'dgrd.DataSource = d
'dgrd.DataSource = (DsDT1("DateType")) ',"DateType"
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.Cr
End Try
SetEditMode(False)
End Sub
Private Sub DisplayPosition()
lblPosition.Text = cm.Position + 1 & " of " & cm.Count
dgrd.NavigateTo(cm.Position, "")
End Sub
Private Sub cm_ItemChanged(ByVal sender As Object, ByVal e As ItemChangedEventArgs)
DisplayPosition()
End Sub
Private Sub cm_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs)
DisplayPosition()
End Sub
Private Sub btnMoveFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveFirst.Click
cm.Position = 0
End Sub
Private Sub btnMovePrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMovePrevious.Click
cm.Position -= 1
End Sub
Private Sub btnMoveNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveNext.Click
cm.Position += 1
End Sub
Private Sub btnMoveLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveLast.Click
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()
DsDT1.Tables("DateType").Rows.Add(DsDT1.Tables("DateType").NewRow)
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 = 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
'Case System.Windows.Forms.DataGrid.HitTestType.None
' Console.WriteLine("You clicked the background.")
'Case System.Windows.Forms.DataGrid.HitTestType.Cell
' MsgBox("You clicked cell at row " & hti.Row & ", col " & hti.Column)
' iRow = hti.Row
' cm.Position = iRow
'Case System.Windows.Forms.DataGrid.HitTestType.ColumnHeader
' Console.WriteLine("You clicked the column header for column " & hti.Column)
'Case System.Windows.Forms.DataGrid.HitTestType.RowHeader
' MsgBox("You clicked the row header for row " & hti.Row)
'Case System.Windows.Forms.DataGrid.HitTestType.ColumnResize
' Console.WriteLine("You clicked the column resizer for column " & hti.Column)
'Case System.Windows.Forms.DataGrid.HitTestType.RowResize
' Console.WriteLine("You clicked the row resizer for row " & hti.Row)
'Case System.Windows.Forms.DataGrid.HitTestType.Caption
' Console.WriteLine("You clicked the caption")
'Case System.Windows.Forms.DataGrid.HitTestType.ParentRows
' Console.WriteLine("You clicked the parent row")
End Select
End Sub
End Class