[RESOLVED] Comboboxes are not showing the next record using the bindingnavigator record selector

[wire_jp]

Hello,

I am using a Winforms application which has a multitabbed form and it is linked to a MySQL database. The form has bindingsource and bindingnavigator controls. My basic database structure is has follows: -

Supplies
SupplyID (pk)
SupplyName


Catalog
CatalogID (pk)
CatalogName

Years
YearID (pk)
YearNumber

Colours
ColourID (pk)
ColourName


Orders
OrderID (pk)
OrderName
SupplyID_fk (fk)
CatalogID_fk (fk)
YearID_fk (fk)
ColourID_fk (fk)

The Orders table has four comboboxes in it: CboSupplyID_fk, CboCatalogID_fk, CboYearID_fk and CboColourID_fk. My problem is that is when I try to navigator to the next record using the bindingnavigator record selector (except for the CboColourID_fk, which works fine). The vb.net code is shown below: -

Imports MySql
Imports MySql.Data.MySqlClient
Imports MySql.Data

Public Class Form1
    Inherits Form

Dim cbCatalog As New MySqlCommandBuilder(daCatalog)
Dim cbYears As New MySqlCommandBuilder(daYears)
Dim cbOrderTypes As New MySqlCommandBuilder(daOrderTypes)
Dim cbOrderColours As New MySqlCommandBuilder(daOrderColours)
Dim dsCatalog As New DataSet
Dim dsYears As New DataSet
Dim dsOrderTypes As New DataSet
Dim dsOrderColours As New DataSet
Dim dsOrders As New DataSet
Dim dsSupplies As New DataSet
Dim dtCatalog As New DataTable
Dim dtYears As New DataTable
Dim dtOrderTypes As New DataTable
Dim dtOrderColours As New DataTable
Dim dtOrders As New DataTable
Dim dtOrderColours As New DataTable
Dim dtSupplies As New DataTable
Dim daCatalog As New MySqlDataAdapter
Dim daYears As New MySqlDataAdapter
Dim daOrderTypes As New MySqlDataAdapter
Dim daOrderColours As New MySqlDataAdapter
Dim daOrders As New MySqlDataAdapter
Dim daSupplies As New MySqlDataAdapter

Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown
MysqlConn.ConnectionString =
        "server=localhost;Port=3306;database=mydatabase;userid=root;password=mypassword;persist security info=True"
        daOrders = New MySqlDataAdapter("SELECT * FROM Orders", MysqlConn)
        daOrders.MissingSchemaAction = MissingSchemaAction.AddWithKey
        Dim dsOrders As New DataSet
        dtOrders = New DataTable("Orders")
        daOrders.Fill(dtOrders)
        dsOrders.Tables.Add(dtOrders)
        Dim cbOrders As New MySqlCommandBuilder(daOrders)
        daSupplies = New MySqlDataAdapter("SELECT * FROM Supplies", MysqlConn)
        dtSupplies = New DataTable("Supplies")
        daSupplies.Fill(dtSupplies)
        dsOrders.Tables.Add(dtSupplies)
        cbOrders = New MySqlCommandBuilder(daSupplies)

        daCatalog = New MySqlDataAdapter("SELECT * FROM Catalog", MysqlConn)
        dtCatalog = New DataTable("Catalog")
        daCatalog.Fill(dtCatalog)
        dsOrders.Tables.Add(dtCatalog)
        cbOrders = New MySqlCommandBuilder(daCatalog)

        daYears = New MySqlDataAdapter("SELECT * FROM Years", MysqlConn)
        dtYears = New DataTable("Years")
        daYears.Fill(dtYears)
        dsOrders.Tables.Add(dtYears)
        cbOrders = New MySqlCommandBuilder(daYears)


        daOrderTypes = New MySqlDataAdapter("SELECT * FROM OrderTypes", MysqlConn)
        dtOrderTypes = New DataTable("OrderTypes")
        daOrderTypes.Fill(dtOrderTypes)
        dsOrders.Tables.Add(dtOrderTypes)
        cbOrders = New MySqlCommandBuilder(daOrderTypes)

        daOrderColours = New MySqlDataAdapter("SELECT * FROM OrderColours", MysqlConn)
        dtOrderColours = New DataTable("OrderColours")
        daOrderColours.Fill(dtOrderColours)
        dsOrders.Tables.Add(dtOrderColours)
        cbOrders = New MySqlCommandBuilder(daOrderColours)

        dtOrders.Columns("OrderID").AutoIncrement = True
        dtSupplies.Columns("SupplyID").AutoIncrement = True

        dtOrders.Columns(0).AutoIncrementSeed = dtSupplies.Rows.Cast(Of DataRow).Max(Function(dr As DataRow) CInt(dr.Item(0))) + 1
        dtSupplies.Columns(0).AutoIncrementStep = 1

        dtSupplies.Columns(0).AutoIncrementSeed = dtSupplies.Rows.Cast(Of DataRow).Max(Function(dr) CInt(dr.Item(0))) + 1
        dtSupplies.Columns(0).AutoIncrementStep = 1

        dsOrders.Relations.Add(New DataRelation("relationSupply", dsOrders.Tables("Supplies").Columns("SupplyID"), dsOrders.Tables("Orders").Columns("SupplyID_fkey")))

        SupplyBindingSource = New BindingSource(dsOrders, "Supplies")

        CboSupplyID_fkey.DisplayMember = "SupplyNumber"
        CboSupplyID_fkey.ValueMember = "SupplyID"
        CboSupplyID_fkey.DataSource = SupplyBindingSource

        OrderBindingSource = New BindingSource(SupplyBindingSource, "relationSupply")

        'bind the Supply's foreign key to the combobox's "SelectedValue"
        Me.CboSupplyID_fkey.DataBindings.Add(New System.Windows.Forms.Binding("SelectedValue", Me.OrderBindingSource, "SupplyID_fkey", True))


        dtOrders.Columns("OrderID").AutoIncrement = True
        dtCatalog.Columns("CatalogCodeID").AutoIncrement = True

        dtOrders.Columns(0).AutoIncrementSeed = dtCatalog.Rows.Cast(Of DataRow).Max(Function(dr As DataRow) CInt(dr.Item(0))) + 1
        dtCatalog.Columns(0).AutoIncrementStep = 1

        dtCatalog.Columns(0).AutoIncrementSeed = dtCatalog.Rows.Cast(Of DataRow).Max(Function(dr) CInt(dr.Item(0))) + 1
        dtCatalog.Columns(0).AutoIncrementStep = 1

        dsOrders.Relations.Add(New DataRelation("relationCatalog", dsOrders.Tables("Catalog").Columns("CatalogCodeID"), dsOrders.Tables("Orders").Columns("CatalogCodeID_fk")))

        CatalogBindingSource = New BindingSource(dsOrders, "Catalog")

        CboCatalogCodeID_fk.DisplayMember = "CatalogDescription"
        CboCatalogCodeID_fk.ValueMember = "CatalogCodeID"
        CboCatalogCodeID_fk.DataSource = CatalogBindingSource

        OrderBindingSource = New BindingSource(CatalogBindingSource, "relationCatalog")

        'to clear previous binding and then add new binding
        CboSupplyID_fkey.DataBindings.Clear()

        'bind the Catalog Guide's foreign key to the combobox's "SelectedValue"
        Me.CboCatalogCodeID_fk.DataBindings.Add(New System.Windows.Forms.Binding("SelectedValue", Me.OrderBindingSource, "CatalogCodeID_fk", True))

        dtOrders.Columns("OrderID").AutoIncrement = True
        dtYears.Columns("YearID").AutoIncrement = True

        dtOrders.Columns(0).AutoIncrementSeed = dtYears.Rows.Cast(Of DataRow).Max(Function(dr As DataRow) CInt(dr.Item(0))) + 1
        dtYears.Columns(0).AutoIncrementStep = 1

        dtYears.Columns(0).AutoIncrementSeed = dtYears.Rows.Cast(Of DataRow).Max(Function(dr) CInt(dr.Item(0))) + 1
        dtYears.Columns(0).AutoIncrementStep = 1

        dsOrders.Relations.Add(New DataRelation("relationYears", dsOrders.Tables("Years").Columns("YearID"), dsOrders.Tables("Orders").Columns("YearID_fk")))

        YearBindingSource = New BindingSource(dsOrders, "Years")

        CboYearID_fk.DisplayMember = "YearNumber"
        CboYearID_fk.ValueMember = "YearID"
        CboYearID_fk.DataSource = YearBindingSource

        OrderBindingSource = New BindingSource(YearBindingSource, "relationYears")

        'to clear previous binding and then add new binding
        CboCatalogCodeID_fk.DataBindings.Clear()

        'bind the Year's foreign key to the combobox's "SelectedValue"
        Me.CboYearID_fk.DataBindings.Add(New System.Windows.Forms.Binding("SelectedValue", Me.OrderBindingSource, "YearID_fk", True))



        dtOrders.Columns("OrderID").AutoIncrement = True
        dtOrderTypes.Columns("OrderTypeID").AutoIncrement = True

        dtOrders.Columns(0).AutoIncrementSeed = dtOrderTypes.Rows.Cast(Of DataRow).Max(Function(dr As DataRow) CInt(dr.Item(0))) + 1
        dtOrderTypes.Columns(0).AutoIncrementStep = 1

        dtOrderTypes.Columns(0).AutoIncrementSeed = dtOrderTypes.Rows.Cast(Of DataRow).Max(Function(dr) CInt(dr.Item(0))) + 1
        dtOrderTypes.Columns(0).AutoIncrementStep = 1

        dsOrders.Relations.Add(New DataRelation("relationOrderTypes", dsOrders.Tables("OrderTypes").Columns("OrderTypeID"), dsOrders.Tables("Orders").Columns("OrderTypeID_fk")))

        OrderTypeBindingSource = New BindingSource(dsOrders, "OrderTypes")

        CboOrderTypeID_fk.DisplayMember = "OrderType"
        CboOrderTypeID_fk.ValueMember = "OrderTypeID"
        CboOrderTypeID_fk.DataSource = OrderTypeBindingSource

        OrderBindingSource = New BindingSource(OrderTypeBindingSource, "relationOrderTypes")

        'to clear previous binding and then add new binding
        CboYearID_fk.DataBindings.Clear()

        'bind the Order Type's foreign key to the combobox's "SelectedValue"
        Me.CboOrderTypeID_fk.DataBindings.Add(New System.Windows.Forms.Binding("SelectedValue", Me.OrderBindingSource, "OrderTypeID_fk", True))


        dtOrders.Columns("OrderID").AutoIncrement = True
        dtOrderColours.Columns("ColourID").AutoIncrement = True

        dtOrders.Columns(0).AutoIncrementSeed = dtOrderColours.Rows.Cast(Of DataRow).Max(Function(dr As DataRow) CInt(dr.Item(0))) + 1
        dtOrderColours.Columns(0).AutoIncrementStep = 1

        dtOrderColours.Columns(0).AutoIncrementSeed = dtOrderColours.Rows.Cast(Of DataRow).Max(Function(dr) CInt(dr.Item(0))) + 1
        dtOrderColours.Columns(0).AutoIncrementStep = 1

        dsOrders.Relations.Add(New DataRelation("relationColours", dsOrders.Tables("OrderColours").Columns("ColourID"), dsOrders.Tables("Orders").Columns("ColourID_fk")))

        OrderColourBindingSource = New BindingSource(dsOrders, "OrderColours")

        cboColourID_fk.DisplayMember = "Colour"
        cboColourID_fk.ValueMember = "ColourID"
        cboColourID_fk.DataSource = OrderColourBindingSource

        OrderBindingSource = New BindingSource(OrderColourBindingSource, "relationColours")

        'to clear previous binding and then add new binding
        CboOrderTypeID_fk.DataBindings.Clear()

        'bind the Catalog Guide's foreign key to the combobox's "SelectedValue"
        Me.cboColourID_fk.DataBindings.Add(New System.Windows.Forms.Binding("SelectedValue", Me.OrderBindingSource, "ColourID_fk", True))


        'Bind the DataTable to the UI via a BindingSource.
        OrderBindingSource.DataSource = dtOrders
        Me.OrderBindingNavigator.BindingSource = Me.OrderBindingSource

        txtOrderID.DataBindings.Add("Text", Me.OrderBindingSource, "OrderID")
        txtOrderName.DataBindings.Add("Text", Me.OrderBindingSource, "OrderName")
