I have a fairly simple little form with a tabbed container for adding some information to an SQL database. On one of the tab pages I have a few text boxes and a few combo boxes. At the top of this page is a binding navigator.
One thing that makes this a bit atypical is that rather than binding to a table in my database, it is bound (partly) to a data table I created in code in the form.
Things work mostly as expected except for one annoying issue that must be resolved.
When I click the plus sign in the navigator tool strip for the first time it does NOT add a row to my data table. It will on subsequent clicks but then the counter on the tool strip is always showing one more that what is actually in the data table. I just cant see how to fix this. Been googling for examples of binding navigators bound to tables IN CODE ONLY to no avail. All examples are bound to tables in a database. I can attach my form code if that helps (how do you attach files here?)
Please help!!
Ok, I'll see if I can paste the form code in here...
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Public Class CableNewForm
'define our objects for the temporary datatable
Dim Cores_DataTable As New DataTable
Dim Cores_DataRow As DataRow
Dim Cores_Binding As New BindingSource
Public Sub New()
' This call is required by the designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
'First tab page "Properties"
Try
Using connection As New SqlClient.SqlConnection(databaseOps.connectionString)
connection.Open()
Dim da As New SqlDataAdapter("select Manufacturer from Manufacturer", connection)
'The combo boxes are bound to the respective tables in the SQL database
Dim manufacturer_dt As New DataTable
da.Fill(manufacturer_dt)
If manufacturer_dt.Rows.Count > 0 Then
Me.ComboBoxManufacturer.DataSource = manufacturer_dt
Me.ComboBoxManufacturer.DisplayMember = "Manufacturer"
Me.ComboBoxManufacturer.ValueMember = "Manufacturer"
Me.ComboBoxManufacturer.SelectedIndex = 0
End If
da.SelectCommand.CommandText = "select libraryname from library"
Dim library_dt As New DataTable
da.Fill(library_dt)
If library_dt.Rows.Count > 0 Then
Me.ComboBoxLibrary.DataSource = library_dt
Me.ComboBoxLibrary.DisplayMember = "libraryname"
Me.ComboBoxLibrary.ValueMember = "libraryname"
Me.ComboBoxLibrary.SelectedIndex = 0
End If
da.SelectCommand.CommandText = "select Supplier from Supplier"
Dim supplier_dt As New DataTable
da.Fill(supplier_dt)
If supplier_dt.Rows.Count > 0 Then
Me.ComboBoxSupplier.DataSource = supplier_dt
Me.ComboBoxSupplier.DisplayMember = "Supplier"
Me.ComboBoxSupplier.ValueMember = "Supplier"
Me.ComboBoxSupplier.SelectedIndex = 0
End If
da.SelectCommand.CommandText = "select cabletypeName from cabletypes"
Dim cabletype_dt As New DataTable
da.Fill(cabletype_dt)
If cabletype_dt.Rows.Count > 0 Then
Me.ComboBoxCableType.DataSource = cabletype_dt
Me.ComboBoxCableType.DisplayMember = "cabletypeName"
Me.ComboBoxCableType.ValueMember = "cabletypeName"
Me.ComboBoxCableType.SelectedIndex = 0
End If
da.SelectCommand.CommandText = "select colorcode from colors"
Dim cablecolor_dt As New DataTable
da.Fill(cablecolor_dt)
If cablecolor_dt.Rows.Count > 0 Then
Me.ComboBoxCableColor.DataSource = cablecolor_dt
Me.ComboBoxCableColor.DisplayMember = "colorcode"
Me.ComboBoxCableColor.ValueMember = "colorcode"
Me.ComboBoxCableColor.SelectedIndex = 0
End If
End Using
'Set some defaults on the properties tab page
InsertedUserTextBox.Text = currentUser
InsertedDateTimePicker.Text = DateTime.Now.ToString("yyyy/MM/dd")
UpdatedDateTimePicker.Text = DateTime.Now.ToString("yyyy/MM/dd")
NumberOfCoresTextBox.Text = "0"
HasShieldCheckBox.Checked = False
HasGroundCheckBox.Checked = False
CableDefIdTextBox.Text = "-1"
Catch sqex As SqlClient.SqlException
MsgBox("Sql Exception: " & sqex.Message)
Catch ex As DataException
MsgBox(ex.Message)
End Try
'Second tab page "Cores"
Try
Using connection As New SqlClient.SqlConnection(databaseOps.connectionString)
connection.Open()
'Again, the combo boxes are bound to tables in the database
Using wiretype_da As New SqlDataAdapter("select wiretypename from wiretypes", connection)
Dim wiretype_dt As New DataTable
wiretype_da.Fill(wiretype_dt)
If wiretype_dt.Rows.Count > 0 Then
Me.ComboBoxWireType.DataSource = wiretype_dt
Me.ComboBoxWireType.DisplayMember = "wiretypename"
Me.ComboBoxWireType.ValueMember = "wiretypename"
Me.ComboBoxWireType.SelectedIndex = 0
End If
End Using
Using wiregauge_da As New SqlDataAdapter("select wiregaugecode from wiregauges", connection)
Dim wiregauge_dt As New DataTable
wiregauge_da.Fill(wiregauge_dt)
If wiregauge_dt.Rows.Count > 0 Then
Me.ComboBoxWireGauge.DataSource = wiregauge_dt
Me.ComboBoxWireGauge.DisplayMember = "wiregaugecode"
Me.ComboBoxWireGauge.ValueMember = "wiregaugecode"
Me.ComboBoxWireGauge.SelectedIndex = 0
End If
End Using
Using wirecolor_da As New SqlDataAdapter("select colorcode from colors", connection)
Dim wirecolor_dt As New DataTable
wirecolor_da.Fill(wirecolor_dt)
If wirecolor_dt.Rows.Count > 0 Then
Me.ComboBoxWireColor.DataSource = wirecolor_dt
Me.ComboBoxWireColor.DisplayMember = "colorcode"
Me.ComboBoxWireColor.ValueMember = "colorcode"
Me.ComboBoxWireColor.SelectedIndex = 0
End If
End Using
End Using
'Set some defaults on the cores tab page
CableDefIdTextBox1.Text = "-1"
Catch sqex As SqlClient.SqlException
MsgBox("Sql Exception: " & sqex.Message)
Catch ex As DataException
MsgBox(ex.Message)
End Try
'Create a temporary datatable for containing cable cores.
'When the "ok" button is clicked, these will be written to the database
Cores_DataTable.Columns.Add("CableWireDefId", Type.GetType("System.Int32"))
Cores_DataTable.Columns.Add("WireType", Type.GetType("System.String"))
Cores_DataTable.Columns.Add("CableDefId", Type.GetType("System.Int32"))
Cores_DataTable.Columns.Add("WireGauge", Type.GetType("System.String"))
Cores_DataTable.Columns.Add("WireGroup", Type.GetType("System.String"))
Cores_DataTable.Columns.Add("WireColor", Type.GetType("System.String"))
Cores_DataTable.Columns.Add("Voltage", Type.GetType("System.Double"))
Cores_DataTable.Columns.Add("Attenuation", Type.GetType("System.Double"))
Cores_DataTable.Columns.Add("Resistance", Type.GetType("System.Double"))
'Cores_DataTable.Constraints.Add("me", Cores_DataTable.Columns.Item("Cablewiredefid"), primaryKey:=True)
'Bind the datatable to the navigator object
Cores_Binding.DataSource = Cores_DataTable
Me.BindingNavigator1.BindingSource = Cores_Binding
'Bind the text fields
CableWireDefIdTextBox.DataBindings.Add("Text", Cores_Binding, "CableWireDefId")
WireGroupTextBox.DataBindings.Add("Text", Cores_Binding, "WireGroup")
VoltageTextBox.DataBindings.Add("Text", Cores_Binding, "Voltage")
AttenuationTextBox.DataBindings.Add("Text", Cores_Binding, "Attenuation")
ResistanceTextBox.DataBindings.Add("Text", Cores_Binding, "Resistance")
End Sub
Private Sub KillButton_Click(sender As Object, e As EventArgs) Handles KillButton.Click
Me.Close()
End Sub
Private Sub OkButton_Click(sender As Object, e As EventArgs) Handles OkButton.Click
'Here the new cable will be added to the database, its cores will come from our
'temporary data table defined in this form.
Me.Close()
End Sub
Private Sub TabControl1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles TabControl1.SelectedIndexChanged
Dim cabletype_index As Integer = 0
'Update the number of cores on the properties sheet
NumberOfCoresTextBox.Text = Cores_DataTable.Rows.Count
If Cores_DataTable.Rows.Count > 1 Then
cabletype_index = Me.ComboBoxCableType.FindString("Cable")
Me.ComboBoxCableType.SelectedIndex = cabletype_index
End If
'if one of the cores is a shield or ground, check the boxes in the properties tab
For Each row As DataRow In Cores_DataTable.Rows
If row.Item("WireType") = "Shield" Then
HasShieldCheckBox.Checked = True
cabletype_index = Me.ComboBoxCableType.FindString("Cable, shielded")
Me.ComboBoxCableType.SelectedIndex = cabletype_index
End If
If row.Item("WireType") = "Ground" Then
HasGroundCheckBox.Checked = True
End If
Next
End Sub
'Binding navigator overrides---------------------------------------------------------------------------------------------------
Private Sub fixComboBoxes()
'get wire specs by ID
Dim type_index As Integer = 0
'temporary vars that I can look at while debugging, to be removed later.
Dim temp As String = String.Empty
Dim temp2 As Object
For Each row As DataRow In Cores_DataTable.Rows
temp = row.Item("CableWireDefId").ToString()
If CableWireDefIdTextBox.Text = temp Then
temp2 = row.Item("WireType")
type_index = ComboBoxWireType.FindString(row.Item("WireType")) 'this can be null! fix it!
ComboBoxWireType.SelectedIndex = type_index
temp2 = row.Item("WireGauge")
type_index = ComboBoxWireGauge.FindString(row.Item("WireGauge"))
ComboBoxWireGauge.SelectedIndex = type_index
temp2 = row.Item("WireColor")
type_index = ComboBoxWireColor.FindString(row.Item("WireColor"))
ComboBoxWireColor.SelectedIndex = type_index
End If
Next
End Sub
Private Sub BindingNavigatorAddNewItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorAddNewItem.Click
Dim rownum As Int16 = (Convert.ToInt16(BindingNavigator1.PositionItem.Text) - 1)
'for some reason, this is not adding to cores_dt the first time it is clicked,
'but it does increment the counter on the navigator strip to 1
Cores_Binding.AddNew()
'this will add add a row to cores_dt if the first click didnt, but then it increments the
'counter again which will say 2 even though there is only 1 row in the datatable
If Cores_DataTable.Rows.Count = 0 Then
'Cores_Binding.AddNew()
End If
'need to manually add the selected index of comboboxes since they are not bound.
If Cores_DataTable.Rows.Count > 0 Then
Cores_DataTable.Rows(rownum).Item("CableWireDefId") = Convert.ToInt32(BindingNavigator1.PositionItem.Text)
Cores_DataTable.Rows(rownum).Item("WireType") = ComboBoxWireType.Text
Cores_DataTable.Rows(rownum).Item("WireGauge") = ComboBoxWireGauge.Text
Cores_DataTable.Rows(rownum).Item("WireColor") = ComboBoxWireColor.Text
End If
CableWireDefIdTextBox.Text = BindingNavigator1.PositionItem.Text
'Me.Cores_DataTable.AcceptChanges() 'this hoses it totally
'AddRowToDataTable()
'fixComboBoxes()
MsgBox("core count is " & Cores_DataTable.Rows.Count.ToString)
End Sub
Private Sub BindingNavigatorMoveFirstItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorMoveFirstItem.Click
Cores_Binding.MoveFirst()
CableWireDefIdTextBox.Text = BindingNavigator1.PositionItem.Text 'This will be wrong if a row is deleted! get number from datatable
fixComboBoxes()
End Sub
Private Sub BindingNavigatorMovePreviousItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorMovePreviousItem.Click
Cores_Binding.MovePrevious()
CableWireDefIdTextBox.Text = BindingNavigator1.PositionItem.Text 'This will be wrong if a row is deleted! get number from datatable
fixComboBoxes()
End Sub
Private Sub BindingNavigatorMoveNextItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorMoveNextItem.Click
Cores_Binding.MoveNext()
CableWireDefIdTextBox.Text = BindingNavigator1.PositionItem.Text 'This will be wrong if a row is deleted! get number from datatable
fixComboBoxes()
End Sub
Private Sub BindingNavigatorMoveLastItem_Click(sender As Object, e As EventArgs) Handles BindingNavigatorMoveLastItem.Click
Cores_Binding.MoveLast()
CableWireDefIdTextBox.Text = BindingNavigator1.PositionItem.Text 'This will be wrong if a row is deleted! get number from datatable
fixComboBoxes()
End Sub
Private Sub BindingNavigatorSaveItemButton_Click(sender As Object, e As EventArgs) Handles BindingNavigatorSaveItemButton.Click
'if the user hits save, but there are now rows in the datatable, add a row and save it with the values in the dialog box
'If Cores_DataTable.Rows.Count = 0 Then
'AddRowToDataTable()
'End If
'fixComboBoxes()
'Me.Cores_DataTable.AcceptChanges() ' if there is only one row when hitting save, it gets zeroed out!
'Me.Validate()
End Sub
End Class
What I have tried:
Been googling for examples of binding navigators bound to tables IN CODE ONLY to no avail. All examples are bound to tables in a database. Hacked the binding navigator "add" method to do a second add if there are zero rows in the data table, works but the counter in the tool strip is still higher than the number of rows.
Submit an article or tip