Using ADO to Handle Errors

The OLE DB error objects that are returned by the SAS providers are presented in the ADO interface as Error objects. Because it is possible for more than one error to be returned, the errors are stored in the Errors Collection. To retrieve complete error information, you iterate through the Errors Collection and output the information. In most cases, the "Description" and "Number" properties of the Error object provide the most helpful information.

The Visual Basic, VBScript, and Active Server Pages (ASP) examples all show how errors can be handled by using ADO. The VBScript and ASP examples perform the same tasks as the Visual Basic example, but there are language differences between them. When reviewing the sample code, keep the following differences in mind:

An ASP file typically embeds VBScript and delimits the VBScript with <% and %>.
VBScript does not predefine the ADO Enumerated Constants, so you must code them as constants.
In VBScript, you should create objects by using the CreateObject syntax.
VBScript usually provides different output than Visual Basic.
Usually, Visual Basic programmers handle errors by using the On Error Goto syntax. However, this syntax is not supported by VBScript, so the VBScript and ASP examples use a subroutine to handle error output.

Note: Although the examples use the Debug.Print and MsgBox methods to display error information, applications can use other methods such as Document.Write, or Response.Write. You can also write an application that traps errors instead of displaying error information.

The following examples use the local provider to attempt to open a table named lostDataset.

If lostDataset does not exist in the directory c:\testdata, then the application generates an error and outputs the error information. The "Description" property used in the examples is typically a string message that is composed by the SAS provider. However, it might also be a message from the server or some component that is used by the provider. The "Number" property is typically the HRESULT value that is returned by the underlying OLE DB interface method. In the examples, the value is output in hexadecimal because that is the way that it is typically written in the header files such as WinError.h and OleDBErr.h.

Visual Basic Error Handling

Sub Main()
 Dim obConnection As New ADODB.Connection
 Dim obRecordset As New ADODB.Recordset
 Dim errorObject As ADODB.Error
 
 On Error GoTo DisplayErrorInfo
 obConnection.Provider = "sas.LocalProvider"
 obConnection.Properties("Data Source") = "c:\testdata"

 obConnection.Open
 obRecordset.Open "lostDataset", obConnection, adOpenDynamic, adLockOptimistic, ADODB.adCmdTableDirect
 obRecordset.Close
 obConnection.Close

DisplayErrorInfo:
 For Each errorObject In obRecordset.ActiveConnection.Errors
 Debug.Print "Description :"; errorObject.Description
 Debug.Print "Number:"; Hex(errorObject.Number)
 Next
End Sub

VBScript Error Handling

<HTML>
Example showing error handling by using VBScript.

<SCRIPT LANGUAGE=VBSCRIPT>
Const adOpenDynamic = 2
Const adLockOptimistic = 3
Const adCmdTableDirect = 512

Set obConnection = CreateObject("ADODB.Connection")
Set obRecordset = CreateObject("ADODB.Recordset")
Set errorObject = CreateObject("ADODB.Error")

On Error Resume Next
obConnection.Provider = "sas.LocalProvider"
obConnection.Properties("Data Source") = "c:\testdata"

obConnection.Open
obRecordset.Open "lostDataset", obConnection, adOpenDynamic, adLockOptimistic, adCmdTableDirect
DisplayErrorInfo
obRecordset.Close
obConnection.Close


sub DisplayErrorInfo()
 For Each errorObject In obRecordset.ActiveConnection.Errors
 MsgBox "Description: " & errorObject.Description & Chr(10) & Chr(13) & _
 "Number: " & Hex(errorObject.Number)
 Next
End Sub
</SCRIPT>
</HTML>

ASP Error Handling

<HTML>
Example showing error handling by using VBScript.

<%
Const adOpenDynamic = 2
Const adLockOptimistic = 3
Const adCmdTableDirect = 512

Set obConnection = Server.CreateObject("ADODB.Connection")
Set obRecordset = Server.CreateObject("ADODB.Recordset")
Set errorObject = Server.CreateObject("ADODB.Error")

On Error Resume Next
obConnection.Provider = "sas.LocalProvider"
obConnection.Properties("Data Source") = "c:\testdata"

obConnection.Open
obRecordset.Open "lostDataset", obConnection, adOpenDynamic, adLockOptimistic, adCmdTableDirect
DisplayErrorInfo
obRecordset.Close
obConnection.Close

sub DisplayErrorInfo()
 For Each errorObject In obRecordset.ActiveConnection.Errors
 Response.Write "Description: " & errorObject.Description & Chr(10) & Chr(13) & _
 "Number: " & Hex(errorObject.Number)
 Next
End Sub 
%>
</SCRIPT>
</HTML>