none
How to make combo box show text values from foreign key values RRS feed

  • Question

  • Hello,

     I am using a MySQL database linked to Visual Studio Community Edition 2017 software. I have a form which contains a tabcontrol. Each tabcontrol contains a table from the MySQL database. In the Visual Studio software, I would like a combo box to display text values from the foreign key value.

    However it is throwing an exception unhandled error message in my vb.net code at the line:-

    ds3.Relations.Add(New DataRelation("relation", ds3.Tables("authors").Columns("AuthorID"), ds3.Tables("booktours").Columns("AuthorID_fk")))


    The vb.net code is attached: -

    Imports System.Data.SqlClient 
    Imports MySql.Data.MySqlClient 
    Public Class Form1 
    Dim bs1 As BindingSource 
    Dim dt3 As New DataTable
    Private da3 As New MySqlDataAdapter
    
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim conn3 As String
            Dim ds3 As New DataSet
    
            conn3 = "server=localhost;Port=3306;database=authors;User ID=root;Password=mypassword"
    
            Dim ConnSQL3 As MySqlConnection = New MySqlConnection(conn3)
            CmdSQLSelect3 = "Select * from booktour"
            da3 = New MySqlDataAdapter(CmdSQLSelect3, ConnSQL3)
            Dim SQLCommandBuild3 As MySqlCommandBuilder = New MySqlCommandBuilder(da3)
            da3.Fill(dt3)
    
            'Bind the DataTable to the UI via a BindingSource.
            BindingSource3.DataSource = dt3
            Me.BindingNavigator3.BindingSource = Me.BindingSource3
    
            da3 = New MySqlDataAdapter("Select * from authors", ConnSQL3)
            dt3 = New DataTable("authors")
            da.Fill(dt3)
            ds3.Tables.Add(dt3)
            ds3.Relations.Add(New DataRelation("relation", ds3.Tables("authors").Columns("AuthorID"), ds3.Tables("booktours").Columns("AuthorID_fk")))
    
            Dim bs1 As New BindingSource(ds3, "Authors")
    
            cboAuthorID_fk.DisplayMember = "AuthorName"
            cboAuthorID_fk.ValueMember = "AuthorID_fk"
            cboAuthorID_fk.DataSource = bs1
    End Sub
    End Class

    The exception unhandled error message is attached below: -

    Exception Unhandled
    System.NullReferenceException: 'Object reference not set to an instance of an object.'
    
    System.Data.DataTableCollection.this[string].get returned Nothing.
    
    System.NullReferenceException
      HResult=0x80004003
      Message=Object reference not set to an instance of an object.
      Source=Authors Database
      StackTrace:
       at Authors_Database.Form1.Form1_Load(Object sender, EventArgs e) in F:\Authors Folder\Visual Studio 2017 Professional (Community Edition)\Authors\Authors Database\Authors Database\Form1.vb:line 274
       at System.EventHandler.Invoke(Object sender, EventArgs e)
       at System.Windows.Forms.Form.OnLoad(EventArgs e)
       at System.Windows.Forms.Form.OnCreateControl()
       at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
       at System.Windows.Forms.Control.CreateControl()
       at System.Windows.Forms.Control.WmShowWindow(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.Form.WmShowWindow(Message& m)
       at System.Windows.Forms.Form.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
    
    Thank you in advance for the help.



    • Edited by wirejp Tuesday, September 18, 2018 2:15 PM
    Saturday, September 15, 2018 9:50 PM

Answers

  • Start with simple task - get list of the table names from your database and put them as a constants into static class.

    Do the same for a set of the fields in each table.

    As result your code will look like

    ds3.Relations.Add(

    New DataRelation(SourceRelations.relation,
    ds3.Tables(SourceTables.authors).Columns(SourceColumn.AuthorID),
    ds3.Tables(SourceTables.booktours).Columns(SourceColumn.AuthorID_fk)

    )
    )

    So, there will be no text strings in the code and no errors related with mistyping.


    Sincerely, Highly skilled coding monkey.


    • Edited by Andrey Belyakov Sunday, September 16, 2018 4:15 PM
    • Marked as answer by wirejp Thursday, September 20, 2018 5:29 PM
    Sunday, September 16, 2018 4:14 PM
  • Hello,

    I would suggest

    • Checking the spelling of tables involved with this operation.
    • Use the debugger to step through code leading up to the issue. Inspect objects and properties using the "local window".

    The issue is fairly obvious so take time to do the above.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by wirejp Thursday, September 20, 2018 5:29 PM
    Monday, September 17, 2018 12:29 PM
    Moderator
  • Hello,

    I got the last issue resolved in regard to the foreign key text value changing as one navigates through the records using the navigation menu. Thank you to Bonnie for pointing me in the right direction to solve the issue. I had to bind the Author's foreign key to the combo box's SelectedValue.

    The updated vb.net code is: -

    Imports System.Data.SqlClient 
    Imports MySql.Data.MySqlClient 
    Public Class Form1 
    Dim bs1 As BindingSource 
    Dim dt3 As New DataTable
    Private da3 As New MySqlDataAdapter
    
    Public Class Form1
    Dim dtBooktours As DataTable
    Dim dtAuthors As DataTable
    Dim daBooktours As MySqlDataAdapter
    Dim daAuthors As MySqlDataAdapter
    Dim bsBooktours As BindingSource
    Dim bsAuthors As BindingSource
    Dim bsBookEvent As BindingSource
    Dim bnBooktours As BindingNavigator
    
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim con As New MySqlConnection("server=localhost;Port=3306;database=authors;User ID=root;Password=mypassword")
            daBooktours = New MySqlDataAdapter("SELECT * FROM Booktours", con)
            Dim ds As New DataSet
            dtBooktours = New DataTable("Booktours")
            daBooktours.Fill(dtBooktours)
            ds.Tables.Add(dtBooktours)
            Dim cb As New MySqlCommandBuilder(daBooktours)
            daAuthors = New MySqlDataAdapter("SELECT * FROM Authors", con)
            dtAuthors = New DataTable("Authors")
            daAuthors.Fill(dtAuthors)
            ds.Tables.Add(dtAuthors)
            cb = New MySqlCommandBuilder(daAuthors)
    
            dtBooktours.Columns("BooktourID").AutoIncrement = True
            dtAuthors.Columns("AuthorID").AutoIncrement = True
    
            dtBooktours.Columns(0).AutoIncrementSeed = dtAuthors.Rows.Cast(Of DataRow).Max(Function(dr As DataRow) CInt(dr.Item(0))) + 1
            dtAuthors.Columns(0).AutoIncrementStep = 1
    
            dtAuthors.Columns(0).AutoIncrementSeed = dtAuthors.Rows.Cast(Of DataRow).Max(Function(dr) CInt(dr.Item(0))) + 1
            dtAuthors.Columns(0).AutoIncrementStep = 1
    
            ds.Relations.Add(New DataRelation("relation", ds.Tables("Authors").Columns("AuthorID"), ds.Tables("Booktours").Columns("AuthorID_fk")))
    
            bsAuthors = New BindingSource(ds, "Authors")
    
            cboAuthorID_fk.DisplayMember = "AuthorName"
            cboAuthorID_fk.ValueMember = "AuthorID"
            cboAuthorID_fk.DataSource = bsAuthors
    
    'bind the Authors' foreign key to the combobox's "SelectedValue"
    Me.cboAuthorID_fk.DataBindings.Add(New System.Windows.Forms.Binding("SelectedValue", Me.bsBooktours, "AuthorID_fk", True))
    
    bsBooktours = New BindingSource(bsAuthors, "relation")
    
    'Bind the DataTable to the UI via a BindingSource.
    bsBooktours.DataSource = dtBooktours
    Me.bnBooktours.BindingSource = Me.bsBooktours
    txtBooktourID.DataBindings.Add("Text", Me.bsBooktours, "BooktourID")
    txtBooktourName.DataBindings.Add("Text", Me.bsBooktours, "BooktourName")
    
    bsAuthors.Position = bsAuthors.Find("AuthorID", IIf(txtAuthorID.Text = "", 0, txtAuthorID.Text))
    bsBookEvent.Position = bsBookEvent.Find("BookEventID", IIf(txtBookEventID.Text = "", 0, txtBookEventID.Text))
    bsBooktours.Position = bsBooktours.Find("BooktourID", IIf(txtBooktourID.Text = "", 0, txtBooktourID.Text))
    End Sub
    End Class
    
    
    Private Sub cboAuthorID_fk_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboAuthorID_fk.SelectedIndexChanged
    Me.bsBoooktours.Position = Me.cboAuthorID_fk.SelectedIndex
    End Sub




    • Marked as answer by wirejp Thursday, September 20, 2018 5:12 PM
    • Edited by wirejp Thursday, September 20, 2018 5:39 PM
    Thursday, September 20, 2018 5:08 PM

All replies

  • Maybe there is no such table: “booktours”. Try “booktour”.


    Sunday, September 16, 2018 10:37 AM
  • Start with simple task - get list of the table names from your database and put them as a constants into static class.

    Do the same for a set of the fields in each table.

    As result your code will look like

    ds3.Relations.Add(

    New DataRelation(SourceRelations.relation,
    ds3.Tables(SourceTables.authors).Columns(SourceColumn.AuthorID),
    ds3.Tables(SourceTables.booktours).Columns(SourceColumn.AuthorID_fk)

    )
    )

    So, there will be no text strings in the code and no errors related with mistyping.


    Sincerely, Highly skilled coding monkey.


    • Edited by Andrey Belyakov Sunday, September 16, 2018 4:15 PM
    • Marked as answer by wirejp Thursday, September 20, 2018 5:29 PM
    Sunday, September 16, 2018 4:14 PM
  • When you create a relation in your dataset both table - master and SLAVE must be on place.

    Sincerely, Highly skilled coding monkey.

    Sunday, September 16, 2018 4:19 PM
  • Hello,

    I would suggest

    • Checking the spelling of tables involved with this operation.
    • Use the debugger to step through code leading up to the issue. Inspect objects and properties using the "local window".

    The issue is fairly obvious so take time to do the above.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by wirejp Thursday, September 20, 2018 5:29 PM
    Monday, September 17, 2018 12:29 PM
    Moderator
  • Hi,

    Thank for the your help. I stepped through the vb.net code with the debugger and I saw that the ("authors").Columns("AuthorID") was displaying "Nothing". I updated the vb.net code as follows: -

    Imports System.Data.SqlClient Imports MySql.Data.MySqlClient Public Class Form1 Dim bs1 As BindingSource Dim dt3 As New DataTable Private da3 As New MySqlDataAdapter Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load Dim conn3 As String conn3 = "server=localhost;Port=3306;database=authors;User ID=root;Password=mypassword" Dim ConnSQL3 As MySqlConnection = New MySqlConnection(conn3) CmdSQLSelect3 = "Select * from booktours" da3 = New MySqlDataAdapter(CmdSQLSelect3, ConnSQL3) Dim SQLCommandBuild3 As MySqlCommandBuilder = New MySqlCommandBuilder(da3) Dim ds3 As New DataSet Dim dt3 As New DataTable("booktours") da3.Fill(dt3) ds3.Tables.Add(dt3) CmdSQLSelect3 = "Select * from authors" da3 = New MySqlDataAdapter(CmdSQLSelect3, ConnSQL3) dt3 = New DataTable("authors") da3.Fill(dt3) ds3.Tables.Add(dt3) ds3.Relations.Add(New DataRelation("relation", ds3.Tables("authors").Columns("AuthorID"), ds3.Tables("booktours").Columns("AuthorID_fk"))) Dim bs3 As New BindingSource(ds3, "authors") cboAuthorID_fk.DisplayMember = "AuthorName" cboAuthorID_fk.ValueMember = "AuthorID_fk" cboAuthorID_fk.DataSource = bs3 Dim BindingSource3 As New BindingSource(bs3, "relation") 'Bind the DataTable to the UI via a BindingSource. BindingSource3.DataSource = dt3 Me.BindingNavigator3.BindingSource = Me.BindingSource3 txtBooktourID.DataBindings.Add("Text", Me.BindingSource3, "BooktourID") txtBooktourName.DataBindings.Add("Text", Me.BindingSource3, "BooktourName")

    BindingSource1.Position = BindingSource1.Find("AuthorID", IIf(txtAuthorID.Text = "", 0, txtAuthorID.Text))

    BindingSource2.Position = BindingSource2.Find("BookEventID", IIf(txtBookEventID.Text = "", 0, txtBookEventID.Text)) BindingSource3.Position = BindingSource3.Find("BooktourID", IIf(txtBooktourID.Text = "", 0, txtBooktourID.Text)) End Sub End Class

     

    The run build was successful. However when I selected the Booktours tabcontrol of the Form 1.vb, there was an occurrence of the application is in break mode (Your app has entered a break state, but there is no code to show because all threads were executing external code (typically system or framework code). An exception unhandled message was displayed: -

    Exception Unhandled
    System.ArgumentException: 'Cannot bind to the property or column BooktourID on the DataSource.
    Parameter name: dataMember'
    
    System.ArgumentException
      HResult=0x80070057
      Message=Cannot bind to the property or column BooktourID on the DataSource.
    Parameter name: dataMember
      Source=System.Windows.Forms
      StackTrace:
       at System.Windows.Forms.BindToObject.CheckBinding()
       at System.Windows.Forms.BindToObject.SetBindingManagerBase(BindingManagerBase lManager)
       at System.Windows.Forms.Binding.SetListManager(BindingManagerBase bindingManagerBase)
       at System.Windows.Forms.ListManagerBindingsCollection.AddCore(Binding dataBinding)
       at System.Windows.Forms.BindingsCollection.Add(Binding binding)
       at System.Windows.Forms.BindingContext.UpdateBinding(BindingContext newBindingContext, Binding binding)
       at System.Windows.Forms.Control.UpdateBindings()
       at System.Windows.Forms.Control.OnBindingContextChanged(EventArgs e)
       at System.Windows.Forms.Control.OnParentBindingContextChanged(EventArgs e)
       at System.Windows.Forms.Control.OnBindingContextChanged(EventArgs e)
       at System.Windows.Forms.Control.CreateControl()
       at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
       at System.Windows.Forms.TabPage.set_Visible(Boolean value)
       at System.Windows.Forms.TabControl.UpdateTabSelection(Boolean updateFocus)
       at System.Windows.Forms.TabControl.OnSelectedIndexChanged(EventArgs e)
       at System.Windows.Forms.TabControl.WmSelChange()
       at System.Windows.Forms.TabControl.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.SendMessage(HandleRef hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
       at System.Windows.Forms.Control.SendMessage(Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.Control.ReflectMessageInternal(IntPtr hWnd, Message& m)
       at System.Windows.Forms.Control.WmNotify(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.Form.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.CallWindowProc(IntPtr wndProc, IntPtr hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
       at System.Windows.Forms.NativeWindow.DefWndProc(Message& m)
       at System.Windows.Forms.Control.DefWndProc(Message& m)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.TabControl.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
     at Authors_Database.My.MyApplication.Main(String[] Args) in :line 81
    Thanks




    • Edited by wirejp Tuesday, September 18, 2018 2:04 PM
    Tuesday, September 18, 2018 2:44 AM
  • Hello,

    I figured out that the issue was due to how I had the binding sources setup. I have another issue: when the program build run, in the Projects tab control, the Employees combo box do not change text value name (i.e. it is still stuck on the 1st foreign key text value) as I advance forward in the records, using the navigation menu. However, if I press the combo box dropdown tab, it shows a list of all of the text values for each record. I would like the user to advance forward using the navigation menu, and the combo box is populated with each foreign key text value for each record which is retrieved from the MySQL database.

    The vb.net code is shown below:=

    Imports System.Data.SqlClient 
    Imports MySql.Data.MySqlClient 
    Public Class Form1 
    Dim bs1 As BindingSource 
    Dim dt3 As New DataTable
    Private da3 As New MySqlDataAdapter
    
    Public Class Form1
    Dim dtBooktours As DataTable
    Dim dtAuthors As DataTable
    Dim daBooktours As MySqlDataAdapter
    Dim daAuthors As MySqlDataAdapter
    Dim bsBooktours As BindingSource
    Dim bsAuthors As BindingSource
    Dim bsBookEvent As BindingSource
    Dim bnBooktours As BindingNavigator
    
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim con As New MySqlConnection("server=localhost;Port=3306;database=authors;User ID=root;Password=mypassword")
            daBooktours = New MySqlDataAdapter("SELECT * FROM Booktours", con)
            Dim ds As New DataSet
            dtBooktours = New DataTable("Booktours")
            daBooktours.Fill(dtBooktours)
            ds.Tables.Add(dtBooktours)
            Dim cb As New MySqlCommandBuilder(daBooktours)
            daAuthors = New MySqlDataAdapter("SELECT * FROM Authors", con)
            dtAuthors = New DataTable("Authors")
            daAuthors.Fill(dtAuthors)
            ds.Tables.Add(dtAuthors)
            cb = New MySqlCommandBuilder(daAuthors)
    
            dtBooktours.Columns("BooktourID").AutoIncrement = True
            dtAuthors.Columns("AuthorID").AutoIncrement = True
    
            dtBooktours.Columns(0).AutoIncrementSeed = dtAuthors.Rows.Cast(Of DataRow).Max(Function(dr As DataRow) CInt(dr.Item(0))) + 1
            dtAuthors.Columns(0).AutoIncrementStep = 1
    
            dtAuthors.Columns(0).AutoIncrementSeed = dtAuthors.Rows.Cast(Of DataRow).Max(Function(dr) CInt(dr.Item(0))) + 1
            dtAuthors.Columns(0).AutoIncrementStep = 1
    
            ds.Relations.Add(New DataRelation("relation", ds.Tables("Authors").Columns("AuthorID"), ds.Tables("Booktours").Columns("AuthorID_fk")))
    
            bsAuthors = New BindingSource(ds, "Authors")
    
            cboAuthorID_fk.DisplayMember = "AuthorName"
            cboAuthorID_fk.ValueMember = "AuthorID"
            cboAuthorID_fk.DataSource = bsAuthors
    
    bsBooktours = New BindingSource(bsAuthors, "relation")
    
    'Bind the DataTable to the UI via a BindingSource.
    bsBooktours.DataSource = dtBooktours
    Me.bnBooktours.BindingSource = Me.bsBooktours
    txtBooktourID.DataBindings.Add("Text", Me.bsBooktours, "BooktourID")
    txtBooktourName.DataBindings.Add("Text", Me.bsBooktours, "BooktourName")
    
    bsAuthors.Position = bsAuthors.Find("AuthorID", IIf(txtAuthorID.Text = "", 0, txtAuthorID.Text))
    bsBookEvent.Position = bsBookEvent.Find("BookEventID", IIf(txtBookEventID.Text = "", 0, txtBookEventID.Text))
    bsBooktours.Position = bsBooktours.Find("BooktourID", IIf(txtBooktourID.Text = "", 0, txtBooktourID.Text))
    End Sub
    End Class


    • Edited by wirejp Thursday, September 20, 2018 3:23 PM
    Thursday, September 20, 2018 3:22 PM
  • Hello,

    I got the last issue resolved in regard to the foreign key text value changing as one navigates through the records using the navigation menu. Thank you to Bonnie for pointing me in the right direction to solve the issue. I had to bind the Author's foreign key to the combo box's SelectedValue.

    The updated vb.net code is: -

    Imports System.Data.SqlClient 
    Imports MySql.Data.MySqlClient 
    Public Class Form1 
    Dim bs1 As BindingSource 
    Dim dt3 As New DataTable
    Private da3 As New MySqlDataAdapter
    
    Public Class Form1
    Dim dtBooktours As DataTable
    Dim dtAuthors As DataTable
    Dim daBooktours As MySqlDataAdapter
    Dim daAuthors As MySqlDataAdapter
    Dim bsBooktours As BindingSource
    Dim bsAuthors As BindingSource
    Dim bsBookEvent As BindingSource
    Dim bnBooktours As BindingNavigator
    
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim con As New MySqlConnection("server=localhost;Port=3306;database=authors;User ID=root;Password=mypassword")
            daBooktours = New MySqlDataAdapter("SELECT * FROM Booktours", con)
            Dim ds As New DataSet
            dtBooktours = New DataTable("Booktours")
            daBooktours.Fill(dtBooktours)
            ds.Tables.Add(dtBooktours)
            Dim cb As New MySqlCommandBuilder(daBooktours)
            daAuthors = New MySqlDataAdapter("SELECT * FROM Authors", con)
            dtAuthors = New DataTable("Authors")
            daAuthors.Fill(dtAuthors)
            ds.Tables.Add(dtAuthors)
            cb = New MySqlCommandBuilder(daAuthors)
    
            dtBooktours.Columns("BooktourID").AutoIncrement = True
            dtAuthors.Columns("AuthorID").AutoIncrement = True
    
            dtBooktours.Columns(0).AutoIncrementSeed = dtAuthors.Rows.Cast(Of DataRow).Max(Function(dr As DataRow) CInt(dr.Item(0))) + 1
            dtAuthors.Columns(0).AutoIncrementStep = 1
    
            dtAuthors.Columns(0).AutoIncrementSeed = dtAuthors.Rows.Cast(Of DataRow).Max(Function(dr) CInt(dr.Item(0))) + 1
            dtAuthors.Columns(0).AutoIncrementStep = 1
    
            ds.Relations.Add(New DataRelation("relation", ds.Tables("Authors").Columns("AuthorID"), ds.Tables("Booktours").Columns("AuthorID_fk")))
    
            bsAuthors = New BindingSource(ds, "Authors")
    
            cboAuthorID_fk.DisplayMember = "AuthorName"
            cboAuthorID_fk.ValueMember = "AuthorID"
            cboAuthorID_fk.DataSource = bsAuthors
    
    'bind the Authors' foreign key to the combobox's "SelectedValue"
    Me.cboAuthorID_fk.DataBindings.Add(New System.Windows.Forms.Binding("SelectedValue", Me.bsBooktours, "AuthorID_fk", True))
    
    bsBooktours = New BindingSource(bsAuthors, "relation")
    
    'Bind the DataTable to the UI via a BindingSource.
    bsBooktours.DataSource = dtBooktours
    Me.bnBooktours.BindingSource = Me.bsBooktours
    txtBooktourID.DataBindings.Add("Text", Me.bsBooktours, "BooktourID")
    txtBooktourName.DataBindings.Add("Text", Me.bsBooktours, "BooktourName")
    
    bsAuthors.Position = bsAuthors.Find("AuthorID", IIf(txtAuthorID.Text = "", 0, txtAuthorID.Text))
    bsBookEvent.Position = bsBookEvent.Find("BookEventID", IIf(txtBookEventID.Text = "", 0, txtBookEventID.Text))
    bsBooktours.Position = bsBooktours.Find("BooktourID", IIf(txtBooktourID.Text = "", 0, txtBooktourID.Text))
    End Sub
    End Class
    
    
    Private Sub cboAuthorID_fk_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboAuthorID_fk.SelectedIndexChanged
    Me.bsBoooktours.Position = Me.cboAuthorID_fk.SelectedIndex
    End Sub




    • Marked as answer by wirejp Thursday, September 20, 2018 5:12 PM
    • Edited by wirejp Thursday, September 20, 2018 5:39 PM
    Thursday, September 20, 2018 5:08 PM