Referencing an open connection on a component

Results 1 to 4 of 4

Thread: Referencing an open connection on a component

  1. #1
    PedroM Guest

    Default Referencing an open connection on a component

    Hi,<BR><BR>I&#039m trying to move my business logic to a custom ActiveX component but I keep on hitting the same problem: How do I reference an open connection on my component ? Here are the details:<BR><BR>1) Right at the start of a page that will need to use a connection to a database, I set it up and open the connection<BR><BR>2) When the need for the data arises, I use a custom function (which I&#039m trying to move to my component) that has as parameters my SQL query (i.e.: "SELECT * FROM customers") and the DB connection (i.e.: "dbConn") and returns an array (using GetRows()). The actual declaration is:<BR><BR>Public Function GetArray(strSQL, ByVal dbConn As ADODB.Connection) As Variant<BR><BR><BR>The thing is: while I was using this function on an include, everything was fine (which figures, since they are essentially on the same page). When I tried to move it to my component, no matter what I do I get a "Runtime error 3001: Arguments are of the wrong type, are out of acceptable range (...)" when I try to set the ActiveConnection of an ADODB.RecordSet that actually gets the data. <BR><BR>What I don&#039t get is that the connection object goes thru ok - TypeName(dbConn) returns "Connection" and I can check all of its properties such as State, DefaultDatabase, etc. etc. I just can&#039t assign the d*mn thing to the Recordset!<BR><BR>All of the examples I found on the Web involve passing the ConnectionString and opening the connection on the component. I don&#039t think that&#039s the best solution since on many pages I happen to access the data more than once, which means creating and destroying the connection several times. Is there really no other way ??<BR><BR>Cheers,<BR>PedroM

  2. #2
    Join Date
    Dec 1969

    Default RE: Referencing an open connection on a component

    Creating and destroying connections is more efficient than keeping them hanging around and trying to pass them. This is partly because ADO objects are apparently unable to "aggregate the Free-Threaded Marshaller" (whatever that means) and so can only be accessed from the thread in which they were created. There is also a lot of behind-the-scenes plumbing to pool connections without you being aware of it - when you close an OLE-DB connection it doesn&#039t actually get closed, it gets released into OLE-DB&#039s connection pool to be reused.<BR><BR>The rule for ADO objects is create late and release early - that&#039s the route to scalability.<BR><BR>Cheers<BR><BR>Dunc

  3. #3
    Richard A. Lowe Guest

    Default RE: Referencing an open connection on a component

    I&#039m really having trouble re-creating your problem. I open an ADODB connection on an ASP page and pass it to a component instance and use it with no probmlems whatso ever in many differnt ways.<BR><BR>Can you show us the code that A) passes the conn object and B) the vb from the component that accepts the conn object. Also what platform / version you are on? WinNT/9x/2k?? ADO2.0/2.1/2.5?<BR><BR>Richard

  4. #4
    PedroM Guest

    Default RE: Referencing an open connection on a component

    A) The code would be:<BR><BR>- INCLUDE&#039d document fragment (the connection is only opened if necessary):<BR><BR> (...)<BR> Const DB_CONNECTIONSTRING = "Provider=SQLOLEDB.1; Data Source=&#060;server&#062;; Initial Catalog=Northwind; User ID=; Password=; persist security info=true"<BR> <BR> Set DBConn = Server.CreateObject("ADODB.Connection")<BR> DBConn.CommandTimeout = 30<BR> DBConn.ConnectionTimeout = 20<BR> DBConn.Open DB_CONNECTIONSTRING<BR> (...)<BR> <BR>- the test file<BR><BR> (...)<BR> set objCN2 = Server.CreateObject("BuggyComponent.DB")<BR><BR> strSQL = "SELECT * FROM customers"<BR> aCustomers = BuggyComponent.GetArray(strSQL, DBConn)<BR> (...)<BR> <BR>- the GetArray Function inside the BuggyComponent<BR><BR> Public Function GetArray(strRSSource, ByVal dbConn As ADODB.Connection) As Variant<BR><BR> (...)<BR> Set objRS = CreateObject("ADODB.RecordSet")<BR> <BR> With objRS<BR> .Source = strRSSource<BR> .CursorType = adOpenForwardOnly<BR> .CursorLocation = adUseClient<BR> .ActiveConnection = dbConn &#060;---------- Code stops here<BR> .Open<BR> End With<BR> (...)<BR> <BR>B) My config is WinNT 4.0/SP6a with MDAC 2.5<BR><BR>On another reply, Duncan King actually pointed out that creating and releasing the connection is probably more effective. I&#039ve read some articles on this matter, mostly on MS&#039s site, and there seems to be a clear tendency towards creating late and releasing early. I just don&#039t know if this applies on this situation - I mean, sure, if I&#039m connecting to the DB to get *a* recordset, creating the connection right before and tearing it down right aftwards makes perfect sense to me, but on my case it&#039s more like 4 to 6 recordsets per page. How well does connection pooling work ? Is it really better than just keeping it open ? Have you ever felt any difference on the performance ?<BR><BR>Cheers,<BR>PedroM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts