dispalying error returned by stored procedure in .aspx form

Question

hey

i have a stored procedure which i use to insert values in a table...if i implement error handling in it using @@ERROR and if i it returns error in a variable @myError can i display this error in my .aspx form??i m using sql server 2005

thanx....

Answer 1

Yes you can, but normally you don't want to display system error messages to users - it would be better to display a user friendly message instead.

That being said however, just create a label and set the label's Text property with the value of @myError.

Answer 2

I would recommend using TRY/CATCH in T-SQL instead of @@ERROR. The later is notoriously problematic because is reset after each statement. For more details see

• Error Handling in SQL Server – a Background
• Implementing Error Handling with Stored Procedures

I much prefer TRY/CATCH, using a procedure template like the one described here: Exception handling and nested transactions. This way an error is raised into your ASP.Net as a SqlException which you can catch, like any other CLR exception.

Answer 3

You can use the InfoMessage event of the SqlConnection to output SQL warnings and information messages to the client. By default the event is only fired for information messages with a severity of less than 10. By setting the FireInfoMessageEventOnUserErrors property of the SqlConnection to true you can trigger the InfoMessage event for errors of severity 11 to 16 instead of throwing an exception and halting execution. By adding an event handler for the InfoMessage event we can output the message to the client.

using (SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();
    conn.FireInfoMessageEventOnUserErrors = true;
    conn.InfoMessage += delegate(object sender, SqlInfoMessageEventArgs e)
    {
        txtMessage.Text += e.Message;
    };
    ...
}

See: HOW TO: Return Errors and Warnings from a SQL Server Stored Procedure in ADO.NET