Can I re-use a recordset object?

[Terry]

I read the following somewhere:

'ADO lets you reuse a recordset as long as you close it first'

So, presumeably I can do this:

SQL1 = "...."
SQL2 = "...."
set rs = new adodb.recordset
rs.open SQL1, conn, ....
' do something with the RS recordset here
rs.close

rs.open SQL2, conn, ....
' do something with the RS recordset here
rs.close

set rs = nothing
set conn = nothing

Is it safe to do this?

TIA ... Terry

[Chris Hohmann]

"Terry" <saintkilda@shaw.ca> wrote in message
news:jr0e10t2b5p9mtq3ftq78b9p3fshepsjs4@4ax.com...

> I read the following somewhere:
>
> 'ADO lets you reuse a recordset as long as you close it first'
>
> So, presumeably I can do this:
>
> SQL1 = "...."
> SQL2 = "...."
> set rs = new adodb.recordset
> rs.open SQL1, conn, ....
> ' do something with the RS recordset here
> rs.close
>
> rs.open SQL2, conn, ....
> ' do something with the RS recordset here
> rs.close
>
> set rs = nothing
> set conn = nothing
>
> Is it safe to do this?
>
> TIA ... Terry

Yes

[Terry]

Ok ... thanks ... the reason I ask is that I was getting the following
error, sporadically:
'ADODB.Field error '800a0bcd' Either BOF or EOF is true...
etc.'

I found the Microsoft article 230101 which explains it, but the fix
doesn't apply to me, since I am already at Win2000 with MDAC2.7. But
it was totally intermittent, usually working the first time, and then
randomly after that. I know the query I submitted was good, and
returned data, so the error was misleading. And there was one query
in the vicinity of the code below (where i re-used the recordset), so
thought maybe that might be the reason.

I was able to get it to work consistently by simply moving code that
was in an include file, into the base asp page.... no other changes..
weird.

Thanks again.... Terry


On Tue, 27 Jan 2004 18:15:59 -0800, "Chris Hohmann"
<nospam@thankyou.com> wrote:

>"Terry" <saintkilda@shaw.ca> wrote in message
>news:jr0e10t2b5p9mtq3ftq78b9p3fshepsjs4@4ax.com.. .

>> I read the following somewhere:
>>
>> 'ADO lets you reuse a recordset as long as you close it first'
>>
>> So, presumeably I can do this:
>>
>> SQL1 = "...."
>> SQL2 = "...."
>> set rs = new adodb.recordset
>> rs.open SQL1, conn, ....
>> ' do something with the RS recordset here
>> rs.close
>>
>> rs.open SQL2, conn, ....
>> ' do something with the RS recordset here
>> rs.close
>>
>> set rs = nothing
>> set conn = nothing
>>
>> Is it safe to do this?
>>
>> TIA ... Terry

>
>Yes
>

[Ray at]

While you can do it, you may find it easier for others (and yourself) to
follow your code and know what each recordset is for if you don't. I
personally generally would not do this, unless it were just a "rsMisc" for
grabbing random pieces of data here and there or something, I suppose.

Ray at home

"Terry" <saintkilda@shaw.ca> wrote in message
news:jr0e10t2b5p9mtq3ftq78b9p3fshepsjs4@4ax.com...

> I read the following somewhere:
>
> 'ADO lets you reuse a recordset as long as you close it first'
>
> So, presumeably I can do this:
>
> SQL1 = "...."
> SQL2 = "...."
> set rs = new adodb.recordset
> rs.open SQL1, conn, ....
> ' do something with the RS recordset here
> rs.close
>
> rs.open SQL2, conn, ....
> ' do something with the RS recordset here
> rs.close
>
> set rs = nothing
> set conn = nothing
>
> Is it safe to do this?
>
> TIA ... Terry

[Terry]

Ray,

Thanks... appreciate the feedback. And yes, that is basically what
I'm using it for. A few miscellaneous bits, within the same bit of
code, so thought I could save creating a new object.

Terry


On Wed, 28 Jan 2004 00:27:38 -0500, "Ray at <%=sLocation%>"
<myFirstNameATlane34dotKOMM> wrote:

>While you can do it, you may find it easier for others (and yourself) to
>follow your code and know what each recordset is for if you don't. I
>personally generally would not do this, unless it were just a "rsMisc" for
>grabbing random pieces of data here and there or something, I suppose.
>
>Ray at home
>
>"Terry" <saintkilda@shaw.ca> wrote in message
>news:jr0e10t2b5p9mtq3ftq78b9p3fshepsjs4@4ax.com.. .

>> I read the following somewhere:
>>
>> 'ADO lets you reuse a recordset as long as you close it first'
>>
>> So, presumeably I can do this:
>>
>> SQL1 = "...."
>> SQL2 = "...."
>> set rs = new adodb.recordset
>> rs.open SQL1, conn, ....
>> ' do something with the RS recordset here
>> rs.close
>>
>> rs.open SQL2, conn, ....
>> ' do something with the RS recordset here
>> rs.close
>>
>> set rs = nothing
>> set conn = nothing
>>
>> Is it safe to do this?
>>
>> TIA ... Terry

>