Paging through Recordsets

By Matthew Reynolds

It's an extremely common thing to want to do, so here's ASPWatch's answer to how to page through ADO Recordsets.

What happens usually
Basically, when you call Connection.Execute, ADO will attempt to execute that query in the most efficient way possible. But, like a lot of things, the most efficient is the least functional.

Normally, ADO will attempt to open your new Recordset with a "cursor" which is only capable at moving in one direction, forwards, through the list of rows that your query will return. For efficiency, it also will not attempt to determine the number of rows that could be returned if you were to ask for every one. When you ask the Recordset for its RecordCount, it'll return -1 or "I don't know".

(For those who don't know, a "cursor" is a database term for a software device that keeps track of where you are in a Recordset. Basically, when you say "MoveNext", this moves the cursor down to the next row so you can then fetch the data in that row.)

How to create a more functional Recordset
Creating a more functional Recordset is easy to do, but remember that you want to use the conventional Connection.Execute whereever possible because it's faster and puts less drain on your server resources.

With that in mind, this code will execute the statement and return a "keyset" cursor type:

function Execute(sql)
   set Execute = CreateObject("ADODB.Recordset")
   Execute.Open sql, Connection, adOpenKeyset, adLockOptimistic
end function

The adOpenKeyset and adLockOptimistic declarators are defined in the ADOVBS.INC file that's installed along with Active Server Pages. If you can't find, it just add these lines to your code:

const adOpenForwardOnly = 0 ' this is the default
const adOpenKeyset = 1
const adOpenDynamic = 2
const adOpenStatic = 3

const adLockReadOnly = 1 ' this is the default
const adLockPessimistic = 2
const adLockOptimistic = 3
const adLockBatchOptimistic = 4

How to use it
So, if you run your query with that Execute function, rather than Connection.Execute, you'll get a Recordset that's more functional, but you still need to know how to use it!

The trick is to use Recordset.RecordCount to find out how many records you have. Then, create a page that calls into itself by providing some links to the user that say "View 1 - 10... 11-20, etc.". Pass the start point (1, 11, 21, etc.) through a QueryString variable called "Start" back into the page. When you re-execute the page, use:

     RecordSet.Move CInt(Request("Start"))

This is easier to understand if you can see it in action. Take a look at the source code to see how it's done...

Run the sample: PageAdo.asp

Next: Building an ASP File Manager

Previous: Using ASP to view Site Server Search Catalog Properties

Index: Active Server Pages\IIS

The Author

Matthew Reynolds is a developer, architect and evangelist of Web-based applications running on Microsoft Internet technologies. He lives in Phoenix, Arizona and London, England on no fixed schedule.