[RESOLVED] Parent table issue re: value cannot be null. Parameter name: dataTable'

[wire_jp]

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

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

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.

[jmcilhinney]

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.

[wire_jp]

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

 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!

[wire_jp]

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

 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!