Catching a SQLException error

digriz60 · November 18th, 2003, 07:57 PM

I'm building out a SQL server based application using ASP.NET as an intranet application. Constraints we have on the tables mean if a user doesn't use the proper format or uses an invalid data type, the System.Data.SqlClient.SqlException class will return an error back to the requesting page. How can I catch the error and present it in an aesthetically pleasing way to the user instead of

System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'LIFTED_DATE', table 'InventoryControlSystem.dbo.KM_BOL_RANGE'; column does not allow nulls. INSERT fails.
The statement has been terminated.
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at DreamweaverCtrls.DataSet.DoInit()

Which is obscured behind a graphic and has to be cut and pasted to Wordpad in order to be read. Can I filter this error and return a messagebox of some sort?

TIA

Steve

planoie · November 19th, 2003, 10:57 AM

Put your database calls inside of a Try..Catch block. You can then "Catch" any errors that you would expect to see:

Try
    'Make SQL calls here
Catch e As System.Data.SqlClient.SqlException
    'Handle System.Data.SqlClient.SqlException here
    'you can access the exception message here to give the user feedback
Catch e As SystemException
    'You can have many Catches. They should be ordered from most to least specific.
Finally
    'Do stuff here that always needs to be done
    'like closing the database connection
End Try

Peter
------------------------------------------------------
Work smarter, not harder.

digriz60 · November 19th, 2003, 07:26 PM

Thanks a lot. I got that to successfully work!

designdawg · January 14th, 2008, 01:21 PM

Using this code block, how would one direct a user to an error message that is embedded in a .asp page. Rather than show a person the ugly exception message, I's like to show something more user friendly. Any suggestions?

dparsons · January 14th, 2008, 01:39 PM

You can do a couple of things:

In the Global.asax you can use the Application_OnError event to redirect a user to a custom error page using:
HttpContext.Current.Server.Response.Redirect("some page.aspx")

Or you can do something like
bool isError = false;
try
{
}
catch(Exception ex)
{
isError = true;
}
finally
{
//close connections
if(isError) { Response.Redirect("somepage.aspx"); }
return;
}

hth

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor :.
Wrox Books 24 x 7
================================================== =========

rstelma · January 14th, 2008, 03:14 PM

It would really be much easier just to validate the data before the error using the same logic that others have presented in a Try/Catch block:

Try
            If Not IsNumeric(txtCompPS.Text) Then
                Throw New Exception("Only numbers are allowed for PS quantity.")
            End If
Catch
            Session("Alert") = ex.Message
Finally
            If Len(Session("Alert")) = 0 Then
                Session("Confirm") = "[Any confirmation message you want]."
            End If
End Try

Then on the front page I have:

<%@ Register TagPrefix="include" TagName="Alert" src="../include/alert.ascx" %>
<%@ Register TagPrefix="include" TagName="Confirm" src="../include/confirm.ascx" %>

before the <head></head> tags.

And in the body:

<include:Alert ID="Alert" Runat="server" />
<include:Confirm ID="Confirm" Runat="server" />

Alert page:

<%@ Control Language="vb" AutoEventWireup="false" Codebehind="alert.ascx.vb" Inherits="extranet.include_alert" TargetSchema="http://schemas.microsoft.com/intellisense/ie5" %>
<table cellpadding="0" cellspacing="5" class="alert" id="tblAlert" runat="server">
    <tr>
        <td class="alert">
            <img border="0" src="../images/icons/alert.gif" width="16" height="16" /><br />
        </td>
        <td width="100%" class="alert">
            <asp:Literal ID="litAlert" Runat="server" /><br />
        </td>
    </tr>
    <tr id="tdrOptions" runat="server">
        <td class="alert_options">
            <br />
        </td>
        <td class="alert_options">
            <asp:Literal ID="litOptions" Runat="server" /><br />
        </td>
    </tr>
</table>

Alert Code behind:

Inherits System.Web.UI.UserControl

    Private _Alert As String
    Private _Options As String

    Protected tblAlert As HtmlTable
    Protected litAlert As Literal
    Protected tdrOptions As HtmlTableRow
    Protected litOptions As Literal

Private Sub Page_Prerender(ByVal Sender As Object, ByVal e As EventArgs) Handles MyBase.PreRender
        _Alert = Session("Alert")
        _Options = Session("Options")
        If Len(_Alert) = 0 Then
            tblAlert.Visible = False
        Else
            tblAlert.Visible = True
            litAlert.Text = _Alert
            If Len(_Options) = 0 Then
                tdrOptions.Visible = False
            Else
                tdrOptions.Visible = True
                litOptions.Text = _Options
            End If
            Session("Alert") = ""
            Session("Options") = ""
        End If
    End Sub