2nd recordset doesn't work

I wasn't sure which board to put this on, so I'm putting it here...
I've got sequel server and regular old asp running under IIS on NT.

What I was doing, and worked, was returning a recordset from the database with a where clause provided by the user, that recordset would then be used to display the current set of rows the user is viewing (50 at a time). The problem was that this was slow, if the recordset had 6000 rows returned then the information from all 20 or so fields was loaded into memory for the 6k records. So, my plan is to fix this by making the frist recordset load only the record IDs and then create a where clause that will return only the 50 records to be displayed out of the entire query results. After that I close the first recordset and open up a second one, with all the fields but of only the 50 records. Much faster and better.

I've got no problem with how to do that, infact it's all done. BUT - somewhat randomly, but very very often the second recordset will not return any records. I've even tried using the exact same sql used to open the first recordset, but still I get nothing. What the heck is happening here? Closing and setting the objects to nothing and then reestablishing them doesn't help, neither does using entirely different variables.

Set con = Server.CreateObject("ADODB.Connection")
Set rst = Server.CreateObject("ADODB.Recordset")

con.Open "CustomerDB"
SQL = "SELECT Customers.RecordID FROM Customers WHERE Customers.FirstName Like '%" & Request("FirstName") & "%' ORDER BY Customers.FirstName"
rst.Open SQL, con, 3
...
Code to obtain the recordID of the 50 records I want. Note, I make use of .Move, .MoveNext, .MovePrev, .MoveLast, and .MoveFirst.
...
rst.Close
...
Create new sql string - or just leave it the same. Doesn't matter, I still get the empty recordset.
...
rst.Open SQL, 3


Brewguru99

Have you tried creating a second recordset, or are you simply using a single recordset?

Try creating a second recordset

Set con = Server.CreateObject("ADODB.Connection")
Set rst = Server.CreateObject("ADODB.Recordset")
Set rst = Server.CreateObject("ADODB.Recordset")

con.Open "CustomerDB"
SQL = "SELECT Customers.RecordID FROM Customers WHERE Customers.FirstName Like '%" & Request("FirstName") & "%' ORDER BY Customers.FirstName"
rst.Open SQL, con, 3
...
Code to obtain the recordID of the 50 records I want. Note, I make use of .Move, .MoveNext, .MovePrev, .MoveLast, and .MoveFirst.
...
rst.Close
...
Create new sql string.
...
rst2.Open SQL, 3

Or, if you really want to use the same name

Set rst = nothing

then re-create the recordset. You should be able to have both recorsdests open over the same connection.