Q: Relationships and exceptions

[Geoff]

Hi

Can anybody explain to me what I'm doing wrong or misunderstood in the
following:

I have two tables, for example, customers and orders. I have loaded these
into a dataset. I have also created a relation between these two tables
using the Relation keyword e.g.

ds.Relationships.Add("Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"), True)

I populate both tables with data.

Everything works ok. However, when I choose a row from the Customers tables
and try to delete it as follows:

Try
row.Delete() ' where row has child rows in the Orders table
Catch ex Exception
MessageBox(ex.Message)
End Try

The thing is that no exception is thrown!!! I was expecting one to be
thrown. Have I missed something obvious?

Thanks for any help in advance

Geoff

 

[Bart Mermuys]

Hi,

Hi

Can anybody explain to me what I'm doing wrong or misunderstood in the
following:

I have two tables, for example, customers and orders. I have loaded these
into a dataset. I have also created a relation between these two tables
using the Relation keyword e.g.

ds.Relationships.Add("Customers_Orders", _
ds.Tables("Customers").Columns("CustomerID"), _
ds.Tables("Orders").Columns("CustomerID"), True)

I populate both tables with data.

Everything works ok. However, when I choose a row from the Customers
tables and try to delete it as follows:

Try
row.Delete() ' where row has child rows in the Orders table
Catch ex Exception
MessageBox(ex.Message)
End Try

The thing is that no exception is thrown!!! I was expecting one to be
thrown. Have I missed something obvious?

When you add a DataRelation, then there's also a ForeignKeyConstraint added
to the child table. The ForeignKeyConstraint has a property 'DeleteRule'.
By default that property is set to Rule.Cascade. So when you delete a
parent row, child rows are deleted too and therefore there is no reason for
an Exception.

You can change this behaviour, eg. :

ds.Relations("Customers_Orders").ChildKeyConstraint.DeleteRule = Rule.None

If you delete a parent row now and the parent has child rows, it will throw
an exception, because the child rows would become orphaned.


HTH,
Greetings

 

[Geoff]

Thanks Bart!

Hi,



When you add a DataRelation, then there's also a ForeignKeyConstraint
added to the child table. The ForeignKeyConstraint has a property
'DeleteRule'. By default that property is set to Rule.Cascade. So when
you delete a parent row, child rows are deleted too and therefore there is
no reason for an Exception.

You can change this behaviour, eg. :

ds.Relations("Customers_Orders").ChildKeyConstraint.DeleteRule = Rule.None

If you delete a parent row now and the parent has child rows, it will
throw an exception, because the child rows would become orphaned.


HTH,
Greetings