Skip Navigation LinksHome > Categories > Code from a Category

Handling Custom Errors in a Client Application

User Name: codelecturer
Name: Mike Chauhan
Contact Me:
Home Page:
6 years of total IT experience including programming, application development, System Maintenance . 3 years of Experienced and expertise in .Net Framework VB, Visual Basic.Net, ASP.Net 2.0, 3.5 and AD... [More]
Viewed Times: 916
Add Date: 12/19/2013
In this article, we will discusses how to use access custom error messages generated by SQL Server with the Errors Collection of ADO.
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

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.

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.

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

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.

Happy Programming!!!

Post a Comment

Name: (Optional)
Email: (Optional, you can get an email if somebody replys your comments)*
Email me if somebody respons my comment below:
Enter Text
as Below:
(case insensitive, if hard to read, click the "get a new one" button)
* Your email address will not be shared with any third parties for any reason.
** Maximum 1000 charactors.