Error Setting ActiveConnection of Command to Connection object

[Ed Pinto]

When I try setting the ActiveConnection property of my Command object to a
valid and open Connection object I get ADO error 3001 telling me that: The
application is using arguments that are of the wrong type, are out of
acceptable range, or are in conflict with one another. I initially thought
this might be due to my CommandType setting so I changed it to unknown, but
this didn't help. Any ideas?
TIA,
Ed

[Narasimhan]

Hi,

can you post the code too?

Thanks
PLN

"Ed Pinto" <epinto@autoadmin.com> wrote:
>When I try setting the ActiveConnection property of my Command object to
a
>valid and open Connection object I get ADO error 3001 telling me that: The
>application is using arguments that are of the wrong type, are out of
>acceptable range, or are in conflict with one another. I initially thought
>this might be due to my CommandType setting so I changed it to unknown,
but
>this didn't help. Any ideas?
>TIA,
>Ed
>
>

[Ed Pinto]

Hi Narasimhan,
The code involves three methods in three different classes. The first is
this FetchBy. This is where the command object is created and parameters
are packed into it using the second function PackParameters. After the
command has been created and the parameters have been appended, the actual
fetching goes on in either the Fetch or the FetchCollection methods (not
shown here). The command is passed to one of these methods and is
immediately sent to the last method included here called
RecordsetFromCommand. I get the error in RecordsetFromCommand when I try to
set the ActiveConnection of the CommandObject to the connection object
(cnnConnection) that I opened at the beginning of the method.
Your help is greatly appreciated.
Ed

Private Function FetchBy(Params As Variant, ByVal FetchType As
cftCustomerFetchType) As String
Dim cmdCommand As Command
Dim objParameterHelper As IDParameterHelper

Set cmdCommand = New Command
cmdCommand.CommandType = adCmdStoredProc
cmdCommand.CommandText = LoadResString(SPOFFSET_CUSTOMER + FetchType)

'we'll use new here because the parameterhelper is not really used as
'an object, but more like a function library. (I.E., there is no need,
'present or future, to get the SCM involved.)
Set objParameterHelper = New CDParameterHelper
With objParameterHelper

On Error Resume Next

'Compose the SQL from the parameters and use the Fetch or
FetchCollection function
Select Case FetchType
'Fetch by CustomerID (PKID) complete with children
Case cftCustomerID
.PackParameters Params, cmdCommand, .MakeIntDesc(STR_PKID)
On Error GoTo 0
'This next block is repeated for each case because error
handling has to be
'turned off in the procedure raising the error so, we'll just
pack the statement
'all together to make it look less ugly
If Err.Number <> 0 Then RaiseFetchByError
FetchBy = Fetch(cmdCommand, FetchType)

'Fetch by CustomerCode complete with children
Case cftCustomerCode
.PackParameters Params, cmdCommand,
..MakeVarcharDesc(STR_CUSTOMERCODE, 10)
On Error GoTo 0
If Err.Number <> 0 Then RaiseFetchByError
FetchBy = Fetch(cmdCommand, FetchType)

'Return a collection of childless Customer data by CustomerID range
Case cftCustomerIDRange
.PackParameters Params, cmdCommand, .MakeIntDesc(STR_PKIDLO), _
.MakeIntDesc(STR_PKIDHI)
On Error GoTo 0
If Err.Number <> 0 Then RaiseFetchByError
FetchBy = FetchCollection(cmdCommand, FetchType)

'Return a collection of PKIDs (no children) for all customers who
are interest exempt
Case cftInterestExempt
.PackParameters Params, cmdCommand,
..MakeSmallIntDesc(STR_INTERESTEXEMPT)
On Error GoTo 0
If Err.Number <> 0 Then RaiseFetchByError
FetchBy = FetchCollection(cmdCommand, FetchType)

'Fetch a childless Customer with a total for all the customer's
invoices
Case cftCustomerInvoiceTotal
.PackParameters Params, cmdCommand, .MakeSmallIntDesc(STR_PKID)
On Error GoTo 0
If Err.Number <> 0 Then RaiseFetchByError
'don't forget to advise the fetch command that an additional
field is being requested
FetchBy = Fetch(cmdCommand, FetchType, STR_INVOICETOTAL)

End Select

End With
Set objParameterHelper = Nothing

'flag the COM+ transaction as complete and get out
If mflgMTS Then GetObjectContext.SetComplete
End Function

Private Sub PackParameters(Parms As Variant, ByRef TargetCommand As Command,
Types() As Variant)
Const METHODNAME As String = "PackParameters"
Dim avntTypes() As Variant
Dim avntParms() As Variant
Dim lngParmIndex As Long
Dim lngParmsBase As Long
Dim vbParmType As VbVarType
'Although we might not use each of these udts, it is better to alocate
the memory
'once at the begining of this method than several times within the loop
through
'the types
Dim udtNumericDesc As NumericDesc
Dim udtVarcharDesc As VarcharDesc
Dim udtIntDesc As IntDesc
Dim udtSmallintDesc As SmallIntDesc

If Not (VarType(Parms) And vbArray) = vbArray Then
ReDim avntParms(0)
avntParms(0) = Parms
Else
avntParms = Parms
End If

'make sure the number of parameters and types match
lngParmsBase = LBound(avntParms)
If Not UBound(avntParms) - lngParmsBase = UBound(Types) - LBound(Types)
Then _
RaiseError ERR_SMSGPARMMISMATCH, SOURCENAME, METHODNAME

