Controlling the Display of Database Queries
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.
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.
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.