asp pagination

[smita]

I hav a asp page displaying 1000 records of a page.
I want that only 50 records should be displyed on one pge,next 50 on second and so on.

[abduraooft]

It's a good idea to start with google first, see http://www.google.com/search?q=asp+pagination

[brazenskies]

this is the mack daddy of all asp pagination scripts...

http://www.moazam.com/2007/03/30/asp-digg-style-pagination-script/

[Old Pedant]

That link that BrazenSkies gives is *BAD CODE*.

DO NOT USE IT!

It does this:

objrs.cursorlocation = 3
...
objrs.open “SELECT * FROM Customers”, objcon,1,2

Because it opens the recordset in adForwardOnly (that's what the 1 there indicates), it has to depend on a *client side cursor* to be able to get the recordcount (which is what that "cursorlocation" line is doing).

And that means the EACH TIME you hit this page, the ADO code has to read *ALL* the records from the DB server into ADO memory. *ALL* the records.

Okay, if you only have 20 or 30 records, maybe that's not terrible. But if you have a few thousand records?? HORRIBLE!

On top of that, for some unknown and really silly reason, the author has chosen to open the recordset for *WRITING*! (That's what the 2 on the Open line means.)

COME ON FOLKS! If you are paging through a table, you are *NOT* writing to the database! So don't *ever* open the DB for writing.

****************

The sad part about this is that fixing the code is easy:

(1) Get rid of the cursorlocation line.
(2) Change the open to simply:

objrs.open “SELECT * FROM Customers”, objcon, 3

The open mode 3 is adOpenStatic, and with a static cursor you *will* get a recordcount without needing a client side cursor.

Exception: A few very old drivers (such as some very old MyODBC drivers) don't handle a static cursor properly. But since MySQL has the LIMIT keyword, you should not use this kind of pagination with MySQL even with an up-to-date driver!