DataGridView Master-Detail (Parent-Child)

[Mark Tompkins]

Gents -

I have a DataSet with a Parent / Child relation.

On Form1 I have a DGV that is bound to the details (child) portion of the
relation (tblInventoryTransBindingSource).
I have a BindingNavigator bound to the Parent binding source (tblPurchaseOrdersBindingSource).

On the form I also have a DateTimePicker for date control bound to tblPurchaseOrdersBindingSource
- OrderDate.

In order to create a default value of today on the DateTimePicker control,
I have set the BindingNavigator's "AddNewItem" to NONE to handle this programmatically.

I have the following code:

<code>
Private Sub POAddNewItem_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) _
Handles BindingNavigatorAddNewItem.Click

Me.TblPurchaseOrdersBindingSource.AddNew()
Me.OrderDateDateTimePicker.Value = DateTime.Today

End Sub
</code>

That works fine.

The problem I am running into is on the entering of data to a new child record.
When I click AddNew of the binding nav new record data on the parent is
started and the first line of entry on the DGV appears. As I enter data on
the child (DGV) on row one it enters without issue but as I move to row 2
(next new row) an exception is thrown:

System.Data.InvalidConstraintException: ForeignKeyConstraint FK_tblInvTrans_tblPurchaseOrders
requires the child key values (1) to exist in the parent table.

So it seems I need to get that primary key generated into the Master record
(tblPurchaseOders) prior to entering data into the DGV.

I have tried some of the following code to no avail -

<code>
' Trying to save the Parent PO data prior to editing the DGV
' Those listed below did not make this happen

'Me.TblInvTransTableAdapter.Update(Me.PODataSet.tblInvTrans)
'Me.TblPurchaseOrdersTableAdapter.GetData()
'Me.TblPurchaseOrdersTableAdapter.Fill(Me.PODataSet.tblPurchaseOrders)
'Me.TblInvTransTableAdapter.GetData()
'Me.TblPurchaseOrdersTableAdapter.Update(Me.PODataSet.tblPurchaseOrders)
'Me.PODataSet.AcceptChanges()
'Me.TblPurchaseOrdersTableAdapter.Fill(Me.PODataSet.tblPurchaseOrders)
</code>

Which are my efforts of pushing this autoincrement key back up to the parent
and refreshing the DGV.


I have tried setting the fields: AutoIncrementSeed =-1, AutoIncrementStep=-1
to try and let the DataSet handle it.

Again no luck.

Help in the appropriate way to handle this would be appreciated.

Many thanks,
Mark

 

[Mark Tompkins]

** Update **

Trying to move along with this . . .

I have referenced the following HowTo:

http://msdn2.microsoft.com/en-us/library/4esb49b4(d=ide).aspx

Walkthrough: Saving Data to a Database (Multiple Tables)

As such my code now looks as follows:



<code>

Private Sub TblPOSaveItem_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) _

Handles TblPurchaseOrdersBindingNavigatorSaveItem.Click

'Move the focus out of the DGV before saving

Me.TabPage1.Focus()

If Me.PurchaseOrderIDTextBox.Text <> "" Then

If Me.EmployeeIDComboBox.Text = "" Then

errNulls.SetError(Me.EmployeeIDComboBox, "Please Select Employee")

errNulls.SetError(Me.SupplierIDComboBox, Nothing)

ElseIf Me.SupplierIDComboBox.Text = "" Then

errNulls.SetError(Me.SupplierIDComboBox, "Please Select Supplier")

errNulls.SetError(Me.EmployeeIDComboBox, Nothing)

Else

errNulls.SetError(Me.EmployeeIDComboBox, Nothing)

errNulls.SetError(Me.SupplierIDComboBox, Nothing)

Call PoTabSave()

End If

End If

End Sub

Public Sub PoTabSave()

Me.Validate()

Me.TblInvTransBindingSource.EndEdit()

Me.TblPurchaseOrdersBindingSource.EndEdit()

Dim deletedInvTrans As PODataSet.tblInvTransDataTable = CType( _

PODataSet.tblInvTrans.GetChanges(Data.DataRowState.Deleted), PODataSet.tblInvTransDataTable)

Dim newInvTrans As PODataSet.tblInvTransDataTable = CType( _

PODataSet.tblInvTrans.GetChanges(Data.DataRowState.Added), PODataSet.tblInvTransDataTable)

Dim modifiedInvTrans As PODataSet.tblInvTransDataTable = CType( _

PODataSet.tblInvTrans.GetChanges(Data.DataRowState.Modified), PODataSet.tblInvTransDataTable)

Try

' Remove all deleted orders from the tblInvTrans table

If deletedInvTrans IsNot Nothing Then

TblInvTransTableAdapter.Update(deletedInvTrans)

End If

'Update the PO table

TblPurchaseOrdersTableAdapter.Update(PODataSet.tblPurchaseOrders)

'Add new orders to the InvTrans table

If newInvTrans IsNot Nothing Then

TblInvTransTableAdapter.Update(newInvTrans)

End If

'Update all modified InvTrans

If modifiedInvTrans IsNot Nothing Then

TblInvTransTableAdapter.Update(modifiedInvTrans)

End If

PODataSet.AcceptChanges()

Catch ex As Exception

'MsgBox("Update Failed")

Finally

If deletedInvTrans IsNot Nothing Then

deletedInvTrans.Dispose()

End If

If newInvTrans IsNot Nothing Then

newInvTrans.Dispose()

End If

If modifiedInvTrans IsNot Nothing Then

modifiedInvTrans.Dispose()

End If

End Try

End Sub

</code>



This leaves me with the following problems. Interestingly, when I delete
a parent record with the bindingnavigaor and click save - the record is not
deleted and no exception is thrown. This deletion should occur on the code
line

TblPurchaseOrdersTableAdapter.Update(PODataSet.tblPurchaseOrders)

When there are no records in the table and I generate the first record (parent).
There is a failure to save it - it seems it needs to go to the SQL database
and pull the first autonumber.

OK - after all of this I am at a crossroads -

At the SQL server level FK relations -

If I have the UPDATE / DELETE set to Cascade -DELETES work but INSERT throws
an FK exception.

If I have the UPDATE/DELETE set to nothing and have just a relationship -
the DGV throws an FK exception when moving to a 2nd row.

If I have just relationships at the SQL server level and try and configure
the DataSet same result.

 

[Mark Tompkins]

From various research I have made the following change -

I have bound the child DGV to the Parent FK.

Now updates, inserts, deletes seem to work except I am unable to delete the
first parent record in the SQL database (parent table).

Any thoughts?