avntTypes = Types
For lngParmIndex = LBound(avntTypes) To UBound(avntTypes)
vbParmType = VarType(avntParms(lngParmIndex + lngParmsBase))
Select Case TypeName(avntTypes(lngParmIndex))
Case "NumericDesc"
If Not (vbParmType Or vbDouble) = vbDouble Then
RaiseFetchMismatch
udtNumericDesc = avntTypes(lngParmIndex)
TargetCommand.Parameters.Append _
CreateNumericParameter(udtNumericDesc,
CDbl(avntParms(lngParmIndex + lngParmsBase)))
Case "VarcharDesc"
If Not (vbParmType Or vbString) = vbString Then
RaiseFetchMismatch
udtVarcharDesc = avntTypes(lngParmIndex)
TargetCommand.Parameters.Append _
TargetCommand.CreateParameter(udtVarcharDesc.ParmName,
adVarChar, _
udtVarcharDesc.Direction, udtVarcharDesc.Size, _
avntParms(lngParmIndex + lngParmsBase))
Case "IntDesc"
If Not (vbParmType Or vbLong) = vbLong Then
RaiseFetchMismatch
udtIntDesc = avntTypes(lngParmIndex)
TargetCommand.Parameters.Append _
TargetCommand.CreateParameter(udtIntDesc.ParmName,
adInteger, _
udtIntDesc.Direction, , avntParms(lngParmIndex +
lngParmsBase))
Case "SmallIntDesc"
If Not (vbParmType Or vbInteger) = vbInteger Then
RaiseFetchMismatch
udtSmallintDesc = avntTypes(lngParmIndex)
TargetCommand.Parameters.Append _
TargetCommand.CreateParameter(udtSmallintDesc.ParmName,
adInteger, _
udtSmallintDesc.Direction, , avntParms(lngParmIndex +
lngParmsBase))
End Select
Next

End Sub

Public Function RecordsetFromCommand(CommandObject As Command,
FileSourceName As String, MethodSourceName As String) As Recordset
Dim objConnectString As IDConnectionString
Dim strConnectString As String
Dim cnnConnection As ADODB.Connection
Dim rstReturnRecordset As Recordset

'retrieve the connection string specified by the administrator in the
COM+ catalog
Set objConnectString = CreateObject(PI_DCOMMONDOT &
CN_CDCONNECTIONSTRING)
strConnectString = objConnectString.GetConnectionString
Set objConnectString = Nothing

'!!DEFER: In the current implementation, we'll raise an error if no
connection string
'is provided by the admin. At some point, we might want to handle
default connection
'settings???
If Len(strConnectString) = 0 Then _
RaiseTransactedError ERR_DCONNSTRMISSING, FileSourceName,
MethodSourceName, _
GetObjectContext

'Error handling on
On Error Resume Next

'create a new connection object and open it
Set cnnConnection = New ADODB.Connection
cnnConnection.Open strConnectString

If Err.Number = eeNone Then
'if we opened the connection without error then set the
commandobject's connection
'to this open one


'THIS IS WHERE I GET THE ERROR''''''''''''''''''''''''''''''''''''''
Set CommandObject.ActiveConnection = cnnConnection ''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''

'Open up the recordset using the command object
Set rstReturnRecordset = CommandObject.Execute
'close the connection
cnnConnection.Close
End If

'error handling off
On Error GoTo 0

'now we check the error object. If we encountered an error when trying
to connect
'or trying to retrieve the recordset then we will see it here
If Err.Number <> eeNone Then
'translate the error to something that one of the
'data services layer's clients can use
'Although GDCommonFunctions is a global multiuse class, this only
applies to
'clients outside the project so we'll have to instantiate the class
to access its
'methods
Dim objCommonFunctions As GDCommonFunctions

Set objCommonFunctions = New GDCommonFunctions
objCommonFunctions.ConvertADOCommandError Err,
CommandObject.ActiveConnection, _
FileSourceName, MethodSourceName
Set objCommonFunctions = Nothing

'clean up !!VB7: When VB7 comes out, put this in the finally clause
of the
'try, catch, finally construct
Set rstReturnRecordset = Nothing
Set cnnConnection = Nothing

'And finally, raise the error whilst disabling the transaction
RaiseTransactedErrorToCaller Err, FileSourceName, MethodSourceName,
_
GetObjectContext
End If

'return the requested recordset
Set RecordsetFromCommand = rstReturnRecordset

'clean up !!VB7: When VB7 comes out, put this in the finally clause of
the
'try, catch, finally construct
Set cnnConnection = Nothing
Set rstReturnRecordset = Nothing
End Function

[Narasimhan]

Hi Ed,

I went thru your code and it seems ok to me. Can you check up the two methods
that have not been listed here for any code where you have set the command
object to Nothing.

