asked on

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

jsvb1977

ASKER

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

CCongdon

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

jsvb1977

ASKER

I did not have success with this solution. The server error still displays.

jsvb1977

ASKER

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

CCongdon

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

jsvb1977

ASKER

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

ASKER CERTIFIED SOLUTION

CCongdon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jsvb1977

ASKER

forgive my newbiness, what is and how do i create a Global.asax?

jsvb1977

ASKER

I dont have the option to add a global.asax in my add new item list.

CCongdon

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

jsvb1977

ASKER

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

CCongdon

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.

jsvb1977

ASKER

Understood. I will research how to catch specific errors and display appropriate messages to my end user. thanks for all of your help.

Jason