Multiple Connections

Discussion in 'ASP General' started by James Baker, Jun 3, 2004.

  1. James Baker

    James Baker Guest

    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
     
    James Baker, Jun 3, 2004
    #1
    1. Advertising

  2. James Baker

    Evertjan. Guest

    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.



    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Jun 3, 2004
    #2
    1. Advertising

  3. James Baker

    James Baker Guest

    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.
     
    James Baker, Jun 3, 2004
    #3
  4. James Baker

    Evertjan. Guest

    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 ?

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Jun 3, 2004
    #4
  5. James Baker

    James Baker Guest

    No, we're talking VBScript ;-). I was referring to the connection's "state"
    as in open/closed.
     
    James Baker, Jun 3, 2004
    #5
  6. James Baker wrote:
    > 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

    --
    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.
     
    Bob Barrows [MVP], Jun 3, 2004
    #6
  7. James Baker

    Evertjan. Guest

    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.


    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Jun 3, 2004
    #7
  8. James Baker

    James Baker Guest

    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]" <> wrote in message
    news:...
    > James Baker wrote:
    > > 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
    >
    > --
    > 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, Jun 3, 2004
    #8
  9. > 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.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)
     
    Aaron [SQL Server MVP], Jun 3, 2004
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ken Varn
    Replies:
    3
    Views:
    424
    Ken Varn
    Jun 11, 2004
  2. Joe
    Replies:
    0
    Views:
    2,341
  3. Replies:
    0
    Views:
    330
  4. Replies:
    10
    Views:
    1,543
    Roedy Green
    Oct 19, 2005
  5. jobs
    Replies:
    2
    Views:
    903
Loading...

Share This Page