Code to Retrieve Extended Error Information from the Commerce Server OLEDB Provider

This example script demonstrates retrieving extended error information from the Commerce Server OLEDB provider connection object in the event of an error. The example illustrates handling the entry of an incorrect SQL query statement: the correct statement to submit would include "GeneralInfo.logon_name", instead of "GeneralInfo.login_name" as is attempted in this example. After the errors are retrieved from the errors collection, they are displayed in a message box.

Option Explicit
' Declare the varibles and objects used.
Dim Dsn
Dim cn, rs
Dim Sql, ErrorMessage
Dim i

' Update the connection string to have the right server,
' user name and password.
Dsn="url=mscop://InProcConnect/Server=<server-name>:Database=BlankSite_COMMERCE:Catalog=Profile Definitions: Trusted_Connection=Yes:"

' Create the ADO connection and Record objects
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Record")

' Open the connection object using the Dsn.
cn.Open Dsn

' Build up the query.
Sql = ""
Sql = "SELECT "
Sql = Sql + "[GeneralInfo.login_name],"       
' Note: It should be [GeneralInfo.logon_name] and not [GeneralInfo.login_name]
Sql = Sql + "[GeneralInfo.tel_number]"
Sql = Sql+ "FROM [UserObject]"

' Set resume next condition to do custom error handling.
On error resume next

' Execute the query constructed above.
Set rs = cn.Execute(Sql)

' Check for error code.
if Err.number <> 0 then

   ' Reset custom error handling.
   On Error goto 0
   
   ' Set the error message to blank
   ErrorMessage = ""

   ' Loop over all the error messages in the errors collection.
   For i = cn.Errors.Count to 1 step -1
ErrorMessage = ErrorMessage & chr(10) _ 
   & "Error code: 0x" & CStr(Hex(cn.Errors(i-1).Number)) & chr(10) _
   & "Error description: " & cn.Errors(i-1).Description  _
   & "Source: " & cn.Errors(i-1).Source & chr(10)
   Next

   ' Display the error messages collected above.
   MsgBox ErrorMessage

end if

' Indicate that the operation completed.
MsgBox "Done!"

Copyright © 2005 Microsoft Corporation.
All rights reserved.