[RESOLVED] Make a combo box display text values from foreign key values

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.
The vb.net code is

Code:

---

mports 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=Employees;User ID=root;Password=mypassword"

        Dim ConnSQL3 As MySqlConnection = New MySqlConnection(conn3)
        CmdSQLSelect3 = "Select * from Projects"
        da3 = New MySqlDataAdapter(CmdSQLSelect3, ConnSQL3)
        Dim SQLCommandBuild3 As MySqlCommandBuilder = New MySqlCommandBuilder(da3)
        Dim ds3 As New DataSet
        Dim dt3 As New DataTable("Projects")
        da3.Fill(dt3)
        ds3.Tables.Add(dt3)
        CmdSQLSelect3 = "Select * from Employees"
        da3 = New MySqlDataAdapter(CmdSQLSelect3, ConnSQL3)
        dt3 = New DataTable("Employees")
        da3.Fill(dt3)
        ds3.Tables.Add(dt3)
        ds3.Relations.Add(New DataRelation("relation", ds3.Tables("Employees").Columns("EmployeeID"), ds3.Tables("Projects").Columns("EmployeeID_fk")))

        Dim bs3 As New BindingSource(ds3, "Employees")

        cboAuthorID_fk.DisplayMember = "EmployeeName"
        cboAuthorID_fk.ValueMember = "EmployeeID_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
        txtProjectID.DataBindings.Add("Text", Me.BindingSource3, "ProjectID")
        txtProjectName.DataBindings.Add("Text", Me.BindingSource3, "ProjectrName")
BindingSource1.Position = BindingSource1.Find("EmployeeID", IIf(txtEmployeeID.Text = "", 0, txtEmpolyeeID.Text))
BindingSource2.Position = BindingSource2.Find("ProjectVenueID", IIf(txtProjectVenueID.Text = "", 0, txtProjectVenueID.Text))
BindingSource3.Position = BindingSource3.Find("ProjectID", IIf(txtProjectID.Text = "", 0, txtProjectID.Text))
End Sub
End Class

---

The run build was successful. However when I selected the Projects 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: -

Code:

---

Exception Unhandled
System.ArgumentException: 'Cannot bind to the property or column ProjectID 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 Employees_Database.My.MyApplication.Main(String[] Args) in :line 81

---

Does you Employees table have a ProjectID column? If not, that's your issue.

Your code really is a mess. You should clean it up considerably. For one thing, don't use the same variable to refer to two different DataTables in such close proximity. Declare all the variables you need in a sensible order and give them sensible names. 'dt3' is very much NOT a sensible name. If you had a 'projectsTable' variable and an 'employeesTable' variable then it would be bleeding obvious which one you were using and thus when you were using the wrong one. You would also have 'projectsAdapter' and 'employeesAdapter' variables as well. Again, bleeding obvious what each one is. Names like BindingNavigator3 and BindingSource2 are equally as bad.

Hi jmcilhinney,

Thank you for your advice and help. I got the issue resolved.

Cheers