ADO Connection Passed as Variant Function Parameter

Posted on 2001-06-24
Medium Priority
Last Modified: 2008-03-10
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
Question by:minerj

Expert Comment

ID: 6223712
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.

Author Comment

ID: 6224856
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?  

Accepted Solution

nigelrowe earned 400 total points
ID: 6226678
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
LVL 49

Expert Comment

ID: 7139742
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

Expert Comment

ID: 7178622
Comment from expert accepted as answer

E-E Moderator

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question