[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.