I have a master/details datagridview, andcan only set up either a datarelation or a foreign key constraintbetween them, but I need both. I set up a datarelation as follows:

Dim parentColumn As DataColumn = _

Me.MetricsDataSet.Tables("SoftwareItems").Columns("Item")

Dim childColumn As DataColumn = _

Me.MetricsDataSet.Tables("SoftwareItemDetail").Columns("Item")

Dim relation As New DataRelation("ItemDetails", parentColumn, childColumn)

Me.MetricsDataSet.Relations.Add(relation)

SoftwareItemDetailBindingSource.DataSource = SoftwareItemsBindingSource

SoftwareItemDetailBindingSource.DataMember = "ItemDetails"

This works fine for the relationship between them, but it throws an error: ("Update requires a valid DeleteCommand when passed DataRow collection with deleted rows.") when attempting to delete a record.

Contrarily,when Ionly set up a foreign key constraint:

Dim parentColumn As DataColumn = _

Me.MetricsDataSet.Tables("SoftwareItems").Columns("Item")

Dim childColumn As DataColumn = _

Me.MetricsDataSet.Tables("SoftwareItemDetail").Columns("Item")

Dim fkeyConstraint As New ForeignKeyConstraint( _

"ItemFKConstraint", parentColumn, childColumn)

fkeyConstraint.DeleteRule = Rule.SetNull

fkeyConstraint.UpdateRule = Rule.Cascade

fkeyConstraint.AcceptRejectRule = AcceptRejectRule.Cascade

Me.MetricsDataSet.Tables("SoftwareItemDetail").Constraints.Add(fkeyConstraint)

... I lose the benefit of the datarelation between the views.

When I try to create both, the last line of code above throws the error: ("Constraint matches constraint named ItemDetails already in collection.") Even though the datarelation and fkey constraint are in separate collections, it complains that the identical constraint already exists.

What am I missing? Thanks for your assistance.

I'm not sure that your problem is entirely an issue of relations and foreign keys.

One issue I see is the error "Update requires a valid DeleteCommand when passed DataRow collection with deleted rows."

Was this code generated by the designer or did you write it by hand?

This sounds like the error you get if your tableadapter does not have a valid DeleteCommand property (you can also geta similarerror for a missing UpdateCommand). Open your MyTableAdapter.designer.cs code and look for a "Delete(...)" method. If it isn't there, I'm betting the datatable involved doesn't have a primary key, or at least a unique constraint in which case the designer won't create the DeleteCommand, UpdateCommand, and the associated methods for you, since you can't update or delete without a unique identifier.

I'm guessing it's the child table that is the problem.

hth,

dob

I'm not sure that your problem is entirely an issue of relations and foreign keys.

One issue I see is the error "Update requires a valid DeleteCommand when passed DataRow collection with deleted rows."

Was this code generated by the designer or did you write it by hand?

This sounds like the error you get if your tableadapter does not have a valid DeleteCommand property (you can also geta similarerror for a missing UpdateCommand). Open your MyTableAdapter.designer.cs code and look for a "Delete(...)" method. If it isn't there, I'm betting the datatable involved doesn't have a primary key, or at least a unique constraint in which case the designer won't create the DeleteCommand, UpdateCommand, and the associated methods for you, since you can't update or delete without a unique identifier.

I'm guessing it's the child table that is the problem.

hth,

dob

You're right. I had to rebuild the db tables with the proper keys/constraints, and now it should work alot better. Thanks!