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.]

Re: Multiple Connections

James Baker wrote on 03 jun 2004 in
microsoft.publi c.inetserver.as p.general:
[color=blue]
> Do While Not rs.EOF
> Query based on value in rs
> Another query based on value in rs
> Loop[/color]

You will have to increment the rs somwhere in the loop!
[color=blue]
> 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?[/color]

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

You can make several of the latter.



--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[James Baker]

Re: Multiple Connections

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.]

Re: Multiple Connections

James Baker wrote on 03 jun 2004 in
microsoft.publi c.inetserver.as p.general:[color=blue]
> I'm incrementing the recordset...I figured that was obvious enough that I
> could leave it out[/color]

It was ment as a joke
[color=blue]
> ;-).[/color]

right.
[color=blue]
> 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.[/color]

[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 ?

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[James Baker]

Re: Multiple Connections

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

[Evertjan.]

Re: Multiple Connections

James Baker wrote on 03 jun 2004 in
microsoft.publi c.inetserver.as p.general:[color=blue]
> No, we're talking VBScript ;-). I was referring to the connection's
> "state" as in open/closed.[/color]

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


--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Bob Barrows [MVP]]

Re: Multiple Connections

James Baker wrote:[color=blue]
> 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[/color]

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.create object("adodb.c onnection")
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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[James Baker]

Re: Multiple Connections

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


"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:uajcRTZSEH A.3580@TK2MSFTN GP09.phx.gbl...[color=blue]
> James Baker wrote:[color=green]
> > 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[/color]
>
> 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.create object("adodb.c onnection")
> 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
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>[/color]

[Aaron [SQL Server MVP]]

Re: Multiple Connections

> Do While Not rs.EOF[color=blue]
> Query based on value in rs
> Another query based on value in rs
> Loop[/color]

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.

--

http://www.aspfaq.com/

(Reverse address to reply.)