Thanks
Narasimhan
"Ed Pinto" <epinto@autoadmin.com> wrote:
>Hi Narasimhan,
>The code involves three methods in three different classes. The first is
>this FetchBy. This is where the command object is created and parameters
>are packed into it using the second function PackParameters. After the
>command has been created and the parameters have been appended, the actual
>fetching goes on in either the Fetch or the FetchCollection methods (not
>shown here). The command is passed to one of these methods and is
>immediately sent to the last method included here called
>RecordsetFromCommand. I get the error in RecordsetFromCommand when I try
to
>set the ActiveConnection of the CommandObject to the connection object
>(cnnConnection) that I opened at the beginning of the method.
>Your help is greatly appreciated.
>Ed
>
>Private Function FetchBy(Params As Variant, ByVal FetchType As
>cftCustomerFetchType) As String
> Dim cmdCommand As Command
> Dim objParameterHelper As IDParameterHelper
>
> Set cmdCommand = New Command
> cmdCommand.CommandType = adCmdStoredProc
> cmdCommand.CommandText = LoadResString(SPOFFSET_CUSTOMER + FetchType)
>
> 'we'll use new here because the parameterhelper is not really used as
> 'an object, but more like a function library. (I.E., there is no need,
> 'present or future, to get the SCM involved.)
> Set objParameterHelper = New CDParameterHelper
> With objParameterHelper
>
> On Error Resume Next
>
> 'Compose the SQL from the parameters and use the Fetch or
>FetchCollection function
> Select Case FetchType
> 'Fetch by CustomerID (PKID) complete with children
> Case cftCustomerID
> .PackParameters Params, cmdCommand, .MakeIntDesc(STR_PKID)
> On Error GoTo 0
> 'This next block is repeated for each case because error
>handling has to be
> 'turned off in the procedure raising the error so, we'll just
>pack the statement
> 'all together to make it look less ugly
> If Err.Number <> 0 Then RaiseFetchByError
> FetchBy = Fetch(cmdCommand, FetchType)
>
> 'Fetch by CustomerCode complete with children
> Case cftCustomerCode
> .PackParameters Params, cmdCommand,
>..MakeVarcharDesc(STR_CUSTOMERCODE, 10)
> On Error GoTo 0
> If Err.Number <> 0 Then RaiseFetchByError
> FetchBy = Fetch(cmdCommand, FetchType)
>
> 'Return a collection of childless Customer data by CustomerID range
> Case cftCustomerIDRange
> .PackParameters Params, cmdCommand, .MakeIntDesc(STR_PKIDLO),
_
> .MakeIntDesc(STR_PKIDHI)
> On Error GoTo 0
> If Err.Number <> 0 Then RaiseFetchByError
> FetchBy = FetchCollection(cmdCommand, FetchType)
>
> 'Return a collection of PKIDs (no children) for all customers who
>are interest exempt
> Case cftInterestExempt
> .PackParameters Params, cmdCommand,
>..MakeSmallIntDesc(STR_INTERESTEXEMPT)
> On Error GoTo 0
> If Err.Number <> 0 Then RaiseFetchByError
> FetchBy = FetchCollection(cmdCommand, FetchType)
>
> 'Fetch a childless Customer with a total for all the customer's
>invoices
> Case cftCustomerInvoiceTotal
> .PackParameters Params, cmdCommand, .MakeSmallIntDesc(STR_PKID)
> On Error GoTo 0
> If Err.Number <> 0 Then RaiseFetchByError
> 'don't forget to advise the fetch command that an additional
>field is being requested
> FetchBy = Fetch(cmdCommand, FetchType, STR_INVOICETOTAL)
>
> End Select
>
> End With
> Set objParameterHelper = Nothing
>
> 'flag the COM+ transaction as complete and get out
> If mflgMTS Then GetObjectContext.SetComplete
>End Function
>
>Private Sub PackParameters(Parms As Variant, ByRef TargetCommand As Command,
>Types() As Variant)
> Const METHODNAME As String = "PackParameters"
> Dim avntTypes() As Variant
> Dim avntParms() As Variant
> Dim lngParmIndex As Long
> Dim lngParmsBase As Long
> Dim vbParmType As VbVarType
> 'Although we might not use each of these udts, it is better to alocate
>the memory
> 'once at the begining of this method than several times within the loop
>through
> 'the types
> Dim udtNumericDesc As NumericDesc
> Dim udtVarcharDesc As VarcharDesc
> Dim udtIntDesc As IntDesc
> Dim udtSmallintDesc As SmallIntDesc
>
> If Not (VarType(Parms) And vbArray) = vbArray Then
> ReDim avntParms(0)
> avntParms(0) = Parms
> Else
> avntParms = Parms
> End If
>
> 'make sure the number of parameters and types match
> lngParmsBase = LBound(avntParms)
> If Not UBound(avntParms) - lngParmsBase = UBound(Types) - LBound(Types)
>Then _
> RaiseError ERR_SMSGPARMMISMATCH, SOURCENAME, METHODNAME
>
> avntTypes = Types
> For lngParmIndex = LBound(avntTypes) To UBound(avntTypes)
> vbParmType = VarType(avntParms(lngParmIndex + lngParmsBase))
> Select Case TypeName(avntTypes(lngParmIndex))
> Case "NumericDesc"
> If Not (vbParmType Or vbDouble) = vbDouble Then
>RaiseFetchMismatch
> udtNumericDesc = avntTypes(lngParmIndex)
> TargetCommand.Parameters.Append _
> CreateNumericParameter(udtNumericDesc,
>CDbl(avntParms(lngParmIndex + lngParmsBase)))
> Case "VarcharDesc"
> If Not (vbParmType Or vbString) = vbString Then
>RaiseFetchMismatch
> udtVarcharDesc = avntTypes(lngParmIndex)
> TargetCommand.Parameters.Append _
> TargetCommand.CreateParameter(udtVarcharDesc.ParmName,
>adVarChar, _
> udtVarcharDesc.Direction, udtVarcharDesc.Size, _
> avntParms(lngParmIndex + lngParmsBase))
> Case "IntDesc"
> If Not (vbParmType Or vbLong) = vbLong Then
>RaiseFetchMismatch
> udtIntDesc = avntTypes(lngParmIndex)
> TargetCommand.Parameters.Append _
> TargetCommand.CreateParameter(udtIntDesc.ParmName,
>adInteger, _
> udtIntDesc.Direction, , avntParms(lngParmIndex +
>lngParmsBase))
> Case "SmallIntDesc"
> If Not (vbParmType Or vbInteger) = vbInteger Then
>RaiseFetchMismatch
> udtSmallintDesc = avntTypes(lngParmIndex)
> TargetCommand.Parameters.Append _
> TargetCommand.CreateParameter(udtSmallintDesc.ParmName,
>adInteger, _
> udtSmallintDesc.Direction, , avntParms(lngParmIndex +
>lngParmsBase))
> End Select
> Next
>
>End Sub
>
>Public Function RecordsetFromCommand(CommandObject As Command,
>FileSourceName As String, MethodSourceName As String) As Recordset
> Dim objConnectString As IDConnectionString
> Dim strConnectString As String
> Dim cnnConnection As ADODB.Connection
> Dim rstReturnRecordset As Recordset
>
> 'retrieve the connection string specified by the administrator in the
>COM+ catalog
> Set objConnectString = CreateObject(PI_DCOMMONDOT &
>CN_CDCONNECTIONSTRING)
> strConnectString = objConnectString.GetConnectionString
> Set objConnectString = Nothing
>
> '!!DEFER: In the current implementation, we'll raise an error if no
>connection string
> 'is provided by the admin. At some point, we might want to handle
>default connection
> 'settings???
> If Len(strConnectString) = 0 Then _
> RaiseTransactedError ERR_DCONNSTRMISSING, FileSourceName,
>MethodSourceName, _
> GetObjectContext
>
> 'Error handling on
> On Error Resume Next
>
> 'create a new connection object and open it
> Set cnnConnection = New ADODB.Connection
> cnnConnection.Open strConnectString
>
> If Err.Number = eeNone Then
> 'if we opened the connection without error then set the
>commandobject's connection
> 'to this open one
>
>
> 'THIS IS WHERE I GET THE ERROR''''''''''''''''''''''''''''''''''''''
> Set CommandObject.ActiveConnection = cnnConnection ''
>
>''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>''''''''''''''''''''''''''''''''''''''''''
>
> 'Open up the recordset using the command object
> Set rstReturnRecordset = CommandObject.Execute
> 'close the connection
> cnnConnection.Close
> End If
>
> 'error handling off
> On Error GoTo 0
>
> 'now we check the error object. If we encountered an error when trying
>to connect
> 'or trying to retrieve the recordset then we will see it here
> If Err.Number <> eeNone Then
> 'translate the error to something that one of the
> 'data services layer's clients can use
> 'Although GDCommonFunctions is a global multiuse class, this only
>applies to
> 'clients outside the project so we'll have to instantiate the class
>to access its
> 'methods
> Dim objCommonFunctions As GDCommonFunctions
>
> Set objCommonFunctions = New GDCommonFunctions
> objCommonFunctions.ConvertADOCommandError Err,
>CommandObject.ActiveConnection, _
> FileSourceName, MethodSourceName
> Set objCommonFunctions = Nothing
>
> 'clean up !!VB7: When VB7 comes out, put this in the finally clause
>of the
> 'try, catch, finally construct
> Set rstReturnRecordset = Nothing
> Set cnnConnection = Nothing
>
> 'And finally, raise the error whilst disabling the transaction
> RaiseTransactedErrorToCaller Err, FileSourceName, MethodSourceName,
>_
> GetObjectContext
> End If
>
> 'return the requested recordset
> Set RecordsetFromCommand = rstReturnRecordset
>
> 'clean up !!VB7: When VB7 comes out, put this in the finally clause
of
>the
> 'try, catch, finally construct
> Set cnnConnection = Nothing
> Set rstReturnRecordset = Nothing
>End Function
>
>
>

