How to catch SQL Server Error, Stop Insert Query, And Display Friendly Message

I have a .net web app which uses a datagrid and update query. I am attempting to catch the server error generated if an end user attempts to insert data into an existing primary key and redirect the end user to a friendly message on another asp page.

the error is:
Violation of PRIMARY KEY constraint 'PK_FSPI_AssociateIndex'. Cannot insert duplicate key in object 'dbo.FSPI_AssociateIndex'.
The statement has been terminated.

My code is listed below. Thanks in advance for your assistance.

Jason

Protected Sub AssociateIndexDataSource_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles AssociateIndexDataSource.Inserting
        Try

        Catch ex As Exception
            Me.Server.Transfer("TransactionDenied.aspx")
        End Try
    End Sub

Open in new window

jsvb1977Asked:
Who is Participating?
 
CCongdonCommented:
However an SQL error should kick back to generate a page error... Unless.... I wonder if it's causing an application error and not a page error....
You could try creating a Global.asax and inserting this sub in it:

Sub Application_Error(ByVal sender As Object, 
    ByVal e As EventArgs) 
        Server.Transfer("TransactionDenied.aspx", True)  
End Sub

Open in new window

0
 
jsvb1977Author Commented:
I also tried the following, but without success.
Dim DbCommand As Boolean

        If DbCommand Then
            'do nothing
        Else
            Me.Server.Transfer("TransactionDenied.aspx")
        End If

Open in new window

0
 
CCongdonCommented:
Most likely you want to handle an error like this at the page level, otherwise you have to break apart your datagrid control and re-write the insert behavior by hand... Add this to your page's code section/code-behind.
You can do a lot with this, including just flashing a warning up on the screen and not even having to go to a different page to handle the error.

Private Sub Page_Error(ByVal sender As Object, ByVal e As EventArgs)
    Server.Transfer("TransactionDenied.aspx", True)
End Sub

Open in new window

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
jsvb1977Author Commented:
I did not have success with this solution. The server error still displays.
0
 
jsvb1977Author Commented:
I tried this inline as well without success. I rebuilt the site and ran it from vs2008 as well.

"Page_Error" is this a command that .net recognizes or do i need to define it in some way?

Let me know your thoughts.

Jason
0
 
CCongdonCommented:
Try this. The application level error handler may still be catching the error even after you do the server transfer.
Page_Error is an event that is part of the System.Web.UI.Page class, so shouldn't need any special coding.

Private Sub Page_Error(ByVal sender As Object, ByVal e As EventArgs) 
    Server.ClearError()
    Server.Transfer("TransactionDenied.aspx", True) 
End Sub

Open in new window

0
 
jsvb1977Author Commented:
yeah -- i was reading about Server.ClearError() online when you posted the solution. unfortunately fo me, i am still getting the server error.

Is there a way to place this Page_Error inside the "AssociateIndexDataSource_Inserting" Sub? I dont know -- just a guess.

Everything i read online indicates that i should be able to use Page_Error in the same way you have posted it. I wonder why it is not working for me in my application?

Think it has to do with the fact that the error is an SQL error and not a web error?

Jason
0
 
jsvb1977Author Commented:
forgive my newbiness, what is and how do i create a Global.asax?
0
 
jsvb1977Author Commented:
I dont have the option to add a global.asax in my add new item list.
0
 
CCongdonCommented:
Global.asax is a global handler. It has events that span the application or even just sessions. It's also known as the Application file.
http://aspalliance.com/1114_Understanding_the_Globalasax_file
http://msdn.microsoft.com/en-us/library/2027ewzw.aspx 
 
 
0
 
jsvb1977Author Commented:
Bouyah! That was it!

I just created my own Global.asax file in notepad, added your solution, tested, and bouyah -- success.

Thank you for your help.

Jason
0
 
CCongdonCommented:
Be aware, this method will catch ALL errors if they aren't handled by the page already. You can tweak this some to give you some details about the error or to even do different things based on what error you got.
0
 
jsvb1977Author Commented:
Understood. I will research how to catch specific errors and display appropriate messages to my end user. thanks for all of your help.

Jason
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.