-
Jan 28th, 2019, 12:27 PM
#1
Thread Starter
Member
[RESOLVED] Parent table issue re: value cannot be null. Parameter name: dataTable'
Hello,
I am using a MySQL database which is linked to a WinForm application in visual studio enterprise 2017. I created a multi-tabbed Form in visual studio with a "Countries" tab (parent table) with the following fields: -Country ID; Country Code and Country Name. I have "Persons" tab (child table) with the following fields: - Person ID, Country (foreign key) and Person Name.
I am able to retrieve data from the MySQL database to populate these tables. My issue is that I am unable to save updated changes or add new records in the "Countries" tab (parent table). My vb.net code is shown below
Code:
Imports MySql
Imports MySql.Data.MySqlClient
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim Countrydt As New DataTable
Dim MysqlConn = New MySqlConnection
MysqlConn.ConnectionString =
"server=localhost;Port=3306;database=database;userid=root;password=password;persistsecurityinfo=True"
Using adapter As MySqlDataAdapter = New MySqlDataAdapter("SELECT * From Countries", MysqlConn)
adapter.Fill(Countrydt)
End Using
'Bind the DataTable to the UI via a BindingSource.
Me.CountryBindingSource.DataSource = Countrydt
Me.CountryBindingNavigator.BindingSource = Me.CountryBindingSource
Me.txtCountryID.DataBindings.Add("Text", Me.CountryBindingSource, "CountryID")
Me.txtCountryCode.DataBindings.Add("Text", Me.CountryBindingSource, "CountryCode")
Me.txtCountryName.DataBindings.Add("Text", Me.CountryBindingSource, "CountryName")
Dim dtPerson As New DataTable
daPerson = New MySqlDataAdapter("SELECT * FROM Person", MysqlConn2)
Dim dsPerson As New DataSet
dtPerson = New DataTable("Person")
daPerson.Fill(dtPerson)
dsPerson.Tables.Add(dtPerson)
Dim cbPerson As New MySqlCommandBuilder(daPerson)
daCountries = New MySqlDataAdapter("SELECT * FROM Countries", MysqlConn)
dtCountries = New DataTable("Countries")
daCountries.Fill(dtCountries)
dsPerson.Tables.Add(dtCountries)
cbPerson = New MySqlCommandBuilder(daCountries)
dtPerson.Columns("PersonID").AutoIncrement = True
dtCountries.Columns("CountryID").AutoIncrement = True
dtPerson.Columns(0).AutoIncrementSeed = dtCountries.Rows.Cast(Of DataRow).Max(Function(dr As DataRow) CInt(dr.Item(0))) + 1
dtCountries.Columns(0).AutoIncrementStep = 1
dtCountries.Columns(0).AutoIncrementSeed = dtCountries.Rows.Cast(Of DataRow).Max(Function(dr) CInt(dr.Item(0))) + 1
dtCountries.Columns(0).AutoIncrementStep = 1
dsPerson.Relations.Add(New DataRelation("relation", dsPerson.Tables("Countries").Columns("CountryID"), dsPerson.Tables("Person").Columns("CountryID_fkey")))
CountryBindingSource = New BindingSource(dsPerson, "Countries")
cboCountryID_fkey.DisplayMember = "CountryName"
cboCountryID_fkey.ValueMember = "CountryID"
cboCountryID_fkey.DataSource = CountryBindingSource
PersonBindingSource = New BindingSource(CountryBindingSource, "relation")
'bind the Countries' foreign key to the combobox's "SelectedValue"
cboCountryID_fkey.DataBindings.Add(New Binding("SelectedValue", PersonBindingSource, "CountryID_fkey", True))
'Bind the DataTable to the UI via a BindingSource.
PersonBindingSource.DataSource = dtPerson
Me.PersonBindingNavigator.BindingSource = Me.PersonBindingSource
Me.txtPersonID.DataBindings.Add("Text", Me.PersonBindingSource, "PersonID")
Me.txtPersonName.DataBindings.Add("Text", Me.PersonBindingSource, "PersonName")
'if it didn't find the key, position = 1
'you can also try any else proper event
CountryBindingSource.Position = CountryBindingSource.Find("CountryID", IIf(txtCountryID.Text = "", 0, txtCountryID.Text))
PersonBindingSource.Position = PersonBindingSource.Find("PersonID", IIf(txtPersonID.Text = "", 0, txtPersonID.Text))
End Sub
Private Sub EndEditOnAllBindingSources()
Dim BindingSourcesQuery = From bindingsources In Me.components.Components
Where (TypeOf bindingsources Is Windows.Forms.BindingSource)
Select bindingsources
For Each bindingSource As Windows.Forms.BindingSource In BindingSourcesQuery
bindingSource.EndEdit()
Next
End Sub
Private Sub BtnSaveCountries_Click(sender As Object, e As EventArgs) Handles BtnSaveCountries.Click
Me.EndEditOnAllBindingSources()
SaveCountries()
End Sub
Private Sub SaveCountries(Optional messages As Boolean = True)
If Me.ValidateChildren Then
Me.CountryBindingSource.Position = i
Me.CountryBindingSource.EndEdit()
Me.PersonBindingSource.EndEdit()
Me.daCountries.Update(Me.dsCountries.Tables("Countries"))
Else
System.Windows.Forms.MessageBox.Show(Me, "Validation _
errors occurred.",
"Save", System.Windows.Forms.MessageBoxButtons.OK,
System.Windows.Forms.MessageBoxIcon.Warning)
End If
End Sub
Private Sub BtnSavePersons_Click(sender As Object, e As EventArgs) Handles BtnSavePersons.Click
Me.EndEditOnAllBindingSources()
SavePersons()
End Sub
Private Sub SavePersons(Optional messages As Boolean = True)
If Me.Validate Then
Me.PersonBindingSource.EndEdit()
Me.daPersons.Update(Me.dsPersons.Tables("Persons"))
dsPersons.EnforceConstraints = False
Else
System.Windows.Forms.MessageBox.Show(Me, "Validation _
errors occurred.",
"Save", System.Windows.Forms.MessageBoxButtons.OK,
System.Windows.Forms.MessageBoxIcon.Warning)
End If
End Sub
End Class
If I build a solution, I try to add a new record, I noticed that the Country ID shows "0" in the field (instead of auto-incrementing to the next number in the sequence). When I add a new record and I press the save button, I receive the following error message at the point - Me.daCountries.Update(Me.dsCountries.Tables("Countries")): -
Code:
Exception Unhandled System.ArgumentNullException: 'Value cannot be null.
Parameter name: dataTable'
If I edit and update the data in the fields of the Persons' tab and press the Save button in the Persons' tab, no data is saved to the MySQL database. The Person ID field shows "0" (how do I get this field to show the next auto-increment number?) However, if I navigate to the next record, by pressing the "Next" icon in the Binding Navigator and then press the Save button, the updated data is now saved in the MySQL database. I do not know how to fix this problem. I assume that the problem has to do with setting the focus to stay on the current record and then save the changes. Any help will be greatly appreciated.
-
Jan 28th, 2019, 06:37 PM
#2
Re: Parent table issue re: value cannot be null. Parameter name: dataTable'
Try setting the MissingSchemaAction property of each data adapter to AddWithKey before calling Fill and you may find that that sorts the issue. If not, post back and I (and possibly others) will take a closer look.
-
Jan 29th, 2019, 11:00 AM
#3
Thread Starter
Member
Re: Parent table issue re: value cannot be null. Parameter name: dataTable'
Hi jmcilhinney,
Thank you for your quick response. I found a Microsoft document guide called "Adding Existing Constraints to a DataSet" (https://docs.microsoft.com/en-us/dot...s-to-a-dataset) which explained this setting up the MissingSchemaAction property of the adapter to AddWithKey.
I added the following vb.net code under the Form Show event and it fixed the problem:-
Code:
Dim dtCountries As DataTable = New DataTable()
daCountries.MissingSchemaAction = MissingSchemaAction.AddWithKey
daCountries.Fill(dtCountries)
Yes, the problem is now resolved. Thank you again for your help. Cheers!
-
Jan 29th, 2019, 11:01 AM
#4
Thread Starter
Member
Re: Parent table issue re: value cannot be null. Parameter name: dataTable'
Hi jmcilhinney,
Thank you for your quick response. I found a Microsoft document guide called "Adding Existing Constraints to a DataSet" (https://docs.microsoft.com/en-us/dot...s-to-a-dataset) which explained this setting up of the MissingSchemaAction property of the adapter to AddWithKey.
I added the following vb.net code under the Form Show event and it fixed the problem:-
Code:
Dim dtCountries As DataTable = New DataTable()
daCountries.MissingSchemaAction = MissingSchemaAction.AddWithKey
daCountries.Fill(dtCountries)
Yes, the problem is now resolved. Thank you again for your help. Cheers!
Last edited by wire_jp; Jan 29th, 2019 at 11:12 AM.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|