[Dean]

Had the same problem. The problems that I found were passing ADO command
and parameter objects around in method calls and having parameters loaded
in a connection object before I set the active connection. I had a routine
that accepted a command object as an argument and then tried to set the active
connection property in the routine. What I ended up doing was creating a
command in the routine and using a for each loop to go through the command
object passed in and loading the private command object parameters collection.
Then everybody was happy.
~DL

"Narasimhan" <plnar@yahoo.com> wrote:
>
>Hi Ed,
>
>I went thru your code and it seems ok to me. Can you check up the two methods
>that have not been listed here for any code where you have set the command
>object to Nothing.
>
>Thanks
>Narasimhan
>"Ed Pinto" <epinto@autoadmin.com> wrote:
>>Hi Narasimhan,
>>The code involves three methods in three different classes. The first
is
>>this FetchBy. This is where the command object is created and parameters
>>are packed into it using the second function PackParameters. After the
>>command has been created and the parameters have been appended, the actual
>>fetching goes on in either the Fetch or the FetchCollection methods (not
>>shown here). The command is passed to one of these methods and is
>>immediately sent to the last method included here called
>>RecordsetFromCommand. I get the error in RecordsetFromCommand when I try
>to
>>set the ActiveConnection of the CommandObject to the connection object
>>(cnnConnection) that I opened at the beginning of the method.
>>Your help is greatly appreciated.
>>Ed
>>
>>Private Function FetchBy(Params As Variant, ByVal FetchType As
>>cftCustomerFetchType) As String
>> Dim cmdCommand As Command
>> Dim objParameterHelper As IDParameterHelper
>>
>> Set cmdCommand = New Command
>> cmdCommand.CommandType = adCmdStoredProc
>> cmdCommand.CommandText = LoadResString(SPOFFSET_CUSTOMER + FetchType)
>>
>> 'we'll use new here because the parameterhelper is not really used
as
>> 'an object, but more like a function library. (I.E., there is no need,
>> 'present or future, to get the SCM involved.)
>> Set objParameterHelper = New CDParameterHelper
>> With objParameterHelper
>>
>> On Error Resume Next
>>
>> 'Compose the SQL from the parameters and use the Fetch or
>>FetchCollection function
>> Select Case FetchType
>> 'Fetch by CustomerID (PKID) complete with children
>> Case cftCustomerID
>> .PackParameters Params, cmdCommand, .MakeIntDesc(STR_PKID)
>> On Error GoTo 0
>> 'This next block is repeated for each case because error
>>handling has to be
>> 'turned off in the procedure raising the error so, we'll just
>>pack the statement
>> 'all together to make it look less ugly
>> If Err.Number <> 0 Then RaiseFetchByError
>> FetchBy = Fetch(cmdCommand, FetchType)
>>
>> 'Fetch by CustomerCode complete with children
>> Case cftCustomerCode
>> .PackParameters Params, cmdCommand,
>>..MakeVarcharDesc(STR_CUSTOMERCODE, 10)
>> On Error GoTo 0
>> If Err.Number <> 0 Then RaiseFetchByError
>> FetchBy = Fetch(cmdCommand, FetchType)
>>
>> 'Return a collection of childless Customer data by CustomerID range
>> Case cftCustomerIDRange
>> .PackParameters Params, cmdCommand, .MakeIntDesc(STR_PKIDLO),
>_
>> .MakeIntDesc(STR_PKIDHI)
>> On Error GoTo 0
>> If Err.Number <> 0 Then RaiseFetchByError
>> FetchBy = FetchCollection(cmdCommand, FetchType)
>>
>> 'Return a collection of PKIDs (no children) for all customers who
>>are interest exempt
>> Case cftInterestExempt
>> .PackParameters Params, cmdCommand,
>>..MakeSmallIntDesc(STR_INTERESTEXEMPT)
>> On Error GoTo 0
>> If Err.Number <> 0 Then RaiseFetchByError
>> FetchBy = FetchCollection(cmdCommand, FetchType)
>>
>> 'Fetch a childless Customer with a total for all the customer's
>>invoices
>> Case cftCustomerInvoiceTotal
>> .PackParameters Params, cmdCommand, .MakeSmallIntDesc(STR_PKID)
>> On Error GoTo 0
>> If Err.Number <> 0 Then RaiseFetchByError
>> 'don't forget to advise the fetch command that an additional
>>field is being requested
>> FetchBy = Fetch(cmdCommand, FetchType, STR_INVOICETOTAL)
>>
>> End Select
>>
>> End With
>> Set objParameterHelper = Nothing
>>
>> 'flag the COM+ transaction as complete and get out
>> If mflgMTS Then GetObjectContext.SetComplete
>>End Function
>>
>>Private Sub PackParameters(Parms As Variant, ByRef TargetCommand As Command,
>>Types() As Variant)
>> Const METHODNAME As String = "PackParameters"
>> Dim avntTypes() As Variant
>> Dim avntParms() As Variant
>> Dim lngParmIndex As Long
>> Dim lngParmsBase As Long
>> Dim vbParmType As VbVarType
>> 'Although we might not use each of these udts, it is better to alocate
>>the memory
>> 'once at the begining of this method than several times within the
loop
>>through
>> 'the types
>> Dim udtNumericDesc As NumericDesc
>> Dim udtVarcharDesc As VarcharDesc
>> Dim udtIntDesc As IntDesc
>> Dim udtSmallintDesc As SmallIntDesc
>>
>> If Not (VarType(Parms) And vbArray) = vbArray Then
>> ReDim avntParms(0)
>> avntParms(0) = Parms
>> Else
>> avntParms = Parms
>> End If
>>
>> 'make sure the number of parameters and types match
>> lngParmsBase = LBound(avntParms)
>> If Not UBound(avntParms) - lngParmsBase = UBound(Types) - LBound(Types)
>>Then _
>> RaiseError ERR_SMSGPARMMISMATCH, SOURCENAME, METHODNAME
>>
>> avntTypes = Types
>> For lngParmIndex = LBound(avntTypes) To UBound(avntTypes)
>> vbParmType = VarType(avntParms(lngParmIndex + lngParmsBase))
>> Select Case TypeName(avntTypes(lngParmIndex))
>> Case "NumericDesc"
>> If Not (vbParmType Or vbDouble) = vbDouble Then
>>RaiseFetchMismatch
>> udtNumericDesc = avntTypes(lngParmIndex)
>> TargetCommand.Parameters.Append _
>> CreateNumericParameter(udtNumericDesc,
>>CDbl(avntParms(lngParmIndex + lngParmsBase)))
>> Case "VarcharDesc"
>> If Not (vbParmType Or vbString) = vbString Then
>>RaiseFetchMismatch
>> udtVarcharDesc = avntTypes(lngParmIndex)
>> TargetCommand.Parameters.Append _
>> TargetCommand.CreateParameter(udtVarcharDesc.ParmName,
>>adVarChar, _
>> udtVarcharDesc.Direction, udtVarcharDesc.Size, _
>> avntParms(lngParmIndex + lngParmsBase))
>> Case "IntDesc"
>> If Not (vbParmType Or vbLong) = vbLong Then
>>RaiseFetchMismatch
>> udtIntDesc = avntTypes(lngParmIndex)
>> TargetCommand.Parameters.Append _
>> TargetCommand.CreateParameter(udtIntDesc.ParmName,
>>adInteger, _
>> udtIntDesc.Direction, , avntParms(lngParmIndex +
>>lngParmsBase))
>> Case "SmallIntDesc"
>> If Not (vbParmType Or vbInteger) = vbInteger Then
>>RaiseFetchMismatch
>> udtSmallintDesc = avntTypes(lngParmIndex)
>> TargetCommand.Parameters.Append _
>> TargetCommand.CreateParameter(udtSmallintDesc.ParmName,
>>adInteger, _
>> udtSmallintDesc.Direction, , avntParms(lngParmIndex +
>>lngParmsBase))
>> End Select
>> Next
>>
>>End Sub
>>
>>Public Function RecordsetFromCommand(CommandObject As Command,
>>FileSourceName As String, MethodSourceName As String) As Recordset
>> Dim objConnectString As IDConnectionString
>> Dim strConnectString As String
>> Dim cnnConnection As ADODB.Connection
>> Dim rstReturnRecordset As Recordset
>>
>> 'retrieve the connection string specified by the administrator in the
>>COM+ catalog
>> Set objConnectString = CreateObject(PI_DCOMMONDOT &
>>CN_CDCONNECTIONSTRING)
>> strConnectString = objConnectString.GetConnectionString
>> Set objConnectString = Nothing
>>
>> '!!DEFER: In the current implementation, we'll raise an error if no
>>connection string
>> 'is provided by the admin. At some point, we might want to handle
>>default connection
>> 'settings???
>> If Len(strConnectString) = 0 Then _
>> RaiseTransactedError ERR_DCONNSTRMISSING, FileSourceName,
>>MethodSourceName, _
>> GetObjectContext
>>
>> 'Error handling on
>> On Error Resume Next
>>
>> 'create a new connection object and open it
>> Set cnnConnection = New ADODB.Connection
>> cnnConnection.Open strConnectString
>>
>> If Err.Number = eeNone Then
>> 'if we opened the connection without error then set the
>>commandobject's connection
>> 'to this open one
>>
>>
>> 'THIS IS WHERE I GET THE ERROR''''''''''''''''''''''''''''''''''''''
>> Set CommandObject.ActiveConnection = cnnConnection ''
>>
>>''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
>>''''''''''''''''''''''''''''''''''''''''''
>>
>> 'Open up the recordset using the command object
>> Set rstReturnRecordset = CommandObject.Execute
>> 'close the connection
>> cnnConnection.Close
>> End If
>>
>> 'error handling off
>> On Error GoTo 0
>>
>> 'now we check the error object. If we encountered an error when trying
>>to connect
>> 'or trying to retrieve the recordset then we will see it here
>> If Err.Number <> eeNone Then
>> 'translate the error to something that one of the
>> 'data services layer's clients can use
>> 'Although GDCommonFunctions is a global multiuse class, this only
>>applies to
>> 'clients outside the project so we'll have to instantiate the class
>>to access its
>> 'methods
>> Dim objCommonFunctions As GDCommonFunctions
>>
>> Set objCommonFunctions = New GDCommonFunctions
>> objCommonFunctions.ConvertADOCommandError Err,
>>CommandObject.ActiveConnection, _
>> FileSourceName, MethodSourceName
>> Set objCommonFunctions = Nothing
>>
>> 'clean up !!VB7: When VB7 comes out, put this in the finally clause
>>of the
>> 'try, catch, finally construct
>> Set rstReturnRecordset = Nothing
>> Set cnnConnection = Nothing
>>
>> 'And finally, raise the error whilst disabling the transaction
>> RaiseTransactedErrorToCaller Err, FileSourceName, MethodSourceName,
>>_
>> GetObjectContext
>> End If
>>
>> 'return the requested recordset
>> Set RecordsetFromCommand = rstReturnRecordset
>>
>> 'clean up !!VB7: When VB7 comes out, put this in the finally clause
>of
>>the
>> 'try, catch, finally construct
>> Set cnnConnection = Nothing
>> Set rstReturnRecordset = Nothing
>>End Function
>>
>>
>>
>

