Solved

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

Posted on 2006-06-20
2
270 Views
Last Modified: 2010-04-23
Greetings,

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?

Bob
0
Comment
Question by:bobsegrest
2 Comments
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 16950503
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.

Roger
0
 
LVL 13

Author Comment

by:bobsegrest
ID: 16950756
Thanks Roger!  That is what I was looking for.

Bob
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question