Handling Errors and Messages in ADO

ADO applications use the Errors collection and the Error object to return provider-specific error information to an application. The Errors collection contains the errors generated by a single operation. Each Error object constitutes one such error in the collection. To get information about an error, query the properties of an Error object from the Connection object. To get all the Error objects in the Errors collection, use code to loop through the collection.

ADO errors (for example, invalid use of ADO properties or methods), as opposed to provider errors, do not appear in the Errors collection. ADO errors are captured by the exception handling mechanism of your run-time environment. For example, in Microsoft® Visual Basic®, the occurrence of an ADO error triggers an On Error event and appears as a Visual Basic Error object.

If you want to trap both provider-specific errors (by querying the properties of an Error object) and ADO errors (by trapping ADO errors through the run-time exception handler) in your application, you have to write error-handling code for both. For more information about ADO Error Codes, see the MSDN Library at Microsoft Web site.

Warning messages that do not stop code execution can be saved in the Errors collection. A warning message has a positive number value, which differentiates it from an error message.

However, critical warning or status messages (such as calls made with unsupported or conflicting properties) may be ignored by ADO and not saved to the Errors collection if the operation succeeded.

The properties of an Error object contain specific details about each error:

The Description property contains the text of the error.
The Number property contains the long integer value of the error constant.
The Source property identifies the object that raised the error.
The SQLState and NativeError properties provide information from SQL data sources.
The HelpFile and HelpContext properties indicate the appropriate Microsoft Windows® Help file and topic, respectively, (if any exist) for the error.

This code fragment, taken from the ADO Introductory Visual Basic Sample, shows how to create a basic data provider error log. The code enumerates the first five properties (all properties except for HelpFile and HelpContext) of each Error object in the Errors collection and displays them in a list on a Visual Basic form. In this example, the variable errLoop is an Error object in the Errors collection. The variable strError is an array of five strings, with each array element corresponding to a label and a specific property of an Error object. The routine loops through each Error object, exposes the value for each specified property, and displays the results as items in a list.The routine provides a count of the errors, using the Errors collection Count property, and clears out the Errors collection (using the Clear property).

Private Sub ErrorLog()
. . . 
Dim errLoop As ADODB.Error
. . . 
' Loop through each Error object in Errors collection.
For Each errLoop In cn.Errors

   Dim strError(5)
   Dim i As Integer
               
   strError(0) = "Error Number: " & errLoop.Number
   strError(1) = "  Description: " & errLoop.Description
   strError(2) = "  Source: " & errLoop.Source
   strError(3) = "  SQL State: " & errLoop.SQLState
   strError(4) = "  Native Error: " & errLoop.NativeError
                
   ' Loop through the five specified properties of Error object.
   i = 0
   Do While i < 5
      Form2.lstErrors.AddItem strError(i)
      i = i + 1
   Loop
                
   Form2.lstErrors.AddItem ""
            
Next 
            
' Create string for summary count of errors.
c = cn.Errors.Count & " provider error(s) occurred."
            
' Display a count of the provider errors.
Form2.lstErrors.AddItem c
Form2.lstErrors.AddItem ""

' Clear the Errors collection.
cn.Errors.Clear

Handling Errors and Messages in ADO

See Also