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