I am developing a Windows application using VB.Net and a JET (Access) database.
The main table in my database is related to several other tables using foreign keys.
I am displaying each of these lookup tables in a datagrid so that appropriate users can add, delete or edit entries. Clearly it is important to ensure that an entry is not deleted if it is being referenced from he main table. This is why the foreign key constraint exists. However I do want the user to be able to delete an entry if it is no longer being referenced by the main table.
Allowing a user to delete a row from the datagrid where the lookup table is displayed is easy. The problem is that when the dataset is subsequently updated, the JET database engine rejects the transaction because of the foreign key relationship and as the row has already been deleted from the datagrid, there is no way to un-do the change.
Is there a way for me to test for a foreign key constraint before a datagrid row is deleted?