Solved

ADO Connection Passed as Variant Function Parameter

Posted on 2001-06-24
5
301 Views
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
0
Comment
Question by:minerj
5 Comments
 
LVL 3

Expert Comment

by:nigelrowe
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.
0
 

Author Comment

by:minerj
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?  
0
 
LVL 3

Accepted Solution

by:
nigelrowe earned 100 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)
    Conn.Close
    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
    Conn.Close
   
    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
        Next
    End If
   
    descruleset = GetDescription
    is_new = False
    Exit Sub
ErrorHandler:
    Abort "Create"
    Err.Raise Err.Number, Err.Source, RTErrDescription(TypeName(Me), "Create", Err.Description)
End Sub
0
 
LVL 49

Expert Comment

by:DanRollins
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
0
 
LVL 1

Expert Comment

by:Computer101
ID: 7178622
Comment from expert accepted as answer

Computer101
E-E Moderator
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now