?
Solved

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

Posted on 2009-12-18
13
Medium Priority
?
514 Views
Last Modified: 2013-11-07
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

0
Comment
Question by:jsvb1977
  • 8
  • 5
13 Comments
 

Author Comment

by:jsvb1977
ID: 26082854
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
 
LVL 9

Expert Comment

by:CCongdon
ID: 26083325
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
 

Author Comment

by:jsvb1977
ID: 26083478
I did not have success with this solution. The server error still displays.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:jsvb1977
ID: 26083673
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
 
LVL 9

Expert Comment

by:CCongdon
ID: 26083882
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
 

Author Comment

by:jsvb1977
ID: 26083994
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
 
LVL 9

Accepted Solution

by:
CCongdon earned 2000 total points
ID: 26084127
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
 

Author Comment

by:jsvb1977
ID: 26084249
forgive my newbiness, what is and how do i create a Global.asax?
0
 

Author Comment

by:jsvb1977
ID: 26084311
I dont have the option to add a global.asax in my add new item list.
0
 
LVL 9

Expert Comment

by:CCongdon
ID: 26084372
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
 

Author Comment

by:jsvb1977
ID: 26084376
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
 
LVL 9

Expert Comment

by:CCongdon
ID: 26096365
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
 

Author Comment

by:jsvb1977
ID: 26096421
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

807 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