Solved

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

Posted on 2006-06-20
2
267 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

815 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