Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Jun 2002
    Location
    Gulf
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Recordset in a loop

    Hi everybody,

    I would like to open a recordset inside a loop like this:

    while not rs1.eof

    sqlstmt = "select * from tbl_name where id = '" & rs1("needed_field") & "'

    rs1.movenext
    wend
    ------------------------------
    Now the problem that it always says "Operation is not allowed when the object is open".

    I remember that in Visual basic 6 i can open the recordset and add this code "rs1.ActiveConnection = Nothing" in order to do the same thing but when i tried it I got some new error "Operation is not allowed when the object is CLOSED"

    So please can any one help me in this
    =======================
    =======================
    WebMaster
    =======================
    =======================

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    what are you trying to do, are you building a new query, based on information in the current recordset? This should work. If you try to re-open rs1with another sql statement while looping throug the current rs1, you should try creating another temporary recordsetobject which you use to get addidtional information from the database while looping through rs1,
    like:
    while not rs1.eof

    sqlstmt = "select * from tbl_name where id = '" & rs1("needed_field") & "'
    rstemp.open sqlstmt, connobject, etc

    do the stuff you wanna doe with this info

    rstemp.close

    rs1.movenext
    wend

    perhaps, you can get all the info you want in the first recordset, using JOIN to connect the tables together, based on the criteria you provide
    I am the luckiest man in the world

  • #3
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Roelf is totally correct, your SQL statement won't work because you are probably redefining the recordset object using the same variable, "rs1". When it tries to loop again, it can't because you have reassigned the rs1 object. To fix this you can create another temporary recordset within the loop using another variable as Roelf demonstrated above - however this is very inefficient and should be avoided if possible.

    What does your first SQL statement look like? The latter suggestion by Roelf is definitely the best... I would use a JOIN if you can.

    By looping and creating additional recordset objects, you are slowing down the server and your application.
    Last edited by whammy; 01-29-2003 at 01:55 AM.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •