Error Setting ActiveConnection of Command to Connection object


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Error Setting ActiveConnection of Command to Connection object

Hybrid View

  1. #1
    Ed Pinto Guest

    Error Setting ActiveConnection of Command to Connection object

    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



  2. #2
    Narasimhan Guest

    Re: Error Setting ActiveConnection of Command to Connection object


    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
    >
    >



  3. #3
    Ed Pinto Guest

    Re: Error Setting ActiveConnection of Command to Connection object

    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




  4. #4
    Narasimhan Guest

    Re: Error Setting ActiveConnection of Command to Connection object


    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
    >
    >
    >



  5. #5
    Dean Guest

    Re: Error Setting ActiveConnection of Command to Connection object


    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
    >>
    >>
    >>

    >



  6. #6
    Ed Pinto Guest

    Re: Error Setting ActiveConnection of Command to Connection object

    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
    > >>
    > >>
    > >>

    > >

    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center