CODEWITHSUNDEEP
asp.netsql-servert-sqlstored-procedures
anay
anay

Reputation: 145

dispalying error returned by stored procedure in .aspx form

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....

Upvotes: 0

Views: 1913

Answers (3)

Phaedrus
Phaedrus

Reputation: 8421

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

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294287

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

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.

Upvotes: 0

Andrew Hare
Andrew Hare

Reputation: 351516

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.

Upvotes: 0

Related Questions