Re-Fill a dataset with FK constraints

[FreeriderUK]

I have a parent table (tbCustomers) with child table (tbJobs) in my Access DB

Everything was working well, until I decided to add a Foreign Key Constraint.

This is what basically happens when I add a new customer:

Me.Validate()
Me.TbCustomersBindingSource.EndEdit()

Dim CustomersTable As DataTable = Me.MyDataSet.tbCustomers.GetChanges()
        'Updates
        Try
            Dim CustomerUpdates() As DataRow = Me.MyDataSet.tbCustomers.Select("", "", DataViewRowState.Added Or DataViewRowState.ModifiedCurrent)
            Me.TbCustomersTableAdapter.Update(CustomerUpdates)

        Catch ex As Exception
            MsgBox("Couldn't update customers")
        End Try

 Me.TbCustomersTableAdapter.FillBySelection      <---- The error occurs here

When I add a new customer and try to re-fill the dataset, I get the following error:

Cannot clear table tbCustomers because ForeignKeyConstraint tbCustomers_tbJobs enforces constraints and there are child rows in tbJobs.

Which seems fair enough. But what am I doing wrong?

 

[FreeriderUK]

It seems, all that's required is to clear the child table before filling the parent table:

Me.MyDataSet.tbJobs.Clear()

 

[cjard]

Of course. Given that a Fill performs a Clear first, when tableAdapter.ClearBeforeFill is true, you cannot remove parent rows while child rows exist.

I find your update logic very curious. Howcome you dont just:

Me.TbCustomersTableAdapter.Update(Me.MyDataSet.tbCustomers)