Refresh a combobox in a parent tabpage after making an entry in child tabpage

[wire_jp]

Hello,

I am using MySQL Database which is connected to Visual Studio Community 2017 Community Edition software. I have used a TabControl form, with tables in each TabPage. I am trying to refresh a combobox on the parent TabPage, after making a data entry in a textbox in the child TabPage. The child form has two textboxes: "txtBookID" and "txtBookName". The textbook "txtBookID" is autoincremented. The Parent form has a combobox called "CboBookID_fkey". If I make a new entry in the textbook "txtBookName", I would like the CboBookID_fkey, to update with the new SelectedItem. However, when I make an update in the textbox on the child TabPage, the "CboBookID_fkey" combobox do not automatically update with the changes. My vb.net code is shown below: -

Imports MySql
Imports MySql.Data.MySqlClient
Imports System.Configuration
Imports MySql.Data

Public Class Form1
    Inherits Form
Dim conString As String = "Server=localhost;Port=3306;Database=mydatabase;userid=root;password=password;persist security info=True"	
    Dim con As MySqlConnection = New MySqlConnection(conString)

Private Sub SelectBookName(cb As ComboBox)

        Dim con As New MySqlConnection(conString)

        Dim myCommand As MySqlCommand = connection.CreateCommand()
        myCommand.CommandText = "SELECT BookName from Books"
        con.Open()

        Dim reader As MySqlDataReader

        reader = myCommand.ExecuteReader()
        While reader.Read()
            cb.Items.Add(reader.GetString("BookName"))
        End While


        con.Close()

    End Sub

    Private Sub TabPage2_Click(sender As Object, e As EventArgs) Handles TabPage2.Click
        InitializeComponent()
        CboBookID_fkey.Items.Clear()
        SelectBookName(CboBookID_fkey)
    End Sub

End Class

[si_the_geek]

Should that code really be in the TabPage2.Click event?

Clearing the combobox should presumably be done inside SelectBookName (before it adds the new values), and the call to SelectBookName should probably be in an event that runs when the txtBookName value is added to the database (based on what you've told us, we can't tell what event that might be).

[wire_jp]

Hi si_the_geek,

I was referencing this youtube: https://www.youtube.com/watch?v=zGB-jFVUJiw and in the video, the presenter put the code in the Form1.Activate event. I tried that method, but it caused all of fields to be blank, and so I decided to use the TabPage2.Click event. My complete vb.net code is shown below: -

Imports MySql
Imports MySql.Data.MySqlClient
Imports System.Configuration
Imports MySql.Data
Imports System.Windows.Forms
Imports System.Drawing
Imports System
Imports System.Data
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Xml
Imports System.IO
Imports System.Text

Public Class Form1
    Inherits Form
Dim conString As String = "Server=localhost;Port=3306;Database=mydatabase;userid=root;password=password;persist security info=True"	
    Dim con As MySqlConnection = New MySqlConnection(conString)

Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown

        Dim MysqlConn As New MySqlConnection
        MysqlConn.ConnectionString =
        "server=localhost;Port=3306;database=mydatabase;userid=root;password=mypassword;persist security info=True"
        daBooks = New MySqlDataAdapter("SELECT * From Books", MysqlConn)

        Dim dtBooks As DataTable = New DataTable()
        daBooks.MissingSchemaAction = MissingSchemaAction.AddWithKey
        daBooks.Fill(dtBooks)
        dsBooks.Tables.Add(dtBooks)
        cbBooks = New MySqlCommandBuilder(daBooks)

        dtBooks.Columns("BookID").AutoIncrement = True
        dtBooks.Columns(0).AutoIncrementStep = 1

        'Bind the DataTable to the UI via a BindingSource.
        BookBindingSource.DataSource = dtBooks
        BookBindingNavigator.BindingSource = Me.BookBindingSource

        txtBookID.DataBindings.Add("Text", BookBindingSource, "BookID")
        txtBookName.DataBindings.Add("Text", BookBindingSource, "BookName")


        Dim dtAuthors As New DataTable

        daAuthors = New MySqlDataAdapter("SELECT * FROM Authors", MysqlConn)
        daAuthors.MissingSchemaAction = MissingSchemaAction.AddWithKey
        dtAuthors = New DataTable("Authors")
        daAuthors.Fill(dtAuthors)
        dsAuthors.Tables.Add(dtAuthors)
        daBooks = New MySqlDataAdapter("SELECT * FROM Books", MysqlConn)
        dtBooks = New DataTable("Books")
        daBooks.Fill(dtBooks)
        dsAuthors.Tables.Add(dtBooks)
        cbAuthors = New MySqlCommandBuilder(daBooks)

        dtAuthors.Columns("AuthorID").AutoIncrement = True
        dtBooks.Columns("BookID").AutoIncrement = True

        dtAuthors.Columns(0).AutoIncrementSeed = dtBooks.Rows.Cast(Of DataRow).Max(Function(dr As DataRow) CInt(dr.Item(0))) + 1
        dtBooks.Columns(0).AutoIncrementStep = 1

        dtBooks.Columns(0).AutoIncrementSeed = dtBooks.Rows.Cast(Of DataRow).Max(Function(dr) CInt(dr.Item(0))) + 1
        dtBooks.Columns(0).AutoIncrementStep = 1

        dsAuthors.Relations.Add(New DataRelation("relation", dsAuthors.Tables("Books").Columns("BookID"), dsAuthors.Tables("Authors").Columns("BookID_fkey")))

        BookBindingSource = New BindingSource(dsAuthors, "Books")

        CboBookID_fkey.DisplayMember = "BookName"
        CboBookID_fkey.ValueMember = "BookID"
        CboBookID_fkey.DataSource = BookBindingSource

        AuthorBindingSource = New BindingSource(BookBindingSource, "relation")

        'bind the Books' foreign key to the combobox's "SelectedValue"
        CboBookID_fkey.DataBindings.Add(New Binding("SelectedValue", AuthorBindingSource, "BookID_fkey", True))

        'Bind the DataTable to the UI via a BindingSource.
        AuthorBindingSource.DataSource = dtAuthors
        AuthorBindingNavigator.BindingSource = Me.AuthorBindingSource

        txtAuthorID.DataBindings.Add("Text", AuthorBindingSource, "AuthorID")
        txtAuthorName.DataBindings.Add("Text", AuthorBindingSource, "AuthorName")


        'if it didn't find the key, position = 1
        'you can also try any else proper event
        BookBindingSource.Position = BookBindingSource.Find("BookID", IIf(txtBookID.Text = "", 0, txtBookID.Text))
        AuthorBindingSource.Position = AuthorBindingSource.Find("AuthorID", IIf(txtAuthorID.Text = "", 0, txtAuthorID.Text))

