ServerWatch content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
When designing a web site using ASP you have to think about all of the possibilities when it
comes to user interaction. This article will deal with how you display the results from a
search query and why you should always presume the worst.
When designing a web site using ASP you have to think about all of the possibilities when it comes to user interaction. This article will deal with how you display the results from a search query and why you should always presume the worst.
The Problem…
If you have a table where you need to retrieve records based on some search criteria,
in an ideal world your result set would be between 5 and 20 records. These can be
displayed with ease and relativly good speed in a table in the browser window.
However a problem arises when your database becomes quite large and your search
criteria is non-restrictive. What if you get 5000+ records back? You cannot build
a table of 5000+ lines. Even if the script does not time out, the user will be
sitting there for a long time waiting for a result. Even then they have a huge list
which defeats the whole purpose of searching.
Outline of a solution…
As you may have seen on other web sites, a common solution to this problem is to show
a certain number of records at a time and use a Next button and Previous button to
navigate through the results.
So the user will enter the search criteria, and the system will display the first n
results. Clicking Next will display the next n results and clicking Previous will
display the previous n results.
In most other Windows development environments you could use bound data controls or keep
the Recordset/Dynaset in global scope so it is merely a matter of moving around it.
ASP Issues…
Although it may seem inefficient, it will be necessary to re-run the query every
time Next or Previous is clicked. The reason for this is that you are advised
against using Session or Application level data connections. A feature of IIS and
SQL Server is that these data connections can cause the connection manager to queue
the requests – effectively making the entire application single threaded, for want of
a better term. This is not an issue in a single user instance, but if you are
developing for the Web then a single user application should not be considered.
For this reason you will need to keep the current position in the recordset in a
Session variable and move to that record everytime the query is run.
As you can imagine, if records are being added and deleted by other users you will need
to track the number of additions and deletions at an application level. Or you could
accept the fact that the position could be thrown off by a record or two. It depends
on the importance of accuracy and usage of your system.
Download sample code…
The code should be quite self-explanatory. To test it add a table to your SQL database
called Products and have at least two fields, ProductID and Description.
Also have a variable called Session(“scnConnectionString”) that holds your database
connection information.
Download a ZIP file containing the sample ASP page.
