Solved

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

Posted on 2006-06-20
2
264 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
Comment Utility
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
Comment Utility
Thanks Roger!  That is what I was looking for.

Bob
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now