Errors branch of ADO.Command object getting reset when passed to the error handler

[mdlueck]

Greetings,

I am seeing an unexpected behavior in A2007 working on enhancements to my Robust VBA Error Handler. I am teaching the error handler to be able to interrogate various object types for additional error condition information.

My first task was to interrogate MS XML 6 DOMDocument60 objects. I am very nicely able to hand off the MS XML 6 object to the error handler, it is able to receive the object, identify it as such, and enumerate the parseError data contained within it. Example:

 MSXML parseError:
  errorCode:            -1072896764
  errorParametersCount: 0
  errorXPath:          
  filepos:              806
  line:                 19
  linepos:              50
  reason:               A name was started with an invalid character.
  
  srcText:              This email is being sent via the --> Production <-- Fandango Agent Server! Woo hoo!!! :-)
  url:

Next I coded up a similar solution to be able to interrogate ADODB.Command objects to enumerate through the error information in those types of objects.

I am seeing that the error information in the adoCMD object is getting cleared at the point execution passes from the place in the code where the error occurred to my global error handler. Everything else appears to be kept intact in the adoCMD object received in the error handler. The error branch, however, is completely cleared. ggggrrrrr.....

LOC in the worker code which calls the error handler:

  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: UpdateSortPosition()", , [B]adoCMD[/B])

LOC which receives the object in the error handler:

Sub errorhandler_MsgBox(ByVal strAppErrorMsg As String, Optional ByVal flgLogErrorToDisk As Boolean = True, [B]Optional ByRef objMultiType As Object = Nothing[/B])

Suggestions please?

 

[mdlueck]

I am coming up with one theory which seems at least plausible about what is happening. Within the adoCMD object, I am finding the error information down the ActiveConnection path. The ADO.Connection object was a local object to the function that the error occurred in. Perhaps do adoCMD objects not embed a good enough instance of the connection object... it goes out of scope at the boundary of the function, and is not available for the error handler to inspect?

I updated my call to the error hadnler just to pass the ADO.Errors object portion of the entire adoCMD object:

  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: UpdateSortPosition()", , [B]adoCMD.ActiveConnection.Errors[/B])

Now the ADO.Errors object does have the error data.

In the case of not executing a Stored Procedure, I was interested in interrogating the CommandText of the adoCMD object. Now it seems I can have only one or the other, not both, within one object.

Still, suggestions please?

 

[mdlueck]

I coded around the deficiency as follows:

1) Use of custom class object to transfer the payload of data within one object. Blue are new LOCs.

Err_UpdateSortPosition:
[COLOR=Blue][B]  Dim ObjErrorHandlerADO As New clsObjErrorHandlerADO
  ObjErrorHandlerADO.ADOErrors = adoCMD.ActiveConnection.Errors
  ObjErrorHandlerADO.CommandText = adoCMD.CommandText
[/B][/COLOR]  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: UpdateSortPosition()", , [COLOR=Blue][B]ObjErrorHandlerADO[/B][/COLOR])
  UpdateSortPosition = False
  Resume Exit_UpdateSortPosition

2) Source code for new class file - needed to transport the payload:

Rem /************************************************************************************/
Rem /* FILENAME       :  clsObjErrorHandlerADO                                          */
Rem /* TYPE           :  VBA Class                                                      */
Rem /* DESCRIPTION    :  Class containing memory storage for ADO object Error Handling  */
Rem /*                                                                                  */
Rem /* AUTHOR         :  Michael D Lueck                                                */
Rem /*                   mlueck@lueckdatasystems.com                                    */
Rem /*                                                                                  */
Rem /* NEEDS          :                                                                 */
Rem /*                                                                                  */
Rem /* USAGE          :                                                                 */
Rem /*                                                                                  */
Rem /* REVISION HISTORY                                                                 */
Rem /*                                                                                  */
Rem /* DATE       REVISED BY DESCRIPTION OF CHANGE                                      */
Rem /* ---------- ---------- -------------------------------------------------------    */
Rem /* 04/23/2013 MDL        Initial Creation                                           */
Rem /************************************************************************************/

Option Compare Database
Option Explicit

Dim objADOErrors As Object
Dim strSQL As String

'ADOErrors API's
Public Property Get ADOErrors() As Object
On Error GoTo Err_ADOErrors

  Set ADOErrors = objADOErrors

Exit_ADOErrors:
  Exit Property

Err_ADOErrors:
  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Property: Get ADOErrors()")
  ADOErrors = Nothing
  Resume Exit_ADOErrors

End Property

Public Property Let ADOErrors(ByRef newADOErrors As Object)
On Error GoTo Err_ADOErrors

  Set objADOErrors = newADOErrors

Exit_ADOErrors:
  Exit Property

Err_ADOErrors:
  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Property: Let ADOErrors()")
  Resume Exit_ADOErrors

End Property

'SQL API's
Public Property Get SQL() As String
On Error GoTo Err_SQL

  SQL = strSQL

Exit_SQL:
  Exit Property

Err_SQL:
  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Property: Get SQL()")
  SQL = vbNullString
  Resume Exit_SQL

End Property

Public Property Let SQL(ByVal newSQL As String)
On Error GoTo Err_SQL

  strSQL = newSQL

Exit_SQL:
  Exit Property

Err_SQL:
  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Property: Let SQL()")
  Resume Exit_SQL

End Property

3) Custom output Message Box of the error handler:

 

[mdlueck]

Deja vu... here we go again... I am now working with A2010, a DB2 LUW BE DB, and am seeing the ADO ActiveConnection.Errors reset when I attempt to grab them into the ObjErrorHandlerADO object.

How are other dealing with ADO ActiveConnection.Errors objects in order to capture error information?

Also with ADO.Command objects which return an ADO.RecordSet object... in this case the ADO.Command ends so badly that an ADO.RecordSet object is never created as result of firing the .Execute() method, but other times .Execute() gets far enough that they are created. So suggestions how to automatically detect where to look for the ADO ActiveConnection.Errors objects without executing so much error handler code that the ADO ActiveConnection.Errors ends up getting destroyed / reset?

I am thankful,