Unable to save new changes where the primary key (parent table) in a tab control is linked to foreign key (child table) in another tab control of the

[wirejp]

Hello,


I have created an Authors database using MySQL database, Visual Studio Community Edition 2017 software, Winform and vb.net. I have a form which contains a tabcontrol. Each tabcontrol contains a table from the MySQL database. I am experiencing an issue where I am unable to save new changes where the primary key (parent table) in a tab control is linked to foreign key (child table) in another tab control of the form. In the MySQL database, I ensured that the foreign keys between the parent tables and the child tables are configured to cascade on update, so that any changes in the value in the parent column will automatically propagate the child column of any related records.

The vb.net code is: -

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

Public Class Form1
Dim dtBooktours As DataTable
Dim dtAuthors As DataTable
Dim daBooktours As MySqlDataAdapter
Dim daAuthors As MySqlDataAdapter
Dim bsBooktours As BindingSource
Dim bsAuthors As BindingSource
Dim bsBookEvent As BindingSource
Dim bnBooktours As BindingNavigator

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim con As New MySqlConnection("server=localhost;Port=3306;database=authors;User ID=root;Password=mypassword")
daBooktours = New MySqlDataAdapter("SELECT * FROM Booktours", con)
Dim ds As New DataSet
dtBooktours = New DataTable("Booktours")
daBooktours.Fill(dtBooktours)
ds.Tables.Add(dtBooktours)
Dim cb As New MySqlCommandBuilder(daBooktours)
daAuthors = New MySqlDataAdapter("SELECT * FROM Authors", con)
dtAuthors = New DataTable("Authors")
daAuthors.Fill(dtAuthors)
ds.Tables.Add(dtAuthors)
cb = New MySqlCommandBuilder(daAuthors)

dtBooktours.Columns("BooktourID").AutoIncrement = True
dtAuthors.Columns("AuthorID").AutoIncrement = True

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

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

ds.Relations.Add(New DataRelation("relation", ds.Tables("Authors").Columns("AuthorID"), ds.Tables("Booktours").Columns("AuthorID_fk")))

bsAuthors = New BindingSource(ds, "Authors")

cboAuthorID_fk.DisplayMember = "AuthorName"
cboAuthorID_fk.ValueMember = "AuthorID"
cboAuthorID_fk.DataSource = bsAuthors

'bind the Authors' foreign key to the combobox's "SelectedValue"
Me.cboAuthorID_fk.DataBindings.Add(New System.Windows.Forms.Binding("SelectedValue", Me.bsBooktours, "AuthorID_fk", True))

bsBooktours = New BindingSource(bsAuthors, "relation")

'Bind the DataTable to the UI via a BindingSource.
bsBooktours.DataSource = dtBooktours
Me.bnBooktours.BindingSource = Me.bsBooktours
txtBooktourID.DataBindings.Add("Text", Me.bsBooktours, "BooktourID")
txtBooktourName.DataBindings.Add("Text", Me.bsBooktours, "BooktourName")

bsAuthors.Position = bsAuthors.Find("AuthorID", IIf(txtAuthorID.Text = "", 0, txtAuthorID.Text))
bsBookEvent.Position = bsBookEvent.Find("BookEventID", IIf(txtBookEventID.Text = "", 0, txtBookEventID.Text))
bsBooktours.Position = bsBooktours.Find("BooktourID", IIf(txtBooktourID.Text = "", 0, txtBooktourID.Text))
End Sub
End Class


Private Sub cboAuthorID_fk_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboAuthorID_fk.SelectedIndexChanged
Me.bsBoooktours.Position = Me.cboAuthorID_fk.SelectedIndex
End Sub


'Saving the new records to the MySQL databse from visual studio
Private Sub btnAuthorSave_Click(sender As Object, e As EventArgs) Handles btnAuthorSave.Click
Try

Me.bsAuthor.EndEdit()
Me.daAuthors.Update(dtAuthors)
MsgBox("Update successful")

Catch ex As Exception
MsgBox("Update failed")
End Try
End Sub

Private Sub btnBooktourSave_Click(sender As Object, e As EventArgs) Handles btnBooktourSave.Click
Try

Me.bsBooktour.EndEdit()
Me.daBooktours.Update(dtbBooktours)
MsgBox("Update successful")

Catch ex As Exception
MsgBox("Update failed")
End Try
End Sub




Thank you in advance for any help.

Continue reading...