Whats my RecordSet doing?

[Laphan]

Hi All

I'd just like to confirm something with you if you don't mind.

Basically when my ASP page contains 3 or 4 queries, in my own little world I
thought I was conserving resources by reusing the main ADODB objects of
Connection, Command and Recordset rather than continually creating,
dropping, creating, etc An example is as follows:

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Connectionstring = strDSN
oConn.Open

Set oCmd=Server.CreateObject("ADODB.Command")
Set oCmd.ActiveConnection = oConn
oCmd.CommandText = "PostOrder(" & myorderparams & ")"
oCmd.CommandType = 4
oCmd.Execute


oCmd.CommandText = "GetOrderNo()"
oCmd.CommandType = 4

Set oRSv=Server.CreateObject("ADODB.recordset")
oRSv.open oCmd

IF NOT oRSv.EOF THEN
strOrderNo = oRSv("ORDERNO")
END IF

oRSv.close


oCmd.CommandText = "ShowReport()"
oCmd.CommandType = 4

oRSv.open oCmd

IF NOT oRSv.EOF THEN
strOrderNo = oRSv("ORDERNO")
END IF

oRSv.close


Set oRSv=nothing
Set oCmd=nothing
oConn.close
Set oConn=nothing

Now the same 1 Command object seems to be reusable, but am I actually only
using 1 Recordset object or is it that when I 'oRSv.close' and then
'oRSv.open oCmd' I'm actually completely closing and recreating a oRSv
Recordset each time?

I'm not actually setting the oRSv to nothing until the end, but if I don't
use the '.close' option then I can't reuse my oRSv recordset for another
job.

I just want to be resourceful as possible on this aspect and my decision was
that if the above is in fact dropping and re-creating a recordset each time,
I would create my Command and Recordset objects at the beginning of my page
(in an include file), do the required jobs and then have a closing objects
include file at the end of the page.

Thanks

Laphan

[Bob Barrows]

Laphan wrote:

> Hi All
>
> I'd just like to confirm something with you if you don't mind.
>
> Basically when my ASP page contains 3 or 4 queries, in my own little
> world I thought I was conserving resources by reusing the main ADODB
> objects of Connection, Command and Recordset rather than continually
> creating, dropping, creating, etc

What has made you doubt this?

> Now the same 1 Command object seems to be reusable, but am I actually
> only using 1 Recordset object or is it that when I 'oRSv.close' and
> then 'oRSv.open oCmd' I'm actually completely closing and recreating
> a oRSv Recordset each time?

What difference does it make? Yes, you are closing and re-opening it with a
different source.

>
> I'm not actually setting the oRSv to nothing until the end, but if I
> don't use the '.close' option then I can't reuse my oRSv recordset
> for another job.

This is true.

>
> I just want to be resourceful as possible on this aspect and my
> decision was that if the above is in fact dropping

There's no dropping done when the recordset is closed. The recordet's
properties and Fields collection are reset, but you still have a recordset
object in memory.

> and re-creating a
> recordset each time,

No, you are not recreating it when you use .Open. You are resetting its
properties and Fields collection to the new values provided by the new data
source.

> I would create my Command and Recordset objects
> at the beginning of my page (in an include file), do the required
> jobs and then have a closing objects include file at the end of the
> page.
>

As I said in my last reply, this is my preferred way of doing it. I only use
multiple recordset variables when I need to have two recordsets opened
simultaneously, which is rare.

Time to move on. Stop obsessing about this.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Astra]

Thanks for the therapy session Bob.

Much appreciated.

Rgds

Robbie


"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OTTSVPtKEHA.1280@TK2MSFTNGP10.phx.gbl...
Laphan wrote:

> Hi All
>
> I'd just like to confirm something with you if you don't mind.
>
> Basically when my ASP page contains 3 or 4 queries, in my own little
> world I thought I was conserving resources by reusing the main ADODB
> objects of Connection, Command and Recordset rather than continually
> creating, dropping, creating, etc

What has made you doubt this?

> Now the same 1 Command object seems to be reusable, but am I actually
> only using 1 Recordset object or is it that when I 'oRSv.close' and
> then 'oRSv.open oCmd' I'm actually completely closing and recreating
> a oRSv Recordset each time?

What difference does it make? Yes, you are closing and re-opening it with a
different source.

>
> I'm not actually setting the oRSv to nothing until the end, but if I
> don't use the '.close' option then I can't reuse my oRSv recordset
> for another job.

This is true.

>
> I just want to be resourceful as possible on this aspect and my
> decision was that if the above is in fact dropping

There's no dropping done when the recordset is closed. The recordet's
properties and Fields collection are reset, but you still have a recordset
object in memory.

> and re-creating a
> recordset each time,

No, you are not recreating it when you use .Open. You are resetting its
properties and Fields collection to the new values provided by the new data
source.

> I would create my Command and Recordset objects
> at the beginning of my page (in an include file), do the required
> jobs and then have a closing objects include file at the end of the
> page.
>

As I said in my last reply, this is my preferred way of doing it. I only use
multiple recordset variables when I need to have two recordsets opened
simultaneously, which is rare.

Time to move on. Stop obsessing about this.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"