End Sub

Private Sub SelectBookName(cb As ComboBox)

        Dim connection As New MySqlConnection(conString)

        Dim myCommand As MySqlCommand = connection.CreateCommand()
        myCommand.CommandText = "SELECT BookName from Books"
        connection.Open()

        Dim reader As MySqlDataReader

        reader = myCommand.ExecuteReader()
        While reader.Read()
            cb.Items.Add(reader.GetString("BookName"))
        End While


        connection.Close()

    End Sub

    Private Sub TabPage2_Click(sender As Object, e As EventArgs) Handles TabPage2.Click
        InitializeComponent()
        CboBookID_fkey.Items.Clear()
        SelectBookName(CboBookID_fkey)
    End Sub

End Class

[wire_jp]

double post. I deleted the duplicated post.

[kpmc]

From a quick look at that code I would advise you to use "Using" blocks so that your applicable objects are disposed of after use. Also, while there is nothing wrong with creating a datatable and adding it to a dataset, you can just have the fill method do that for you, simple use:

.Fill(DataSet,"TableName")

And the Adapter will create that table for you. From which point you can refer to the DataTable such as:

Dset.Tables("TableName")

Finally, to address your question, What you may consider is binding the SelectedValue to the same source table, while getting a distinct table for the datasource.

Something like this right after your Adapter.Update routine is a good place to start:

        Dim CboxDT As DataTable = Dset.Tables("MyTable").DefaultView.ToTable(True, "Column1").Copy 'Get distinct valuesfrom columns
        Cbox.DataSource = CboxDT
        Cbox.ValueMember = "Column1"
        Cbox.DisplayMember = "Column1"
        Cbox.DataBindings.Add("SelectedValue", Dset.Tables("MyTable"), "Column1")'This should be run only once in Load event

If you choose to have a differnt value for DisplayMember than a few more steps would need to be taken.

[wire_jp]

Finally, to address your question, What you may consider is binding the SelectedValue to the same source table, while getting a distinct table for the datasource.

Something like this right after your Adapter.Update routine is a good place to start:

        Dim CboxDT As DataTable = Dset.Tables("MyTable").DefaultView.ToTable(True, "Column1").Copy 'Get distinct valuesfrom columns
        Cbox.DataSource = CboxDT
        Cbox.ValueMember = "Column1"
        Cbox.DisplayMember = "Column1"
        Cbox.DataBindings.Add("SelectedValue", Dset.Tables("MyTable"), "Column1")'This should be run only once in Load event

If you choose to have a differnt value for DisplayMember than a few more steps would need to be taken.

Hi kpmc,

I am checking with you where should this be inserted? Is it to replace this code: -

        CboBookID_fkey.DisplayMember = "BookName"
        CboBookID_fkey.ValueMember = "BookID"
        CboBookID_fkey.DataSource = BookBindingSource

.

I replaced this code with your code snipptet but there was no change.

I tried to use the Using Statement around a block of code in the load event but it threw an error message:

System.NullReferenceException: 'Object reference not set to an instance of an object.'

System.Data.DataColumnCollection.this[string].get returned Nothing.

at the line:

dtCountries.Columns("CountryID").AutoIncrement = True

Here is the vb.net code:

        Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles MyBase.Shown
        Using con As New MySqlConnection("server=localhost;Port=3306;database=mydatabase;userid=root;password=mypassword;persist security info=True")
            Dim daBooks As New MySqlClient.MySqlDataAdapter
            con.Open()
            
            ' Define the command 
            Using MySqlcmd As New MySqlCommand
                MySqlcmd.Connection = con
                MySqlcmd.CommandType = CommandType.Text
                ' MySqlcmd.CommandText = MySqlStatement

                daBooks = New MySqlDataAdapter("SELECT * From Books", con)


                daBooks.MissingSchemaAction = MissingSchemaAction.AddWithKey
                daBooks.Fill(dsBooks, "Books")
                'daBooks.Fill(dtBooks)
                dsBooks.Tables.Add(dtBooks)
                cbBooks = New MySqlCommandBuilder(daBooks)

                    dtBooks.Columns("BookID").AutoIncrement = True
                    dtBooks.Columns(0).AutoIncrementStep = 1

                    'Bind the DataTable to the UI via a BindingSource.
                    BookBindingSource.DataSource = dtBooks
                    BookBindingNavigator.BindingSource = Me.BookBindingSource

                    txtBookID.DataBindings.Add("Text", BookBindingSource, "BookID")
                    txtBookCode.DataBindings.Add("Text", BookBindingSource, "BookCode")
                    txtBookName.DataBindings.Add("Text", BookBindingSource, "BookName")

                    con.Close()
                End Using

            End Using
End Sub