BrewGuru99
September 28th, 2000, 04:14 PM
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
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