How to make combo box show text values from foreign key values

[wirejp]

Hello,

I am using a MySQL database linked to Visual Studio Community Edition 2017 software. In the Visual Studio software, I would a combo box to display text values from the foreign key value.

I was following the approach used by Paul in post#3 of the attached linked:-

how to make combo box show values of foreign key values

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.

Continue reading...