• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Can I test a foreign key constraint before deleting a datagrid row?


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?

1 Solution
There are various things you could do.

First, it is not quite true to say that "as the row has already been deleted from the datagrid, there is no way to un-do the change".  Although the row may have been taken out of the dataGRID it will not be irrevocably removed from the dataTABLE which is the datasource of the datagrid until .AcceptChanges is called on (or with reference to) that datatable.  So you can reverse the process by calling .RejectChanges: the "deleted" row will then reappear in the datagrid.  If you are trying to update the database after a single deletion, this is probably the easiest approach.

Second, you could bring all the linked tables over into a dataset and, within that dataset, reproduce the relationships and constraints which exist in the database.  Then the fact that a deletion would affect the main table will be detected by dataset before any attempt to alter the database is made.  But that (a) involves bringing over data that you may not need for immediate purposes and (b) some fairly complicated coding.  It would be my last choice.

Third, you could query the database's main table before you decide whether to process a deletion.  Using command text something like

   "SELECT COUNT(id) FROM MainTable WHERE LookUpRef = " & ThisLookUpRef

with Command.ExecuteScalar will return 0 if it is OK to delete.  Any other number will indicate that the reference is in use in the main table.

There may be other approaches, but those are possibilities that immediately occur to me.

bobsegrestAuthor Commented:
Thanks Roger!  That is what I was looking for.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now