Handling Custom Errors in a Client Application

By Garth Wells on 5 March 2001 | Tags: Stored Procedures

In this article, Garth discusses how to use access custom error messages generated by SQL Server with the Errors Collection of ADO. This is a follow up to the earlier Handling Errors in Stored Procedures article.

In my previous article Handling Errors in Stored Procedures I showed some basic error-handling techniques that can be used in stored procedures. You will want to read and understand the material presented in the article before continuing. Toward the end of the article I used the system stored procedure sp_addmessage to add a custom error message to the sysmessages system table in the master database. Obviously the techniques discussed in the article all had to do with server-side actions.

In this article, I want to take a look at handling custom errors in a client application. The key thing to know when handling custom error messages is that the error's ID (error is the actual column name in sysmessages) is greater than 50,000. This is a requirement of the sp_addmessages procedure.

Client Error Handling Functionality

There are two approaches to handling SQL Server errors in a client application. The first approach involves using the error handling functionality of the client and the second sends an explicit return value to the client so it can respond accordingly. Only the client approach is covered here, because it is the more efficient way to handle custom error messages.

Three of the most popular development languages/technologies are Visual Basic (VB), C++, and Active Server Pages (ASP). All three support ADO, so the example presented here will demonstrate how to use the ADO Errors collection to access the errors returned by SQL Server. We'll use the following procedure to demonstrate how a custom error message can be sent to a client application

CREATE PROCEDURE ps_NonFatal_INSERT
@Column2 int = NULL
AS
SET NOCOUNT ON

DECLARE @ErrorMsgID int

INSERT NonFatal VALUES (@Column2)
SET @ErrorMsgID =@@ERROR
IF @ErrorMsgID <>0
 BEGIN
  RAISERROR (50001,17,1)
 END

You will notice that one of the first actions the procedure performs is: SET NOCOUNT ON. This statement prohibits intermediary results from being sent to the client application. Intermediary results (e.g., rows affected by a statement) cause OLE-DB to terminate processing before the custom error message is sent to the application. Intermediary results do not affect ODBC, so SET NOCOUNT ON is not needed if you are going to connect to the database with ODBC.

Some books and articles I have read tell you to always use SET NOCOUNT ON as the first statement in a stored procedure. To be honest, though, I have worked with stored procedures for a number of years without using it on all my procedures. I wish the books would have explained why it should be used, because it took me more than a few minutes to figure out why I could not see the custom error message in the client application. It wasn't until I did some research and determined the exact impact it had on an OLE-DB connection before I could get this example to work properly.

The following example, which is based on ASP/ADO, makes an invalid call to the procedure.

<!--#include file="adovbs.inc"-->

<%
On Error Resume Next
Set objConn =server.CreateObject("ADODB.Connection")

REM DSN-Less OLE-DB
ObjConn.Open  "Provider=SQLOLEDB;Server=Ace;Database=tempdb;Uid=garth;Pwd=password"

Set cmd1 =server.CreateObject("ADODB.Command")
cmd1.ActiveConnection =objConn
cmd1.CommandType =adCmdStoredProc
cmd1.CommandText ="ps_NonFatal_INSERT"
cmd1.Parameters.Append
cmd1.CreateParameter("Column2",adInteger,adParamInput,,NULL)
cmd1.Execute

i =0
For Each error_item in objConn.Errors
 response.write objConn.Errors(i).Description &"<br>"
 response.write objConn.Errors(i).NativeError &"<br>"
 i =i +1
Next
%>

ADO use the Errors collection of the Connection object to capture the errors returned by SQL Server. The Description property of the Errors collection contains the text of the message. The NativeError property contains the associated error number. When the page is executed the following output is generated.

[Microsoft ][ODBC SQL Server Driver ][SQL Server ]Cannot insert the value NULL 
into column 'Column2',table 'tempdb.dbo.NonFatal';column does not allow nulls.
INSERT fails. 
515
[Microsoft ][ODBC SQL Server Driver ][SQL Server ]The statement has been 
terminated. 
3621
[Microsoft ][ODBC SQL Server Driver ][SQL Server ]An error occured updating the 
NonFatal table 
50001

The system generated messages are returned along with the custom message returned by RAISERROR. You can use programming logic to discern system from custom error messages because the latter will have a NativeError value greater than 50,000. In addition, string manipulation code can be used to strip away the part of the message that might confuse the end user. The following shows how this done (partial code).

<%
. . .
i =0
For Each error_item in objConn.Errors
 If objConn.Errors(i).NativeError >50000 Then
  response.write Mid(objConn.Errors(i).Description,InStrRev(objConn.Errors(i).Description,"]")+1)
 End If
 i =i +1
Next
%>

The output generated by this code is shown here.

An error occurred updating the NonFatal table

That's all there is to implementing custom error messages with ADO. As you have read, the bulk of the material you need to understand relates to SQL Server, not ADO. Once you understand how to add a custom message with sp_addmessage and access it with RAISERROR, it's just a matter of finding it in the ADO Errors collection. And if you want to be nice to the end-users you should use the string manipulation techniques listed about to remove the confusing parts of the message.