[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

ADO Connection Passed as Variant Function Parameter

I am trying to build ASP ActiveX components which can store their own data. The client ASP script is responsible for creating an open ADO connection and then passing the Connection as a variant parameter to the component. The component in turn calls a private function to examine the "DBMS Name" property of the Connection object to determine what kind of database to talk to.

When I test out this approach, sometimes the component code reads the variant parameter as a Connection object properly and gives me the desired DBMS Name property; other times the code doesn't seem to recognize the variant as a Connection object and generates an exception with "Object required". When I use the Watch window, the expression DBConnect.Properties("DBMS Name") is evaluated as "Expression not defined in context" even when I CAN recognize and use this value. I am relatively new to VB and programming with Variants. Am I missing or violating something here?

Code for private function is:

Private Function Store(DBConnect as Variant) as Boolean
If DatabaseType = "" Then
DatabaseType = DBConnect.Properties("DBMS Connection")
End If
End Function

Any help greatly appreciated
1 Solution
It's not advisable to pass Connections round in DCOM. You should have a connection in each component, that way, you know exactly where the connection is and its state.
minerjAuthor Commented:
I need  some mechanism of supporting transactions.  If each component has to manage its own connections, then I understand that I will have to use MTS to implement transaction processing.  This means de-object orienting and decomposing all my components to implement the MTS object-based method of transaction processing.  This seems like significant overkill (at the moment)and extra work for me.  It's only for an internal intranet on one server.  It seems appropriate at this stage to leave transaction processing to the client through shared ADO Connections.
Is your recommendation based on principles of future scalability or because Variant parameters containing connections sometimes lose their memory?  
The problem with passing them around is that you cannot be sure what is happening to them. I indeed, use MTS. Each component is in the same transaction and handles its own connections, ie. the connection is opened and closed in each sub that requires it. I have two methods in each component, Abort and Destroy. In case of any error in the component, the GetObjectContext.SetAbort is called, therefore aborting the complete transaction that the object is in. At the end of use of the component, the Destroy method is called, releasing any resources and setting the transaction to completed for this particular component in the principal transaction.
I'm not sure what you mean by de-object orienting. The components that I create are, I hope object oriented (well, as far as that goes in VB). They are in fact business objects driving very large databases, where management of transactions would be very complicated an bug prone if it were left to the developer. There is one component (a sort of kernel) which supplies a connection to each component that requires to open a connection. So, I would have something like this in the Create sub of the component....

Public Sub Create(ByVal an_env As String, ByVal id_ruleset As Long)
On Error GoTo ErrorHandler
Dim Conn As New ADODB.Connection
Dim AIRuleSetRs As ADODB.Recordset
Dim sqlclause As String
Dim AIRule_Inst As AIRule
Dim AIRuleSetArray As Variant
Dim loop1 As Integer

    LoadEnv an_env
    'object_status = OS_INITIALIZED
    idruleset = id_ruleset
    Conn.Open Env.GetConnectionString
    sqlclause = "SELECT parameters, descruleset FROM AIRuleSet WHERE idruleset = " & idruleset
    Set AIRuleSetRs = Conn.Execute(sqlclause)
    descruleset = SQLDecode(AIRuleSetRs.Fields("descruleset").Value)
    parameters = SQLDecode(AIRuleSetRs.Fields("parameters").Value)
    Set evaluation_parameters = BuildParameterSet(parameters)
    Set Desc_Inst = GetObjectContext.CreateInstance("AIEngine.AIDescription")
    Desc_Inst.Create SerialEnv, descruleset
    Set rules_coll = New Collection
    Conn.Open Env.GetConnectionString
    sqlclause = "SELECT idrule, odrule FROM AIEvaluates" _
                & " WHERE idruleset = " & SQLEncode(idruleset) _
                & " ORDER BY odrule"
    Set AIRuleSetRs = Conn.Execute(sqlclause)
    If Not AIRuleSetRs.EOF Then
        AIRuleSetArray = AIRuleSetRs.GetRows(adGetRowsRest)
    End If
    If IsArray(AIRuleSetArray) Then
        For loop1 = LBound(AIRuleSetArray, 2) To UBound(AIRuleSetArray, 2)
            Set AIRule_Inst = GetObjectContext.CreateInstance("AIEngine.AIRule")
            AIRule_Inst.Create SerialEnv, SQLDecode(AIRuleSetArray(0, loop1))
            rules_coll.Add AIRule_Inst
    End If
    descruleset = GetDescription
    is_new = False
    Exit Sub
    Abort "Create"
    Err.Raise Err.Number, Err.Source, RTErrDescription(TypeName(Me), "Create", Err.Description)
End Sub
Hi minerj@devx,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will suggest to:

    Accept nigelrowe's comment(s) as an answer.

minerj@devx, if you think your question was not answered at all or if you need help, you can simply post a new comment here.  Community Support moderators will follow up.
DanRollins -- EE database cleanup volunteer
Comment from expert accepted as answer

E-E Moderator

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now