Reference Constraint?

jn148 · December 28th, 2008, 06:40 PM

You mention in your intro to databases chapter how to setup a diagram in the database to enforce not being able to delete (for example) a category record, if it's ID is used in a record in, say a Products table.

It works, but now when I write a GridView that deletes Category records, I get an ugly reference constraint asp.net error. Is there some way to code it in the ItemDeleting code or somewhere to cancel the delete and change a lable.text to "cannot delete category when it is still being referenced in products table" ?

Thoughts?
Thanks!

Imar · December 29th, 2008, 04:22 AM

Hi there,

Check out the TIO on page 494. It shows you how to disable the Delete link to stop users from deleting records that still have associated child records. Additionally, check out the TIO on page 498. This TIO shows you how to handle errors like the foreign key constraint exception you mentioned.

Cheers,

Imar

jn148 · December 29th, 2008, 08:56 AM

Thanks Imar!!

I have about 100 pages of reading to finish up to get there (I am still just starting the LINQ chapter...)

But I am dealing with a client who has this need now. When I simply add the code on page 500 to my GridView:

Code:

ProtectedSub dsCategories_Deleted(ByVal sender AsObject, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles dsCategories.Deleted
If e.Exception IsNotNothingAndAlsoTypeOf (e.Exception) Is sq SqlException Then
Dim myException As SqlException = CType(e.Exception, SqlException)
If myException.Number = 547 Then
Message.Text = "Sorry, you cannot delete this Category because " & _
"it has associated plans that you need to delete or " & _
"reassign first"
e.ExceptionHandled = True
EndIf
EndIf
EndSub

and try to load the page, I get the error: "Type 'SqlException' is not defined"

I am struggling to find time to finish my reading, while working on client projects ... any chance you could give me a quick hint of where I need to define the SQLException to help me limp along until I get my reading done?

Let me know - thanks again!

jn148 · December 29th, 2008, 09:03 AM

Forgot the:
Imports System.Data.SqlClient
at the top of my .vb codebehind - works like a champ now!

Even added a few other 'features' for my own use:

Code:

ProtectedSub Page_Load(ByVal sender AsObject, ByVal e As System.EventArgs) HandlesMe.Load
Message.Text = "<font color=black>Edit Existing Categories...</font>"
EndSub
 
 
ProtectedSub dsCategories_Deleted(ByVal sender AsObject, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles dsCategories.Deleted
If e.Exception IsNotNothingAndAlsoTypeOf (e.Exception) Is SqlException Then
Dim myException As SqlException = CType(e.Exception, SqlException)
If myException.Number = 547 Then
Message.Text = "Sorry, you cannot delete this Category because " & _
"it has associated plans that you need to delete or " & _
"reassign first"
e.ExceptionHandled = True
EndIf
Else
Message.Text = "Category deleted..."
EndIf
EndSub
 
 
ProtectedSub GridView1_RowUpdated(ByVal sender AsObject, ByVal e As System.Web.UI.WebControls.GridViewUpdatedEventArgs) Handles GridView1.RowUpdated
Message.Text = "Category updated..."
EndSub

Thanks again Imar!!

Imar · December 29th, 2008, 10:06 AM

You're welcome.....

Imar