Hmm, I may not be right on this one, I just double checked using ADO 2.5 and
it complains when you do this with a closed connection, however I'm certain
in the past this has caused me problems with ASP pages and a previous
version of ADO (possibly 1.5?). I could be wrong or later versions of ADO
might have fixed it...

> -----Original Message-----
> From: Tony [mailto:one_dev@h...]
> Sent: 28 June 2001 20:14
> To: professional vb
> Subject: [pro_vb] Re: Performance - Calling StoredProc with ADO in Cla
> ss
> 
> 
> Steve, thanks for all the input.
> 
> Something you wrote caught my eye...  "If you don't use SET
> cmd.ActiveConnection = conn  you'll get 2 connections."  I 
> understand that
> ConnectionString is the default property of the Connection 
> Object, but if
> you do use SET and then look at the value of 
> cmd.ActiveConnection, it still
> shows the acutal connection string and not the connection object.
> 
> Is it still creating 2 connections?
> 
> 
> 
> 
> "Steve Wake" <Steve.Wake@S...> wrote in message 
> news:78307@p..._vb...
> >
> > Another good thing to do is to use the 'GetRows' command to 
> ensure all
> rows
> > are in the client before you disconnect it:
> >
> > Set rs = new ADOR.Recordset
> > Set cmd = new ADODB.Command
> >
> > cmd.CommandType = adCmdStoredProc
> > cmd.CommandText = "up_Subscriber"
> > Set cmd.ActiveConnection = m_cnn
> > <do parameter setting stuff if necessary>
> >
> > rs.CursorLocation = adUseClient
> > rs.LockType = asLockReadOnly
> > rs.CursorType = adOpenReadOnly
> >
> > rs.Open cmd
> > if rs.State = adStateOpen then
> > if not (rs.EOF and rs.BOF) then
> > rs.GetRows -1
> > rs.MoveFirst
> > nNumRecords = rs.RecordCount
> > end if
> > end if
> > Set cmd.ActiveConnection = nothing
> > Set cmd = nothing
> >
> > if rs.State = adStateOpen then
> > Do while not rs.EOF
> > <do something with the data>
> > rs.MoveNext
> > Loop
> > rs.Close
> > end if
> > Set rs = nothing
> >
> > Noting that:
> >
> > If you don't use 'set' when you're assigning a connection to a
> > cmd.ActiveConnection the default properties for the 
> connection object
> (i.e.
> > the 'ConnectionString' property) will be used instead and 
> ADO will go and
> > create a brand spanking new connection for you when you try 
> to hit the
> db -
> > so you end up with two connections!
> >
> > adCmdStoredProc and cmd generally gives better performance 
> (esp. with
> older
> > versions of sql) rather than using direct SQL statements - 
> it does mean
> you
> > have to put alot of functionality into stored procedures, 
> however this
> also
> > gives you a cleaner, more manageable (in terms of 
> security), and more
> > supportable in the event of db schema changes, new 
> requirements etc. When
> > you're setting parameters cmd.Parameters.Refresh looks like 
> a good option
> > but it does involve a substantial database hit retrieving 
> the parameter
> info
> > from the database so using cmd.Parameters.Append is 
> probably a better
> option
> > in production/release code.
> >
> > The adOpenReadOnly and adLockReadOnly are used if your 
> doing a 'retrieve'
> > type operation without wanting to modify the data - it gives you a
> > 'firehose' style cursor without locking records. If you 
> want to do updates
> > then you'll need to use something like adOpenStatic and
> adLockBatchOptimitic
> > IIRC - the particular combinations here are very important 
> in determining
> > wether you really do get a disconnected recordset.
> >
> > GetRows makes sure all rows are downloaded to the client 
> and then setting
> > the cmd.ActiveConnection = nothing I think is where the 
> connection is
> > returned into the connection pool, rather than when the 
> connection object
> is
> > instantiated or destroyed. Sometimes I get errors if I 
> don't do this and
> > attempt a movenext as the rs no longer has a connection so 
> can't fetch
> more
> > records from the db. It also allows you to do 
> 'rs.RecordCount' so you can
> > display summary info, say, before the results on an ASP 
> page, without
> having
> > to iterate through or maintain a counter etc and then 
> jiggle the output
> XML.
> >
> > Checking for adStateOpen, EOF and BOF just to prevent 
> errors if the thing
> > didn't open! Ages ago MS recommended to check for both 
> rather than just
> EOF
> > - I'm not sure why though.
> >
> > I normally encapsulate the gumpf into a 'CreateRecordset(cmd as
> > ADODB.Command, nNumRecords) as ADOR.Recordset' type 
> function which does
> all
> > the locktype setting etc and gives me either a nice ready 
> to use rs or
> > nothing back, so you can just testif the result is nothing 
> to know that
> > something went wrong otherwise you have some data to play with.
> >
> > Try to use the ADOR object if your handing recordsets 
> around - it's alot
> > lighter weight than the ADODB object.
> >
> > Sorry - I was getting a bit bored here so decided to have a 
> rant. I'll
> rant
> > somewhere else now... :-)
> > Steve