Multiple Connections

[James Baker]

I've been using ASP.NET for a couple years now and I'm trying to fall back
into ASP for a new position I've acquired...and I'm having a bit of trouble
remembering. I'm running to a problem where the basic logic is as follows:

Query

Do While Not rs.EOF
Query based on value in rs
Another query based on value in rs
Loop

There is no explicit connection object, they all use the same connection
string however. When I limit myself to one query within the Do/Loop, I
don't have any problems. However, when I add another query in there, it
bombs with: Operation is not allowed when the object is open.

I'm assuming it's referring to the connection string being reused, but why
would it allow one additional connection, but not two. I can't really close
this connection before needing to requery. Is there anyway around this, or
am I missing something entirely?

Thanks,
James

 

[Evertjan.]

James Baker wrote on 03 jun 2004 in
microsoft.public.inetserver.asp.general:

Do While Not rs.EOF
Query based on value in rs
Another query based on value in rs
Loop

You will have to increment the rs somwhere in the loop!

There is no explicit connection object, they all use the same
connection string however. When I limit myself to one query within
the Do/Loop, I don't have any problems. However, when I add another
query in there, it bombs with: Operation is not allowed when the
object is open.

I'm assuming it's referring to the connection string being reused, but
why would it allow one additional connection, but not two. I can't
really close this connection before needing to requery. Is there
anyway around this, or am I missing something entirely?

The connnection object is not the same as the query result object

You can make several of the latter.

 

[James Baker]

I'm incrementing the recordset...I figured that was obvious enough that I
could leave it out ;-). The error I'm getting basically a state error,
something about not being able to operate while object is open I believe.
I'll try to find the exact error message.

 

[Evertjan.]

James Baker wrote on 03 jun 2004 in
microsoft.public.inetserver.asp.general:

I'm incrementing the recordset...I figured that was obvious enough that I
could leave it out

It was ment as a joke

;-).
right.

The error I'm getting basically a state error,
something about not being able to operate while object is open I believe.
I'll try to find the exact error message.

[Please quote a significant [info wize] part of the posting.
This is not email, so others will have to remember the thread's subjects.]

A state error?

We are talking ASP ?

 

[James Baker]

No, we're talking VBScript ;-). I was referring to the connection's "state"
as in open/closed.

 

[Bob Barrows [MVP]]

I've been using ASP.NET for a couple years now and I'm trying to fall
back into ASP for a new position I've acquired...and I'm having a bit
of trouble remembering. I'm running to a problem where the basic
logic is as follows:

Query

Do While Not rs.EOF
Query based on value in rs
Another query based on value in rs
Loop

There is no explicit connection object, they all use the same
connection string however. When I limit myself to one query within
the Do/Loop, I don't have any problems. However, when I add another
query in there, it bombs with: Operation is not allowed when the
object is open.

I'm assuming it's referring to the connection string being reused,
but why would it allow one additional connection, but not two. I
can't really close this connection before needing to requery. Is
there anyway around this, or am I missing something entirely?

Thanks,
James

I would use an explicit connection object instead of forcing multiple
connections to be opened through the use of connection strings. Are you
using multiple recordset objects?

Dim cn, rs, rs1, rs2
Set cn=sever.createobject("adodb.connection")
cn.open <your connection string>
set rs=cn.execute(<query1>,,1)
do while not rs.eof
set rs1=cn.execute(<query2 based on rs value>,,1)
'do something with rs1
rs1.close
set rs2=cn.execute(<query2 based on rs value>,,1)
'do something with rs2
rs2.close

rs.movenext
loop
rs.close: set rs=nothing
set rs1=nothing
set rs2=nothing
cn.close: set cn= nothing

HTH,
Bob Barrows

 

[Evertjan.]

James Baker wrote on 03 jun 2004 in
microsoft.public.inetserver.asp.general:

No, we're talking VBScript ;-). I was referring to the connection's
"state" as in open/closed.

If you do not quote, James, the conversation gets to personal and that is
not the way I want to go in this NG.

 

[James Baker]

That might be just the ticket I'm looking for...I'll reply after I've tested
it, but thank you very much. I think I had exactly the backward mentality
on this one.

James

 

[Aaron [SQL Server MVP]]

Do While Not rs.EOF

Query based on value in rs
Another query based on value in rs
Loop

Chances are you could use JOIN(s) here and avoid all this messy nesting of
resultsets. But we can't tell unless you provide more information.