popular way to display data from a recordset, especially a large one, is in a paged fashion. For example, when you ask for all the data from a table in your database, you can see about 20 records neatly arranged in a grid fashion, with the ability to go to the next page, previous page, and so on. This 10-Minute Solution is the first of a series of three articles covering how to incorporate this common interface in your pages. The three articles are separated in terms of ease of understanding and use, from the simplest to the most complex. This first article shows how to display data, hitting the server and database once for each page requested. This solution will work on any browser and will give your users the ability to move from page to page and even jump to a distant page by choosing a drop-down menu option and clicking a ‘Go’ button.
The second article in this series will cover how to display data, hitting the server and database only once no matter how many pages are requested. This solution uses DHTML and will work on Netscape 4.0 and above, and IE 4.0 and above. It will also work on AOL if the internal browser supports DHTML. Finally, the last article in the series will cover how to display data, hitting the server and database only once no matter how many pages are requested. This solution uses Remote Data Services and will work only on IE 4.0 and above, not on Netscape.
Before you get down into the code in this article, take a look at my previous 10-Minute Solutions that dealt with displaying data from a database. You will be using the techniques from those articles, so feel free to browse through them before reading on. First, you need to get your database ready. You can access any ODBC-compliant database from your ASP pages using ActiveX Data Objects (ADO). This means you can access a Microsoft Access database or a Microsoft SQL Server database or even an Oracle database. For the sake of this article, I will demonstrate how to access a Microsoft Access database.
Jump Directly to Page in Recordset
Suppose the user has requested to see data from the customer table. The customer table may contain 100 or even thousands of records, and you do not want to present a grid with all 1000 records. So you break up the result into pages of data, each page displaying about 20 or 25 records. You also provide a means for the user to go from page to page and maybe even jump directly to a given page.
For this article, you will focus only on the page that is displaying the results, and not on any page that was used prior to this page to filter or narrow down the criteria for the results. The SQL Select statement will be SELECT * FROM Customers, that is, return all records from the customer table in the database. If you were not concerned with pages, your code would be similar to the one explained in the article, “Display Data from a Database in Your ASP Page.” In this simplistic approach, you would create the SQL statement, open a Connection object, and populate your recordset with data. You would check to see if your recordset has data, then dump the contents as rows and columns of an HTML table, and close the recordset.
What makes the paged approach different is a new step that allows you to jump directly to the page you want in the recordset. To do this, you use a few of the properties of the ADO Recordset object. The ADO recordset can be viewed as made up of separate logical pages of data. By specifying the size of these pages, you can work with an ADO recordset’s pages. To manipulate the size of a recordset’s pages, you specify the page size by using the PageSize property.
Recordset.PageSize = 25The default page size is 10. Once you specify the PageSize property, you can access the PageCount property to determine how many pages of size PageSize are available within the recordset.
Dim lngTotalPageslngTotalPages = Recordset.PageCountTo move to a specific page, you simply use the AbsolutePage property. Setting the AbsolutePage property to a value between 1 and the PageCount property moves the ADO recordset’s pointer to the first record in the requested page. So, if each page had 25 records, and you specified:
Recordset.AbsolutePage = 3Your recordset’s pointer would be on record number 51 (the first record of the page of records from 51 to 75).
Never Move Backwards in ASP Page
Remember, however, that in your ASP page, you will never be moving backwards. Even if the user wishes to move to a previous page, you will still be moving forward (or not moving at all). This is because, unlike a desktop client application, your recordset will not be open and ready all the time. Because the HTTP protocol is by nature a stateless protocol, you will be opening a recordset, dumping the data, and closing the recordset each time a page is requested.
Why do you do that, you ask? Why not open a recordset once, and then place it in a Session variable so that its data is available the next time the user wants it? Isn’t that easier and more efficient? The simple answer is “no.”
Remember, do not place a Recordset object in a Session object with the hopes that you won’t have to hit the database again the next time the user wants to access the database in order to conserve resources. Keeping an object in a Session variable is a horribly expensive operation in terms of resource usage. Not only that, it prevents ASP from creating multiple threads for your user’s session once you place an object in a Session variable. In essence, you cripple your ASP application by forcing it to work in a single threaded fashion. You spend more resources this way than what you hoped to save by not hitting the database again.
You now have all the pieces together, so your code steps are as follows. Create the SQL statement, open a Connection object, and populate your recordset with data. Then check to see if your recordset has data. The new step here is to move to the page you need to display. This time, you dump contents of one PageSize as rows and columns of an HTML table. Finally, close the recordset.
All your page requires now is a mechanism to indicate what page to view. You can use the QueryString variable “p” to indicate what page to display. So sending p=3 means display page 3. You can easily obtain the value of “p” from the QueryString by using the Request object:
<% Dim lngPage lngPage = Request("p")%>Display a Given Page of Records
Now work with your ASP page that will display a given page of records from the database. If you follow the instructions in last month’s article, “Write to a Database Using SQL and ASP,” you probably have a template for your ASP pages that looks like Listing 1. Save your template as a new ASP page, 10MinPagedRecs1a.asp. Then, obtain the data from the Query String right in the beginning under the line of code that reads:
'----------------------------------------------------------------------' All your ASP preprocessing code goes here'----------------------------------------------------------------------At this point, you need to get the data from the query string. To do so, use the Request object.
<% Dim lngPage lngPage = Request("p")%>Step 1: Create the SQL statement
Dim strSQL StrSQL = "Select * from Customers"Step 2: Open a connection
Set objConn = Server.CreateObject("ADODB.Connection") ' -- Connection String Value strConnection = "FILEDSN=Full Path to your FILE DSN" ' -- Open the Connection objConn.Open strConnectionStep 3: Populate the recordset with data
In all the previous examples, you have been opening a recordset using a simple line of code:
set objRS = objConn.Execute (strSQL)This opens a forward-only recordset. However, you will find (as I did from experience) that when you open a recordset in this fashion, you cannot use the PageSize, PageCount, and AbsolutePage properties (at least not against an Access database).
There is one other issue. If you open a recordset using the previous code, once you obtain the recordset, you “walk through” or process the recordset. Finally, once you are done with the recordset, you close the recordset and only then do you close the connection. This means that during the time when you were processing the recordset (it may be a few seconds or up to a few minutes), a connection was open to the database. This practice is not only wasteful but also degrades performance. When you expect to have a lot of users to your site, holding on to a lot of connections like this is a waste of resources. Instead, it is better if you could open the recordset and immediately close the connection. But if you do that, you lose the data in the recordset. So how can you achieve this?
Instead of opening up a standard recordset, this time (and from now on) you will open a disconnected recordset. A disconnected recordset holds its data in an internal cache and releases the connection. You can take all your time in processing the recordset’s data without having an active connection holding up your server resources.
To open a disconnected recordset, first declare your Connection and Recordset objects. You create the Connection object and open the Connection using your Connection string. Next, you create the Recordset object. You set the cursor location for the Recordset object to be a client-side cursor. Open the recordset using a valid SQL statement and the Connection object you opened, making sure it is a forward-only, optimistically-locked recordset. Immediately set the recordset’s Connection property to Nothing, effectively disconnecting it. Finally, close the Connection object, and set its value to Nothing. Now you have a Recordset object filled with data, but with no connection active. Take your time to process the recordset and when you are done, remember to close the recordset and set its value to Nothing.
A few side notes regarding the disconnected recordset. Many programmers are confused about what a client-side cursor is. Remember, the client here is not your end user’s browser client; it is your Web server. The browser has requested an ASP page from your Web server. The ASP page on your Web server is knocking on the doors of SQL Server or Access, demanding data. So, to the database, the client is your Web server. A client-side cursor will be opened on your Web server. In small Web sites, the Web server and database server may be the same machine (your IIS and SQL Server or Access may be on the same machine), but on large sites, you usually have a dedicated database server as a separate machine.
This technique of opening a recordset is so useful that you will use it over and over again (I know I do in my work). So, instead of coding it within the ASP page, you can code it separately as a routine that you can simply drop into any page that requires it. Create a new ASP page, called ADORoutines.asp. You will include this file within your ASP page so that all routines within this page will be accessible from your code. Assuming you call this routine GetRecordset, your ADORoutines.asp page will look like Listing 2.
From now on, whenever you need a recordset, simply use the function within the ADORoutines.asp page. Instead of using this code:
Set objConn = Server.CreateObject("ADODB.Connection") ' -- Connection String Value strConnection = "FILEDSN=Full Path to your FILE DSN" ' -- Open the Connection objConn.Open strConnection set objRS = objConn.Execute (strSQL)You will replace all of the code with one simple line:
Set objRS = GetRecordset(strConnection, strSQL)Before you invoke the routine, remember to INCLUDE the file, ADORoutines.asp, within your ASP page. To do so, place this code in your file. Remember to place it outside the ASP tags <% and %>?otherwise you will get an error.
%><%Step 4: Check to see if your recordset has data
if (objRS.BOF and objRS.EOF) then response.write "No Records found." response.end End ifStep 5: Move to the page you need to display
You already have the value of the page you wish to move to in your variable lngPage. Now set the PageSize property and move to it.
<% objRS.PageSize = 25 ' 25 records per page ' -- Make sure we do not go over the limit If lngPage <= objRS.PageCount Then ' -- Move to the page ObjRS.AbsolutePage = lngPage End if%>Step 6: Dump contents of one PageSize as rows and columns of an HTML table
Instead of moving through the recordset and dumping all the records, use a counter and make sure you are dumping only PageSize (25) records.
<% Dim j j = 0 Do While Not objRS.EOF j = j+1 if j > 25 then exit do . . . other code Loop%>Step 7: Close the recordset
' -- Close objects objRS.Close set objRS = NothingListing 3 contains the entire listing for 10MinPagedRecs1a.asp. This code has been hard coded to show page 3 all the time. You can check out the code here. If you run the page now, you should see something that looks like Figure 1 as the results.
Allow the User to Specify the Page to View
Once you have the file working, you can modify it to add a mechanism for the user to choose which page they want. Simply provide a drop-down menu of all available pages and a 'Go' button. When the 'Go' button is clicked, the same page is displayed showing the page the user requested.
Copy the page 10MinPagedRecs1a.asp to a new file called 10MinPagedRecs1.asp. Then make the following changes. Instead of hard coding the page number, get it from the Request object. If no page number available in the Request object, default to page 1. Also, ensure that the page number is a long integer.
' -- Page number from Request ObjectlngPage = Request("p")' -- default value if none providedif lngPage = "" Then lngPage = 1' -- make sure it is a long integerlngPage = Clng(lngPage)Next, create an HTML form above the output table that contains a text label, a drop-down menu/combo box (SELECT), and a command button. Make sure that the form calls itself and that the SELECT box is called "p", representing the page number to go to.
Notice in the previous code that you are dynamically generating the page numbers within the drop-down combo box. For each page in the recordset, you are generating a separate
Get a copy of the code here. You should get a screen that looks like Figure 2. From the drop-down menu, choose any page and hit the 'Go' button to view that page's contents.
Feedback
I've had many readers write to me regarding this 10-Minute Solution. Many of you found it to be an eye-opener that you can use the ADO AbsolutePage method to directly jump to a page in a recordset, rather than having to slowly do a MoveNext, counting the number of records being processed. However, many readers have also pointed out that although you are displaying only 20 records at a time, the database server still needs to send all records back to the Web server via the recordset. So you are not improving the performance between the Web server and the database server. If you know of a better way, I would love to hear from you. In a future article, I will tackle this issue and show how you can bring back only the records you want from the database server using SQL Server, Oracle, or Access. I must admit that Oracle makes it the easiest with just a few lines of code. For the others, you will need to make some design and query changes in your database.
