Results 1 to 6 of 6

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

  1. #1

    Thread Starter
    Member
    Join Date
    Aug 2018
    Posts
    46

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

    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: -

    Code:
    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
    Last edited by wire_jp; Mar 26th, 2019 at 07:49 PM.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

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

    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).

  3. #3

    Thread Starter
    Member
    Join Date
    Aug 2018
    Posts
    46

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

    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: -

    Code:
    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

  4. #4

    Thread Starter
    Member
    Join Date
    Aug 2018
    Posts
    46

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

    double post. I deleted the duplicated post.
    Last edited by wire_jp; Mar 26th, 2019 at 07:49 PM. Reason: double posting. Post deleted

  5. #5
    Fanatic Member kpmc's Avatar
    Join Date
    Sep 2017
    Posts
    1,012

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

    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:
    Code:
    .Fill(DataSet,"TableName")
    And the Adapter will create that table for you. From which point you can refer to the DataTable such as:
    Code:
    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:
    Code:
            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.

  6. #6

    Thread Starter
    Member
    Join Date
    Aug 2018
    Posts
    46

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

    Quote Originally Posted by kpmc View Post
    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:
    Code:
            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: -

    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:

    Code:
    System.NullReferenceException: 'Object reference not set to an instance of an object.'
    
    System.Data.DataColumnCollection.this[string].get returned Nothing.
    at the line:
    Code:
    dtCountries.Columns("CountryID").AutoIncrement = True
    Here is the vb.net code:
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width