At some point, an error is going to occur in your web application. Maybe it's your code, maybe it's the SQL statement that's being executed, or maybe it's the network that's preventing proper communication between your web server and the database itself - but whatever the situation, you must be able to handle it in such a way that the user feels as if they are still in control. If the user can't recover, or failing that, if they can't exit the error situation gracefully, they're going to feel as though the application has failed them.
Using @@ERRORSQL Server and MSDE provide a system global variable called @@ERROR that you can use to check for errors from within your stored procedure code (there are many other useful global variables - check out MSDN for details). The variable contains 0 if the most recent SQL statement completed successfully; if it didn't, it contains an error number. To use @@ERROR effectively, you must capture its value after every SQL statement in your stored procedure (use a local variable of type INT to do this), because if any statement completes successfully after an earlier one has failed, @@ERROR will return 0, the value that signifies success. To show you how this works, we need to create a situation within the database in which errors are likely to occur. One such situation is when there's an attempt to insert a non-unique record into a table that uses a unique index to prevent such an action. Let's see @@ERROR in action now. Try It Out - @@ERROR
In the last chapter, we created a number of Visual Basic console applications for the purpose of inserting stored procedures into the Northwind database. You can use that same skeleton code to execute the sample SQL code in this chapter.
How It WorksThe stored procedure that we created in Step 2 will take a first name and last name, and insert a new employee in the Employees table using those values. Its return value will be the last error that occurred. If the action was successful, 0 is returned. In Step 3, we execute a SQL DELETE statement to ensure that an employee by the name of Joe Smith doesn't yet exist. Let's run the SQL script in Step 4 again, to see what happens when you try to insert an employee that already exists. Because we created the unique index in Step 1, we get the following error, along with its error number:
Server: Msg 2601, Level 14, State 3, Procedure InsertEmployee, Line 4 Cannot insert duplicate key row in object 'Employees' with unique index 'IX_Employees'. The statement has been terminated. Error number: 2601 While we're here, we can test something else that we stated above. Keep the Joe Smith record in the database, but change the stored procedure to include a SELECT command immediately after the INSERT command:
ALTER PROCEDURE InsertEmployee(@FirstName nvarchar(10), @LastName nvarchar(20)) AS INSERT INTO employees (firstname, lastname) VALUES (@FirstName, @LastName) SELECT TOP 1 * FROM employees RETURN @@ERROR Now re-run Step 4. Even though the record insertion still fails, as you can see from the result below, the returned message is Error number: 0, as our last statement (SELECT) was successful. (Note that the first employee returned from your Northwind database may be different from the one below.)
Server: Msg 2601, Level 14, State 3, Procedure InsertEmployee, Line 3 Cannot insert duplicate key row in object 'Employees' with unique index 'IX_Employees'. The statement has been terminated. EmployeeID LastName FirstName Title TitleOfCourtesy ----------------------------------------------------------------------------------- 1 Davolio Nancy Sales Representative Ms. (1 row(s) affected) Error number: 0 As mentioned before, you must check for an error condition before the next SQL statement is executed. Otherwise, whatever the old error value was is overwritten by the most recently executed statement's error value. In this case, since the SELECT statement executed successfully, the @@ERROR value is 0. Finally, let's change the InsertEmployee procedure one more time to return the error value correctly:
ALTER PROCEDURE InsertEmployee(@FirstName nvarchar(10), @LastName nvarchar(20)) AS DECLARE @errnum int INSERT INTO employees (firstname, lastname) VALUES (@FirstName, @LastName) SET @errnum = @@ERROR SELECT TOP 1 * FROM employees RETURN @errnum What we're now doing is capturing the @@ERROR global variable's value to a local variable of type int named @errnum, so that we can use it later. Execute Step 4 one last time, and you'll see the correct error value returned, as shown below.
Server: Msg 2601, Level 14, State 3, Procedure InsertEmployee, Line 3 Cannot insert duplicate key row in object 'Employees' with unique index 'IX Employees'. The statement has been terminated. EmployeeID LastName FirstName Title TitleOfCourtesy ------------------------------------------------------------------------------- 1 Davolio Nancy Sales Representative Ms. (1 row(s) affected) Error number: 2601
Making @@ERROR UsefulApplication troubleshooting can be a troublesome thing. Often, one of the most frustrating aspects of troubleshooting is trying to understand what went wrong. Usually, it's your users, not you, who experience errors when they occur - and users are infamous for telling you that there was an error, but not bothering to record specific details about it. One way to minimize the frustration is to log error information automatically. There are many approaches to this, one of which is to have the database itself record an error when it happens. You can do this in your stored procedures by checking the @@ERROR value after each statement within the procedure, and logging any error that occurs. Try It Out - Using @@ERROR in a stored procedure
Let's work through an example that does exactly what we just described. We'll execute a command, and then see if an error occurred. If it did, we'll write an error log entry into a new table that we'll create.
How It WorksLet's step through the stored procedure to understand how it works. First, we execute the INSERT statement.
INSERT INTO employees (firstname, lastname) VALUES (@FirstName, @LastName) If there has been an error,
IF @errnum <> 0 we insert a record into tblErrorLog, stating what the problem was:
BEGIN INSERT INTO tblErrorLog (ErrType, Msg) VALUES ('Duplicate record', 'Employee duplicate record couldn''t be created.') END The key to the ASPX page, meanwhile, lies in our Try...Catch block.
Try cmd.ExecuteNonQuery() Catch ex As Exception Response.Write("Error: " & ex.Message & "<br>") End Try Since the ExecuteNonQuery() method call is within the Try section, the Catch section will be executed if it fails. Logging an error from within the stored procedure does not remove the need to handle the error from within our code, and your users still need to be notified that an error occurred in a way that doesn't mean the application crashing. We do that in the Catch section, where we simply echo the exception message out to the user.
Response.Write("Error: " & ex.Message & "<br/>") This way, the user knows that an error has occurred, and when they call and tell you about it, you can look in the tblErrorLog table and see the specifics!
Raising Your Own Database Errors with RAISERRORWhat if you have multiple steps within a stored procedure, and you'd like some way of communicating to your ASP.NET application which step within the stored procedure caused an error? One way of doing this would be to use return values, but capturing those after every statement is a time-consuming business, and we really don't want to write more code than we absolutely have to. We want a way to know that a problem occurred, and at exactly what step, without using a return value. In this case, the T-SQL RAISERROR statement will fit our requirements. It allows you to raise a user-specific error in place of the actual database error that has occurred. By raising a user-specific error, you, can write your .NET code to handle the error better, or you can give more explanation to your user about the error. Normally, SQL error messages are pretty cryptic about exactly what happened, but RAISERROR gives you complete flexibility in describing the error to your user. The complete syntax of RAISERROR is as follows:
RAISERROR ( { msg_id I msg_str} {, severity, state} [, argument [,…n] ] ) [WITH option [,…n] ] As you can see from the syntax, RAISERROR has a number of options. For our purposes here, we're just going to look at the most common parameters, which are shown below:
Try It Out - Using RAISERROR
The first time, it should execute correctly. However, the second time you should get the following result.
Server: Msg 2601, Level 14, State 3, Procedure InsertEmployee, Line 4 Cannot insert duplicate key row in object 'Employees' with unique index 'IX_Employees'. The statement has been terminated. Msg 50000, Level 1, State 50000 Employee duplicate record couldn't be created. (1 row(s) affected) Note that SQL Server automatically raises the error 2601 again. Next, our custom error is raised. The message (1 row(s) affected) indicates that the record in the tblErrorLog was written.
How It WorksAs usual, the first thing we attempt to do in this stored procedure is to insert the new record into the Employees table:
INSERT INTO employees (firstname, lastname) VALUES (@FirstName, @LastName) After that, we check to see whether an error occurred. If it did, we raise an error stating that the duplicate record couldn't be created. We use a severity of 1 and a state of 1 as arbitrary values, since our code doesn't care about them.
IF @@ERROR <> 0 BEGIN RAISERROR(Employee duplicate record couldn't be created.', 1, 1) If an error occurred, we also attempt to insert a new record into tblErrorLog.
INSERT INTO tblErrorLog (ErrType, Msg) VALUES ('Duplicate record', 'Employee duplicate record couldn''t be created.') Finally, we check to see if yet another error occurred. If it did, we raise another error stating that the error log couldn't be updated.
IF @@ERROR <> 0 BEGIN RAISERROR ('Error log couldn" t be updated.', 1, 1 ) END Now let's create an artificial situation where an error will also occur while updating tblErrorLog. For that, we need to modify the InsertEmployee stored procedure yet again.
ALTER PROCEDURE InsertEmployee(@FirstName nvarchar(10), @LastName nvarchar(20)) AS INSERT INTO Employees (firstname, lastname) VALUES (@FirstName, @LastName) IF @@ERROR <> 0 BEGIN RAISERROR('Employee duplicate record couldn't be created.', 1, 1) INSERT INTO tblErrorLog (ErrType, Msg) VALUES (NULL, 'Employee duplicate record couldn''t be created.') IF @@ERROR <> 0 BEGIN RAISERROR('Employee duplicate record couldn '' t be created.', 1, 1) END END The only change we've made is that we're now attempting to insert NULL into the ErrType field, which does not allow nulls. Let's look at the results in Query Analyzer after we've run Step 2 again.
Server: Msg 2601, Level 14, State 3, Procedure InsertEmployee, Line 4 Cannot insert duplicate key row in object 'Employees' with unique index 'IX_Employees'. The statement has been terminated. Msg 50000, Level 1, State 50000 Employee duplicate record couldn't be created. Server: Msg 515, Level 16, State 2, Procedure InsertEmployee, Line 8 Cannot insert the value NULL into column 'ErrType', table 'Northwind.dbo.tblErrorLog'; column does not allow nulls. INSERT fails. The statement has been terminated. Msg 50000, Level 1, State 50000 Error log couldn't be updated. Notice that four errors are now raised: the original error 2601, the Employee duplicate record couldn't be created error, error 515, and the Error log couldn't be updated error.
Handling Errors in .NETWhen an error occurs, it's important to give your application's users as much information as possible, so that they can handle it more effectively. To do this, you need error messages that are as descriptive as possible within your .NET code. This is where RAISERROR really shines. In the examples above, we took a relatively cryptic error and raised another one that was much more descriptive and specific to our application. When an error occurs within the database during execution via ADO.NET, a .NET exception of type System.Data.SqlClient.SqlException occurs. Before we get into an example, let's look at some of the members that System.Data.SqlClient.SqlException provides that override or augment those that it inherits from the Exception class.
Try It Out - ASP.NET with RAISERROR
How It WorksTaking all of the familiar code as read, we catch the SqlException through this line:
Catch ex As System.Data.SqlClient.SqlException Now we have the SqlException within the variable ex, and the next lines can display the state, the severity (the member that returns the severity is called class), and the message of the SqlException.
Response.Write("- SQLException -<br/>") Response.Write("Message: ") Response.Write(replace(ex.Message, controlchars.lf, "<br/>") & "<br>") Response.Write("Class: " & ex.class & "<br/>") Response.Write("State: " & ex.state & "<br/>") While using the SqlException object is enough to know that an error occurred, it's not enough to know the exact details of each error. There are times when knowing the severity and state of each error, and not just the first one, is important. For that, you have to enumerate through the Errors collection:
Dim sErr As SqlError For Each sErr In ex.Errors Response.Write("#: " & sErr.Number & " - Class: ") Response.Write(sErr.Class & " - State: " & sErr.State) Response.Write(" - Message: ") Response.Write(replace(sErr.Message, controlchars.lf, "<br/>")) Response.Write("<br/><br/>") Next For each error, the error number, severity, state, and message are returned, and you can use this information to display friendly error information to your users. Let's clean up the example above so that it displays only the custom error messages that we generate via RAISERROR in the InsertEmployee stored procedure. The changes to the code are highlighted.
<%@ Import namespace="system.data" %> <%@ Import namespace="system.data.sqlclient" %> <script language="vb" runat="server"> Private Sub Page_Load(ByVal Source As Object, ByVal E As EventArgs) Dim sql As String = "InsertEmployee" Dim strConnection As String = ConfigurationSettings.AppSettings("NWind") Dim conn As New SqlConnection(strConnection) Response.Write("Attempting to add an employee to the database...<br>") conn.Open() Dim cmd As New SqlCommand(sql, conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@FirstName", "Jane") cmd.Parameters.Add("@LastName", "Zerdeni") Try cmd.ExecuteNonQuery() Catch ex As System.Data.SqlClient.SqlException Dim sErr As SqlError For Each sErr In ex.Errors If sErr.Number = 50000 Then Response.Write(sErr.Message & "<br/>") End If Next End Try cmd.Connection.Close() End Sub </script> The results are shown below.
This time, when errors occur, we enumerate through each one and display only those with a message ID of 50000. As mentioned previously, this is the message ID that's generated when you specify a custom message string using RAISERROR. This is our way of knowing that these are the friendly error messages.
For Each sErr In ex.Errors If sErr.Number = 50000 Then Response.Write(sErr.Message & "<br/>") End If Next In this example, we're simply showing the user what went wrong. In your production code, you should try to do more. For instance, you'd probably want to allow them to try the operation again, or make changes to their data. In fact, in a production application, you wouldn't want execution to get to the point where it tries to insert a duplicate record. Instead, you should verify that no duplicate exists prior to attempting the insert. |