Query in a loop

[Hatch]

How do I query in a loop.
I Have this inside a loop
strSQL3="SELECT * from Rates where Zone='" & Zone & "' and Weight="& Weight
objRS3.Open strSQL3, objConn
response.write objRS3("Rate")

Loop %>

I get an error of
Error Type:
ADODB.Recordset (0x800A0E79)
Operation is not allowed when the object is open.

How do I fix it so I don't get that error.
Thanks

[Roy Sinclair]

strSQL3="SELECT * from Rates where Zone='" & Zone & "' and Weight="& Weight
objRS3.Open strSQL3, objConn
response.write objRS3("Rate")
objRS3.close


You can't put a new record set into a recordset object that already contains a recordset, you have to make sure you close the recordset object to release each recordset before you open a new one using the same recordset object.

To be honest though, you shouldn't do it this way in the first place. Unless this loop is only run a few times at most you're creating a page that can run very slowly because of all the database requests that it'll be triggering. You should find a way to call the database once to get all the records you'll need and then dispense them in the loop.

[whammy]

Yeah... like in the example I just posted...

'open your connection

'put your SQL query here and execute it

Do While NOT rs.EOF
' write out your stuff here
rs.MoveNext 'don't ever forget this, as it will kill your server!
Loop

'close your connection