Solved

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

Posted on 2006-06-20
2
280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

631 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