Limiting Search Results being returned from a Do...While Loop

[Jayson]

Hello!

I currently have an ADODB.Recordset connection with my SQL Server 2000 database, which then runs an SQL Select statements, which retrieves a recordset from the database, i then have a Do...while Loop which loops thru the recordset and outputs the results onto the webpage, the problem is, i could have hundreds and maybe thousands for records which are being retrieved, which slows retrieval alot, and also gives a very long and confusing list of records, is there any way to do the ff:

1. Limit the number of records being output to maybe
50 records/page
2. Have a "Next" button to view retrieve the next set
of records.

I am trying to simulate what we see in a typical Search Engine, you type in a key work, click "Search", it then brings back the first 10 sets of records, then clicking "Next" will retrieve the next 10 sets of records.

Any help would be appreciated!

 

[rac2]

Hello Jayson,

Create a working table, search_results that will hold the complete results of the search. This table will have an extra column to identify the rows, result_id.

The query that performs the search stores rows in the working table instead of in a recordset, they stay on the SQL Server.

Retrieve a recordset with 50 rows at a time, keep track of the number of rows retieved, shown_so_far. Use a query which selects on the result_id column

SELECT * FROM search_results WHERE result_id BETWEEN shown_so_far AND shown_so_far + 50

Increment shown_so_far, display next set of results, repeat.


When the person is finished looking at the results,

DELETE FROM search_results

This is a sketch of an approach that might work.

Good luck,
Richard