End Sub
End Class

Thank you in advance for your help.

[OptionBase1]

You've got ComboBoxName.DataBindings.Clear() statements for each combo box you say isn't working correctly.

[wire_jp]

Hi Optionbase1,

I commented out the ComboBoxName.DataBindings.Clear() statements for each combo box (including the Colour combo box which is working), there were no changes: - all three combo boxes for Supplies, Year and Catalog do not work (i.e. they remain on the first record and they do not move to the next record when I navigate to the next record after I press the bindingnavigator record selector) while the combo box for Colour works (i.e. it moves to the next record, when I navigate to the next record using the bindingnavigator record selector).

[wes4dbt]

I don't really understand your code. Why so many bindingsources, why do you keep setting OrderBindingSource over and over again.

Here's a small example with two ComboBoxes, maybe this will help.

Imports System.Data.SqlClient

Public Class Form2
    Private con As New SqlConnection(My.Settings.BooksDBConnectionString)
    Private daAuthors As New SqlDataAdapter("Select ID, Author from Author", con)
    Private daBooks As New SqlDataAdapter("Select * from Books", con)
    Private ds As New DataSet
    Private dtAuthors As New DataTable
    Private dtBooks As New DataTable

    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles Me.Load
        Try
            dtAuthors.TableName = "dtAuthors"
            ds.Tables.Add(dtAuthors)
            daAuthors.Fill(ds.Tables("dtAuthors"))
            dtBooks.TableName = "dtBooks"
            ds.Tables.Add(dtBooks)
            daBooks.Fill(ds.Tables("dtBooks"))

            'Me.DataGridView1.DataSource = ds.Tables("dtAuthors")

            Me.ComboBox1.DisplayMember = "BookName"
            Me.ComboBox1.ValueMember = "BookName"
            Me.ComboBox1.SelectedValue = "BookName"
            Me.ComboBox1.DataSource = ds.Tables("dtBooks")

            Me.ComboBox2.DisplayMember = "Author"
            Me.ComboBox2.ValueMember = "Author"
            Me.ComboBox2.SelectedValue = "Author"
            Me.ComboBox2.DataSource = ds.Tables("dtAuthors")

            Me.BindingSource1.DataSource = ds.Tables("dtBooks")
            Me.BindingNavigator1.BindingSource = Me.BindingSource1

            Me.ComboBox1.DataBindings.Add("SelectedValue", Me.BindingSource1, "BookName")
            Me.ComboBox2.DataBindings.Add("SelectedValue", Me.BindingSource1, "Author")

        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
    End Sub
End Class

I didn't setup any relations in the dataset because I wasn't sure what their purpose was and I didn't want to confuse the Binding issue.

btw - I understand you want the combobox didsplayMember and ValueMember to be different but I didn't have time to setup that scenario but the logic doesn't really change.