• Hello and welcome to our new forums. We upgraded our forum sites to a more robust and modern system which we hope you will enjoy. Be sure to check out your profile by clicking the button on the top right and configure your preferences, signature, time zone, avatar, etc. as you wish. If you need help with using this new forum'ware try the help link on the bottom right.

    Click here to review your account now.

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

wire_jp

Member
Joined
Mar 29, 2019
Messages
5
Programming Experience
Beginner
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. I tried to add a fill dataset in the INSERT event but it did not work. My vb.net code is shown below: -

VB.NET:
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

    Private Sub BtnAuthorSave_Click(sender As Object, e As EventArgs) Handles BtnAuthorSave.Click
        InsertAuthors()
    End Sub
    

    Public Sub InsertAuthors()

        Dim sql As String = "INSERT INTO authors (`AuthorID`, `BookID_fkey`,`AuthorName`,`AuthorNotes`) values (@authorid,@bookid_fkey,@authorname,@authornotes)"
        con = New MySqlConnection("Server=localhost;Port=3306;Database=mydatabase;userid=root;password=mypassword;persist security info=True")
        con.Open()
        Dim cmd = New MySqlCommand(sql, con)

        cmd.Parameters.AddWithValue("@authorid", Me.txtAuthorID.Text)
        cmd.Parameters.AddWithValue("@bookid_fkey", Me.CbobookID_fkey.SelectedItem(0).ToString)
        cmd.Parameters.AddWithValue("@authorname", Me.txtAuthorName.Text)
        cmd.Parameters.AddWithValue("@authornotes", Me.txtAuthorNotes.Text)
        
         Me.dsAuthors.Tables.Clear()
        Me.daAuthors.Fill(Me.dsAuthors, "Authors")


        cmd.ExecuteNonQuery()
        con.Close()
End Sub
End Class
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,047
Location
Sydney, Australia
Programming Experience
10+
What exactly do you think this does:
VB.NET:
Me.dsAuthors.Tables.Clear()
I'm guessing that you think it does something other than what it actually does. Read the documentation or even just pay attention to Intellisense and see what it actually does and ask yourself whether that is what you actually want to do there.
 

wire_jp

Member
Joined
Mar 29, 2019
Messages
5
Programming Experience
Beginner
The documentation say that It will remove all rows in the table. An exception is generated if the table has any enforced child relations that would cause child rows to be orphaned.
Ok, I do not want the enforced child relations to be orphaned, but rather to have the child relations to be updated with new data.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,047
Location
Sydney, Australia
Programming Experience
10+
No, it doesn't say that at all. You read the wrong documentation because you're calling the wrong method. You're calling Clear on the Tables property of the DataSet, not on the DataTable.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,047
Location
Sydney, Australia
Programming Experience
10+
No it doesn't! That's the Clear method of the DataSet itself, not of its Tables property, which is the one you're calling. Try this code to prove it to yourself:
VB.NET:
Module Module1

    Sub Main()
        Dim table1 As New DataTable

        table1.Columns.Add("Name", GetType(String))
        table1.Rows.Add("First")

        Dim table2 As New DataTable

        table2.Columns.Add("Name", GetType(String))
        table2.Rows.Add("Second")

        Dim data As New DataSet

        data.Tables.Add(table1)
        data.Tables.Add(table2)

        Console.WriteLine(table1.Rows.Count)
        Console.WriteLine(table2.Rows.Count)
        Console.WriteLine(data.Tables.Count)

        data.Tables.Clear()

        Console.WriteLine(table1.Rows.Count)
        Console.WriteLine(table2.Rows.Count)
        Console.WriteLine(data.Tables.Count)

        Console.ReadLine()
    End Sub

End Module
You really ought to acquaint yourself with the F1 key for context-sensitive Help. Then you can be sure that you're reading the documentation for the method you're actually calling rather than the one you think you're calling.
 

wire_jp

Member
Joined
Mar 29, 2019
Messages
5
Programming Experience
Beginner
The vb.net code clears all of the tables.

The DataTable.Clear Method clears the DataTable of all data.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,047
Location
Sydney, Australia
Programming Experience
10+
The vb.net code clears all of the tables.

The DataTable.Clear Method clears the DataTable of all data.
Right, and you're doing the wrong one. If your ComboBox is already bound to a DataTable and you want to clear what's in the control and replace it with new data then which of those two things should you be doing? Not the one you ARE doing.
 

wire_jp

Member
Joined
Mar 29, 2019
Messages
5
Programming Experience
Beginner
it is the DataTable.Clear Method is the one which I should be using.

I was thinking of creating this ComboValues event and calling it from the load event of my form:

VB.NET:
Sub ComboValues()

        Dim mycon As New MySqlConnection("Server=localhost;Port=3306;Database=mydatabase;userid=root;password=mypassword;persist security info=True")


        mycon.Open()
        dtAuthors.Clear()
        Dim daAuthors As New MySqlDataAdapter("select BookName,BookID from Books", mycon)
        daAuthors.Fill(dsAuthors, "Authors")
        CboBookID_fkey.DataSource = dtAuthors
        With CboBookID_fkey
            .DisplayMember = "BookName"
            .ValueMember = "BookID"
        End With
    End Sub


But the vb.net code throws an Exception Unhandled error message at the line: . Valuemember = "BookID"

VB.NET:
System.ArgumentException: 'Cannot bind to the new display member.
Parameter name: newDisplayMember'
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,047
Location
Sydney, Australia
Programming Experience
10+
Why are you referring to the DataTable directly some of the time:
VB.NET:
dtAuthors.Clear()
VB.NET:
CboBookID_fkey.DataSource = dtAuthors
and indirectly other times:
VB.NET:
daAuthors.Fill(dsAuthors, "Authors")
I wouldn't be surprised to learn that it's not the same DataTable each time. You need to learn to write consistent code. It will help you avoid mistakes and, if you haven't made such a mistake, avoid us wasting time trying to determine whether you have.
 
Top Bottom