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.
Laptop Battery 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
Apart from redirecting the copy of requested webpage to client’ laptop, web server also detects errors. These errors are commonly known as ‘Server Errors’ and are displayed on the screen with error code and error message.
Thinkpad 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.
mail is then sent to the destination SMTP server which then delivers it to the recipients incoming mail server (POP3). 4) The recipient either views the mail directly on the server with a webmail application, or ideally, mail client (Outlook). With Webmail, you are viewing the mail directly on your POP mail server, mail Client like Outlook, you are downloading mail to your computer. A webmail application is great when you are traveling and do not have access to your own computer.
Microsoft 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
Web server error codes can be of two types – Client Error Codes and Server Error Codes. Three digit error codes, which start with ’4 ‘and ’5’, are considered as Client Error Code and Server Error Code, respectively. Client error code is displayed if the syntax of the request is not valid. In this case, the server will fail to process the request and display Client Error Code. However, you may get error code by sending a valid request with right syntax. If it is a problem of that particular server then you will get the Server Error Code that starts with ‘5’.
Laptop Computers 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.
Three digit server error codes, which start with the digit ‘4’, are known as Client Error Codes.
Laptop Computer 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.
Computer memory is the quickest, cheapest, and easiest way to improve the performance of your system. Find RAM memory upgrades for desktops, laptops, servers, and printers all backed by a lifetime warranty and guaranteed compatible with your computer. Shipping is an everyday low price of $1.99! Computer Memory Outlet sells memory compatible with all leading computer manufacturers like Dell, Apple, Compaq, HP, Sony, IBM, Lenovo, and many more.”
Desktop Computer 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 %>
Notebooks 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
Lenovo 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 %>
Hard Drive The output generated by this code is shown here. An error occurred updating the NonFatal table
Travelstar 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.
[ Comment, Edit or Article Submission ]
