6 Replies - 322 Views - Last Post: 14 March 2019 - 07:06 PM Rate Topic: -----

#1 wire_jp   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 43
  • Joined: 10-January 19

Comboboxes are not showing the next record using a bindingnavigator

Posted 01 March 2019 - 08:50 PM

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



Is This A Good Question/Topic? 0
  • +

Replies To: Comboboxes are not showing the next record using a bindingnavigator

#2 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6737
  • View blog
  • Posts: 27,741
  • Joined: 12-December 12

Re: Comboboxes are not showing the next record using a bindingnavigator

Posted 02 March 2019 - 03:37 AM

Please provide a clear description. What happens? What doesn't happen? At what lines in your code?
Was This Post Helpful? 0
  • +
  • -

#3 wire_jp   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 43
  • Joined: 10-January 19

Re: Comboboxes are not showing the next record using a bindingnavigator

Posted 02 March 2019 - 04:14 AM

Hi Andrewsw,

There are no errors or exceptions in my code, when I build a solution. After building the solution, when I navigate to the next record using the navigation record selector, only Colour combobox shows the next record while the other comboboxes display their first record.


Thanks,

wire_jp
Was This Post Helpful? 0
  • +
  • -

#4 wire_jp   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 43
  • Joined: 10-January 19

Re: Comboboxes are not showing the next record using a bindingnavigator

Posted 06 March 2019 - 05:26 PM

Thank you for all of the help. I was able to resolve my issue by using this vb.net code: -

        MysqlConn = New MySqlConnection()

        ' Define the SQL to grab data from table.
        CmdSQLSelectOrders = "SELECT * FROM Orders"

        'Connection String
        MysqlConn.ConnectionString =
          "server=localhost;Port=3306;database=mydatabase;userid=root;password=mypassword;persist security info=True"
        daOrders.MissingSchemaAction = MissingSchemaAction.AddWithKey
        ' Try, Catch, Finally
        Try
            MysqlConn.Open()

            MysqlCmd.Connection = MysqlConn
            MysqlCmd.CommandText = CmdSQLSelectOrders

            daOrders.SelectCommand = MysqlCmd
            daOrders.Fill(dtOrders)

            daSupplies = New MySqlDataAdapter("SELECT * FROM Supplies", MysqlConn)
            dtSupplies = New DataTable("Supplies")
            daSupplies.Fill(dtSupplies)
            dsOrders.Tables.Add(dtSupplies)

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

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

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

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

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

            Me.CboCatalogID_fk.DisplayMember = "CatalogDescription"
            Me.CboCatalogID_fk.ValueMember = "CatalogID"
            Me.CboCatalogID_fk.DataSource = CatalogBindingSource

            Me.CboYearID_fk.DisplayMember = "Year"
            Me.CboYearID_fk.ValueMember = "YearID"
            Me.CboYearID_fk.DataSource = YearBindingSource


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

            Me.CboColourID_fk.DisplayMember = "Colour"
            Me.CboColourID_fk.ValueMember = "ColourID"
            Me.CboColourID_fk.DataSource = OrderColourBindingSource

            Me.OrderBindingSource.DataSource = ds.Tables("dtSupplies")
            Me.OrderBindingSource.DataSource = ds.Tables("dtCatalog")
            Me.OrderBindingSource.DataSource = ds.Tables("dtYears")
            Me.OrderBindingSource.DataSource = ds.Tables("dtOrderTypes")
            Me.OrderBindingSource.DataSource = ds.Tables("dtOrderColours")

            Me.OrderBindingNavigator.BindingSource = Me.OrderBindingSource

            Me.CboSupplyID_fk.DataBindings.Add("SelectedValue", Me.OrderBindingSource, "SupplyID_fk", True)
            Me.CboCatalogID_fk.DataBindings.Add("SelectedValue", Me.OrderBindingSource, "CatalogID_fk", True)
            Me.CboYearID_fk.DataBindings.Add("SelectedValue", Me.OrderBindingSource, "YearID_fk", True)
            Me.CboOrderTypeID_fk.DataBindings.Add("SelectedValue", Me.OrderBindingSource, "OrderTypeID_fk", True)
            Me.CboColourID_fk.DataBindings.Add("SelectedValue", Me.OrderBindingSource, "ColourID_fk", True)

        Catch myerror As MySqlException
            MessageBox.Show("Cannot connect to database: " & myerror.Message)
        Finally
            MysqlConn.Close()
            MysqlConn.Dispose()
        End Try


This post has been edited by wire_jp: 06 March 2019 - 05:28 PM

Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is offline

  • Stealth IT
  • member icon

Reputation: 6737
  • View blog
  • Posts: 27,741
  • Joined: 12-December 12

Re: Comboboxes are not showing the next record using a bindingnavigator

Posted 07 March 2019 - 02:00 AM

I am glad that you managed to resolve the problem.

It would make this thread useful to future readers if you could briefly describe what the issue and resolution were.
Was This Post Helpful? 0
  • +
  • -

#6 wire_jp   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 43
  • Joined: 10-January 19

Re: Comboboxes are not showing the next record using a bindingnavigator

Posted 14 March 2019 - 06:57 PM

The Orders table contains four comboboxes in it: CboSupplyID_fk, CboCatalogID_fk, CboYearID_fk and CboColourID_fk. After I build the solution and I tried to navigator to the next record using the bindingnavigator "Next" record selector, only the CboColourID_fk combobox will move to the next record, while the other three comboboxes (i.e. CboSupply_fk combobox, CboCatalogID_fk combobox and CboYearID_fk combobox will remain stuck on the first record). There were no error messages or exceptions.


The code in post#1 was not working and so I decided to try a different approach. I applied the approach, using this example of two comboboxes: -

Imports System.Data.MySqlClient

Public Class Form2
    Private con As New MySqlConnection(My.Settings.SuppliesDBConnectionString)
    Private daOrders As New MySqlDataAdapter("Select ID, Author from Author", con)
    Private daSupplies As New MySqlDataAdapter("Select * from Supplies", con)
    Private ds As New DataSet
    Private dtOrders As New DataTable
    Private dtSupplies As New DataTable

    Private Sub Form2_Load(sender As Object, e As EventArgs) Handles Me.Load
        Try
            dtOrders.TableName = "dtOrders"
            ds.Tables.Add(dtOrders)
            daOrders.Fill(ds.Tables("dtOrders"))
            dtSupplies.TableName = "dtSupplies"
            ds.Tables.Add(dtSupplies)
            daSupplies.Fill(ds.Tables("dtSupplies"))

           
            Me.ComboBox1.DisplayMember = "SupplyName"
            Me.ComboBox1.ValueMember = "SupplyID"
            Me.ComboBox1.DataSource = ds.Tables("dtSupplies")

            Me.ComboBox2.DisplayMember = "Order"
            Me.ComboBox2.ValueMember = "OrderID"
            Me.ComboBox2.DataSource = ds.Tables("dtOrders")

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

            Me.ComboBox1.DataBindings.Add("SelectedValue", Me.BindingSource1, "SupplyName")
            Me.ComboBox2.DataBindings.Add("SelectedValue", Me.BindingSource1, "Order")

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


Was This Post Helpful? 1
  • +
  • -

#7 wire_jp   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 43
  • Joined: 10-January 19

Re: Comboboxes are not showing the next record using a bindingnavigator

Posted 14 March 2019 - 07:06 PM

Once I used this vb.net code, I was able to use the bindingnavigator "next" record selector to move to the next record and the Orders table now allows all four comboboxes to move to their next record (using the vb.net code in post#4).
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1