Search our sample db by first or last name. (% returns all)

[Since we've got a very small sample DB, try a single letter search like 'a' or 'e' for an example that actually pages!]

We already have both a Database Paging and a Database Search script on the site. Normally we'd leave it to you to figure out how to combine the two, but we've had a number of visitors tell us that while they had very few problems getting either script to run on their own, getting them to work together has proven troublesome.

While the whole point of ASP 101 has always been to give you the tools you need to be able to write scripts on your own, this particular combo seems to causing a number of people problems.

So... here by request is a basic database search form which displays it's results in a table that you can page through.

Alternate Version: Limited Navigation Links

After trying out our paged database search script, one of our visitors decided to send in an alternate version that doesn't display a link to every page, but instead creates a limited set of links so that the display doesn't become unwieldy if your database contains a large number of records.

Here's his email:

John,

You have a great example of the db_paged_search. I would like to share some code that shows the user how to display the first, next, previous 5, next 5, next and last links. This way, if there are multiple pages, the links look clean by displaying only 10 links rather than a link to every page.

I have attached a [modified version of your db_paged_search.asp which replaces the existing navigation code]. My code starts at line [167] and ends at line [261].

One other thing I suggest is to show users how all the records can be displayed upon entering a page before having to search for a specific record. I have included that code as well. It's very simple and I think this would help newbies out. This code is displayed in lines [88-89] and [95-100].

Thanks for you time and consideration,
Jason Wright

You can download a copy of the modified script from here: db_paged_search_limited_links.zip (3.1 KB).

Thanks Jason!

Limited Navigation Links in a Function with CSS

After downloading the limited links version of our paged database search script above, JP decided to send in an improved implementation that wraps the code up into a function and adds some CSS support.

Here's his email:

While looking for some unique paging solutions, I stumbled upon the Alternate Version: Limited Navigation Links addition to your paged database sample by Jason Wright. I thought he had a pretty decent solution and decided to improve on it a bit. I took his code and ripped out all the HTML to ASP to HTML and turned it into a subroutine. Many times you will want to display the paging links at the top and bottom and it would be a waste to calculate the paging more than once. To make the subroutine versatile for any pages on a site, I added a couple of more options including a CSS class to style the selected page, the page filename and query string that will allow the user to insert other parameters like cateogory id, product id, etc.

Please feel free to share this code with ASP 101 visitors if you think it is worthy of submission.

I would also like to mention that ADO paging is not very scalable. We used to use it across our site (heavy multimedia with 50,000+ images across 30 galleries) and with 100+ users the database and web servers could barely take the strain. Now we are using stored procedures and getrows with application scope 2-D arrays and the servers are much happier, plus we are able to open and close the database connection in 5 lines of code or less.

Keep up the good work, between ASP101 and W3Schools, I learned everything I know about ASP.

Best regards
JP Shook

You can download a copy of the function from here: limited_links_function.zip (1.3 KB).

Thanks JP!