[Ed Pinto]

DOH! I think I fixed the problem. The PackParameters method belonged to a
class that did not support transactions in COM+. So as the command was
passed from an object that supported transactions to an object that didn't
and then back again, it must have gotten pretty confused about its context.
I made the CDParameterHelper class support transactions in the COM+ catalog
and everything worked fine.

"Dean" <dlarson2@usinternet.com> wrote in message
news:38d812a9$1@news.devx.com...
>
> Had the same problem. The problems that I found were passing ADO command
> and parameter objects around in method calls and having parameters loaded
> in a connection object before I set the active connection. I had a
routine
> that accepted a command object as an argument and then tried to set the
active
> connection property in the routine. What I ended up doing was creating a
> command in the routine and using a for each loop to go through the command
> object passed in and loading the private command object parameters
collection.
> Then everybody was happy.
> ~DL
>
> "Narasimhan" <plnar@yahoo.com> wrote:
> >
> >Hi Ed,
> >
> >I went thru your code and it seems ok to me. Can you check up the two
methods
> >that have not been listed here for any code where you have set the
command
> >object to Nothing.
> >
> >Thanks
> >Narasimhan
> >"Ed Pinto" <epinto@autoadmin.com> wrote:
> >>Hi Narasimhan,
> >>The code involves three methods in three different classes. The first
> is
> >>this FetchBy. This is where the command object is created and
parameters
> >>are packed into it using the second function PackParameters. After the
> >>command has been created and the parameters have been appended, the
actual
> >>fetching goes on in either the Fetch or the FetchCollection methods (not
> >>shown here). The command is passed to one of these methods and is
> >>immediately sent to the last method included here called
> >>RecordsetFromCommand. I get the error in RecordsetFromCommand when I
try
> >to
> >>set the ActiveConnection of the CommandObject to the connection object
> >>(cnnConnection) that I opened at the beginning of the method.
> >>Your help is greatly appreciated.
> >>Ed
> >>
> >>Private Function FetchBy(Params As Variant, ByVal FetchType As
> >>cftCustomerFetchType) As String
> >> Dim cmdCommand As Command
> >> Dim objParameterHelper As IDParameterHelper
> >>
> >> Set cmdCommand = New Command
> >> cmdCommand.CommandType = adCmdStoredProc
> >> cmdCommand.CommandText = LoadResString(SPOFFSET_CUSTOMER +
FetchType)
> >>
> >> 'we'll use new here because the parameterhelper is not really used
> as
> >> 'an object, but more like a function library. (I.E., there is no
need,
> >> 'present or future, to get the SCM involved.)
> >> Set objParameterHelper = New CDParameterHelper
> >> With objParameterHelper
> >>
> >> On Error Resume Next
> >>
> >> 'Compose the SQL from the parameters and use the Fetch or
> >>FetchCollection function
> >> Select Case FetchType
> >> 'Fetch by CustomerID (PKID) complete with children
> >> Case cftCustomerID
> >> .PackParameters Params, cmdCommand, .MakeIntDesc(STR_PKID)
> >> On Error GoTo 0
> >> 'This next block is repeated for each case because error
> >>handling has to be
> >> 'turned off in the procedure raising the error so, we'll
just
> >>pack the statement
> >> 'all together to make it look less ugly
> >> If Err.Number <> 0 Then RaiseFetchByError
> >> FetchBy = Fetch(cmdCommand, FetchType)
> >>
> >> 'Fetch by CustomerCode complete with children
> >> Case cftCustomerCode
> >> .PackParameters Params, cmdCommand,
> >>..MakeVarcharDesc(STR_CUSTOMERCODE, 10)
> >> On Error GoTo 0
> >> If Err.Number <> 0 Then RaiseFetchByError
> >> FetchBy = Fetch(cmdCommand, FetchType)
> >>
> >> 'Return a collection of childless Customer data by CustomerID ra
nge
> >> Case cftCustomerIDRange
> >> .PackParameters Params, cmdCommand,
..MakeIntDesc(STR_PKIDLO),
> >_
> >> .MakeIntDesc(STR_PKIDHI)
> >> On Error GoTo 0
> >> If Err.Number <> 0 Then RaiseFetchByError
> >> FetchBy = FetchCollection(cmdCommand, FetchType)
> >>
> >> 'Return a collection of PKIDs (no children) for all customers
who
> >>are interest exempt
> >> Case cftInterestExempt
> >> .PackParameters Params, cmdCommand,
> >>..MakeSmallIntDesc(STR_INTERESTEXEMPT)
> >> On Error GoTo 0
> >> If Err.Number <> 0 Then RaiseFetchByError
> >> FetchBy = FetchCollection(cmdCommand, FetchType)
> >>
> >> 'Fetch a childless Customer with a total for all the customer's
> >>invoices
> >> Case cftCustomerInvoiceTotal
> >> .PackParameters Params, cmdCommand,
..MakeSmallIntDesc(STR_PKID)
> >> On Error GoTo 0
> >> If Err.Number <> 0 Then RaiseFetchByError
> >> 'don't forget to advise the fetch command that an additional
> >>field is being requested
> >> FetchBy = Fetch(cmdCommand, FetchType, STR_INVOICETOTAL)
> >>
> >> End Select
> >>
> >> End With
> >> Set objParameterHelper = Nothing
> >>
> >> 'flag the COM+ transaction as complete and get out
> >> If mflgMTS Then GetObjectContext.SetComplete
> >>End Function
> >>
> >>Private Sub PackParameters(Parms As Variant, ByRef TargetCommand As
Command,
> >>Types() As Variant)
> >> Const METHODNAME As String = "PackParameters"
> >> Dim avntTypes() As Variant
> >> Dim avntParms() As Variant
> >> Dim lngParmIndex As Long
> >> Dim lngParmsBase As Long
> >> Dim vbParmType As VbVarType
> >> 'Although we might not use each of these udts, it is better to
alocate
> >>the memory
> >> 'once at the begining of this method than several times within the
> loop
> >>through
> >> 'the types
> >> Dim udtNumericDesc As NumericDesc
> >> Dim udtVarcharDesc As VarcharDesc
> >> Dim udtIntDesc As IntDesc
> >> Dim udtSmallintDesc As SmallIntDesc
> >>
> >> If Not (VarType(Parms) And vbArray) = vbArray Then
> >> ReDim avntParms(0)
> >> avntParms(0) = Parms
> >> Else
> >> avntParms = Parms
> >> End If
> >>
> >> 'make sure the number of parameters and types match
> >> lngParmsBase = LBound(avntParms)
> >> If Not UBound(avntParms) - lngParmsBase = UBound(Types) -
LBound(Types)
> >>Then _
> >> RaiseError ERR_SMSGPARMMISMATCH, SOURCENAME, METHODNAME
> >>
> >> avntTypes = Types
> >> For lngParmIndex = LBound(avntTypes) To UBound(avntTypes)
> >> vbParmType = VarType(avntParms(lngParmIndex + lngParmsBase))
> >> Select Case TypeName(avntTypes(lngParmIndex))
> >> Case "NumericDesc"
> >> If Not (vbParmType Or vbDouble) = vbDouble Then
> >>RaiseFetchMismatch
> >> udtNumericDesc = avntTypes(lngParmIndex)
> >> TargetCommand.Parameters.Append _
> >> CreateNumericParameter(udtNumericDesc,
> >>CDbl(avntParms(lngParmIndex + lngParmsBase)))
> >> Case "VarcharDesc"
> >> If Not (vbParmType Or vbString) = vbString Then
> >>RaiseFetchMismatch
> >> udtVarcharDesc = avntTypes(lngParmIndex)
> >> TargetCommand.Parameters.Append _
> >> TargetCommand.CreateParameter(udtVarcharDesc.ParmName,
> >>adVarChar, _
> >> udtVarcharDesc.Direction, udtVarcharDesc.Size, _
> >> avntParms(lngParmIndex + lngParmsBase))
> >> Case "IntDesc"
> >> If Not (vbParmType Or vbLong) = vbLong Then
> >>RaiseFetchMismatch
> >> udtIntDesc = avntTypes(lngParmIndex)
> >> TargetCommand.Parameters.Append _
> >> TargetCommand.CreateParameter(udtIntDesc.ParmName,
> >>adInteger, _
> >> udtIntDesc.Direction, , avntParms(lngParmIndex +
> >>lngParmsBase))
> >> Case "SmallIntDesc"
> >> If Not (vbParmType Or vbInteger) = vbInteger Then
> >>RaiseFetchMismatch
> >> udtSmallintDesc = avntTypes(lngParmIndex)
> >> TargetCommand.Parameters.Append _
> >>
TargetCommand.CreateParameter(udtSmallintDesc.ParmName,
> >>adInteger, _
> >> udtSmallintDesc.Direction, , avntParms(lngParmIndex +
> >>lngParmsBase))
> >> End Select
> >> Next
> >>
> >>End Sub
> >>
> >>Public Function RecordsetFromCommand(CommandObject As Command,
> >>FileSourceName As String, MethodSourceName As String) As Recordset
> >> Dim objConnectString As IDConnectionString
> >> Dim strConnectString As String
> >> Dim cnnConnection As ADODB.Connection
> >> Dim rstReturnRecordset As Recordset
> >>
> >> 'retrieve the connection string specified by the administrator in
the
> >>COM+ catalog
> >> Set objConnectString = CreateObject(PI_DCOMMONDOT &
> >>CN_CDCONNECTIONSTRING)
> >> strConnectString = objConnectString.GetConnectionString
> >> Set objConnectString = Nothing
> >>
> >> '!!DEFER: In the current implementation, we'll raise an error if no
> >>connection string
> >> 'is provided by the admin. At some point, we might want to handle
> >>default connection
> >> 'settings???
> >> If Len(strConnectString) = 0 Then _
> >> RaiseTransactedError ERR_DCONNSTRMISSING, FileSourceName,
> >>MethodSourceName, _
> >> GetObjectContext
> >>
> >> 'Error handling on
> >> On Error Resume Next
> >>
> >> 'create a new connection object and open it
> >> Set cnnConnection = New ADODB.Connection
> >> cnnConnection.Open strConnectString
> >>
> >> If Err.Number = eeNone Then
> >> 'if we opened the connection without error then set the
> >>commandobject's connection
> >> 'to this open one
> >>
> >>
> >> 'THIS IS WHERE I GET THE
ERROR''''''''''''''''''''''''''''''''''''''
> >> Set CommandObject.ActiveConnection = cnnConnection ''
> >>
>
>>''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''
> >>''''''''''''''''''''''''''''''''''''''''''
> >>
> >> 'Open up the recordset using the command object
> >> Set rstReturnRecordset = CommandObject.Execute
> >> 'close the connection
> >> cnnConnection.Close
> >> End If
> >>
> >> 'error handling off
> >> On Error GoTo 0
> >>
> >> 'now we check the error object. If we encountered an error when
trying
> >>to connect
> >> 'or trying to retrieve the recordset then we will see it here
> >> If Err.Number <> eeNone Then
> >> 'translate the error to something that one of the
> >> 'data services layer's clients can use
> >> 'Although GDCommonFunctions is a global multiuse class, this
only
> >>applies to
> >> 'clients outside the project so we'll have to instantiate the
class
> >>to access its
> >> 'methods
> >> Dim objCommonFunctions As GDCommonFunctions
> >>
> >> Set objCommonFunctions = New GDCommonFunctions
> >> objCommonFunctions.ConvertADOCommandError Err,
> >>CommandObject.ActiveConnection, _
> >> FileSourceName, MethodSourceName
> >> Set objCommonFunctions = Nothing
> >>
> >> 'clean up !!VB7: When VB7 comes out, put this in the finally
clause
> >>of the
> >> 'try, catch, finally construct
> >> Set rstReturnRecordset = Nothing
> >> Set cnnConnection = Nothing
> >>
> >> 'And finally, raise the error whilst disabling the transaction
> >> RaiseTransactedErrorToCaller Err, FileSourceName,
MethodSourceName,
> >>_
> >> GetObjectContext
> >> End If
> >>
> >> 'return the requested recordset
> >> Set RecordsetFromCommand = rstReturnRecordset
> >>
> >> 'clean up !!VB7: When VB7 comes out, put this in the finally clause
> >of
> >>the
> >> 'try, catch, finally construct
> >> Set cnnConnection = Nothing
> >> Set rstReturnRecordset = Nothing
> >>End Function
> >>
> >>
> >>
> >
>