Ever wonder how the search engines pull off that cool trick of only
showing you the first ten results that your query pulls back from
their gigantic database? Well, rest assured that you're not alone.
It's been a popular request here at ASP 101, and as far as we can tell,
everywhere else as well.
We did some research and there are a couple of ways to accomplish this
particular trick. Since we're doing this for the web, one thing we
decided was that we didn't want to leave any DB objects hanging around.
This decision might hurt performance if you're going to be doing a lot
of paging back and forth, but for us, it seemed to be worth the performace
hit to not have the memory usage from unused DB objects lingering around
in users' session objects.
If you're wondering, the reason we set the page size so small is because
our sample DB is tiny and we figured a single page paging sample wouldn't
illustrate the concept too well! The other thing to notice is that,
besides the include at the top, this was all done in script. No context
switching at all. I don't know if I like it or not, but I never felt the
need to drop out to HTML and heck it's something else for you to notice!
The final thing I'd like you to notice is something everyone seems to
compliain about! Take a look at our HTML source on this one. By my
standards, the table's flawless! Everyone always complains that either
their ASP code looks ugly or the reslting HTML output does. We really
can't allow either one since our code is on public display. I'm showing
you that you can get both to be presentable with a little work and
some careful placement of vbCrLf's and vbTab's!
Once again, this is the same DB as our original
Database sample. If for
some reason you want a copy of the DB you can download it
Take a look through the code. There are some comments, but you'll find
the level of sarcasm is lower than usual. Code, comments, and that's
Alternate Version Using an Array
One of our users has contributed a version of the script that copies the data
to an array before implementing the paging.
I reworked your database paging script to build an array and then
close the connection. This keeps your paging model and reduces the time the SQL
connection is open.
To be honest I'm not sure it'll be any faster (especially with larger data sets)
but I figured I'd go ahead and post it for those of you who are interested.
If anyone wants to speed test them I'll be happy to post the results... I just
don't really have the time at the moment.
Anyway... you can download the code from here (1.5 KB).