devxlogo

Display Recordset Data in a Paged Fashion, Part II

Display Recordset Data in a Paged Fashion, Part II

Why Trust Us

his 10-Minute Solution is the second of a series of three articles covering how to display data from a database in the form of logical pages, so that the user can navigate from page to page. The three articles are separated in terms of ease of understanding and use, from the simplest to the most complex. Last month’s solution showed how to display data, hitting the server and database once for each page requested, and will work on any browser. This month’s article covers 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 Communicator 4.0 and above, and Internet Explorer (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. It uses Remote Data Services and will work only on IE 4.0 and above, not on Netscape.

This month, you will learn how to use Dynamic HTML to provide almost instantaneous navigation between pages. The main advantage of this technique is that you need to access your database only once. The main disadvantage of this technique is that, because it uses DHTML, it will only work on version 4 and above browsers, plus the HTML page will get bloated with all the data from the database. Obviously, you cannot use this technique if you are interested in viewing data that is rapidly changing and you need to see up-to-the-minute, accurate data. However, if most of your users have a 4.0 or above browser, and a high percentage of the data you display is not rapidly changing data, then this solution will work fine.

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.

Write HTML Tables One Page at a Time
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 your database. If you were not concerned with “pages”, your code would be very 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 this paged approach different is that instead of just dumping the contents as rows and columns of an HTML table, you make sure to write HTML tables one page at a time. You make the first page visible, and all others invisible using DHTML and Cascading Style Sheet (CSS) properties. Also, a new step involves using client-side JavaScript to handle the navigation from page to page.

Before you proceed, it is important to understand how you are going to set up and navigate through the pages. In the world of DHTML and CSS, you rely on the HTML tags, DIV and SPAN. Both of these are container tags, that is, they can contain other HTML tags and content inside them. Think of them as a rectangle on your HTML page. So if you write code containing multiple DIV tags, they will form multiple rectangles on your page. This code will create two rectangular segments on your HTML page, although you won’t see them as rectangles:

some text
some more text

The DIV tag also corresponds to Netscape’s LAYER tag, so you can use the DIV tag to write cross-browser DHTML code. In the example, you will be placing each page of data from the recordset within a separate DIV container. Each page of data, in turn, will be contained in a separate TABLE with a title, header row and data rows:

      .. code for title row    .. code for headig row    .. code for many data rows  
      .. code for title row    .. code for headig row    .. code for many data rows

In order to uniquely identify these DIV containers, you will call them pg1, pg2, pg3, and so forth. Use the ID attribute of the DIV tag to provide a name by which a DIV tag can be accessed in your client-side script code:

      .. code for title row    .. code for headig row    .. code for many data rows  
      .. code for title row    .. code for headig row    .. code for many data rows

In addition, you can use the STYLE attribute of the DIV tag to position the rectangles one on top of each other at the same location on the screen. First, set the “position” key of the STYLE attribute to “absolute”. You can then set the “left” and “top” key values to position on-screen. To write key value pairs within a STYLE attribute, use the syntax: key:value; key:value; and so forth:

      .. code for title row    .. code for headig row    .. code for many data rows  
      .. code for title row    .. code for headig row    .. code for many data rows

This code will produce two rectangles of data. However, because their positioning has been set to “absolute” with identical “left” and “top” key values, they will be superimposed one on top of the other. Depending on your browser, you will see all the data jumbled up on the screen because they occupy the same position. You have multiple DIV containers, one on top of the other, each occupying the same position on screen, and each containing rows and rows of data. The case is similar to holding up multiple transparent slides one on top of the other and then trying to peer through all of them at the same time.

Set Visibility of DIV Container
However, a DIV container can be made visible or invisible at whim. So, as long as you keep only one DIV container visible and all others invisible, you will be able to read the data from the DIV container that is visible. To make a DIV container visible or invisible, you use the “visibility” key value within the STYLE attribute. Set the “visibility” of the first DIV container to “visible” and the visibility of the other DIV container to be invisible or “hidden”:

      .. code for title row    .. code for headig row    .. code for many data rows

Now, when the browser renders this page, it will only render the visible DIV container. The other container’s data will be in the browser’s memory, but it will not be visible. It will be as if it did not exist.

By making the second DIV container appear and the first DIV container disappear, you have displayed two separate sets of data to the user without making a round-trip to the server. The second page will appear instantaneously and the first page will disappear. In this case, to programmatically manipulate the page display, you will be using four buttons on the page with the captions: First Page, Previous Page, Next Page, and Last Page.

Each button has to make a specified DIV container visible and the currently visible DIV container invisible. So you need a means to track what container is currently visible. To do that, you will use a hidden form variable that you can track programmatically?almost like a hidden text box on the screen. Specifically, each button needs to do the following:

First Page button:Display first page. Hide current page.
Previous Page button:If the current page is already on 1, do nothing. Otherwise, display current page minus one. Hide current page.
Next Page button:If the current page is already on the last page, do nothing. Otherwise, display current cage plus one. Hide current page.
Last Page button:Display last page. Hide current page.

Notice that all four buttons show a specific page. If you have four routines that correspond with the four buttons?ShowFirstPage(), ShowPrevPage(), ShowNextPage(), ShowLastPage()?you can create a separate routine that handles the actual display of the page ShowPage() and each of these button’s routines will call one single routine to do the display. This way, your code is modular and easy to maintain and enhance. Therefore, you need to write five functions in your client-side script code.

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. Next, display all pages of data making the first one visible and the others invisible. Finally, add form controls and client-side JavaScript to handle page navigation.

Now, work with your ASP page that will display all pages of records from the database. If you follow the instructions in last month’s article, you probably have a template for your ASP pages that looks like Listing 1. Save your template as a new ASP page, 10MinPagedRecs2.asp and follow these steps.

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 strConnection

Step 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 code opens a forward-only recordset. There is one issue to be concerned with here. If you open a recordset using this code, once you obtain your recordset, you “walk through,” or process, the recordset. Finally, once you are done with your 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 is a waste of resources. Instead, it is better to 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 if

Step 5: Display all pages of data, making the first one visible and the others invisible.

Assuming that you have 20 rows in a page, you can walk through the recordset, dumping 20 rows at a time. For each page, you need to create a DIV tag with the proper ID and STYLE attributes; the table code; the title row containing the page number being displayed; and the table heading row to describe each column. You can code this in a separate routine called Write TableTop. Next, you need to create data rows for each row within the page. Finally, you need to create the table end code and the DIV end code, which you can place in a separate routine called WriteTableBottom. So for each page, you need to call WriteTableTop, dump 20 rows of data as table rows, and call WriteTablebottom (see Listing 3).

You dump each row from the recordset as a table row. You keep track of the number of rows being dumped using the variable j. As j increases beyond 20, you take a break and output a new page's code. You do this by calling WriteTableTop, which is a routine within the same page. If you need to close a previous page before writing a new page, you call the WriteTableBottom routine in the same page. Finally, you close the last page by calling WriteTableBottom after you have finished with all the data in the recordset.

Code the Page Top and Bottom Routines
The WriteTableTop and WriteTableBottom routines handle the coding of the DIV tags and the TABLE tags (see Listing 4). Notice that both these routines use a technique that improves the performance of your pages. They both need to write out data to the browser. Normally, textbooks will teach you to do this using multiple Response.write statements:

Response.write ""' -- Then a row for the Page NumberResponse.write ""' -- Then a row of Column HeadingsResponse.write ""
However, each Response.write adds to the overhead of your server code accessing the response object and writing to it. It is much faster to write all your code to a variable and then do a single Response.write at the very end, outputting your variable value.
First, you build the DIV tag and supply it a unique ID, as well as figure out what the STYLE attribute's key values should be. If the page being written is the first page, the DIV tag gets an ID of pg1 and its "visibility" is visible. If not, it is invisible.
' -- first, write the DIV containier with a suitable ID valuestrBuff = "
 1 Then  strBuff = strBuff & "hidden;"else  strBuff = strBuff & "visible;"end ifstrBuff = strBuff & """>" 
You then build the TABLE inside the DIV tag:
' -- Then the Table inside the DIVstrBuff = strBuff & "
Page " & iPage & "
"
This code is followed by a row containing the column heads. You take these column heads from the Recordset object that is visible across the whole page, including within this subroutine:
' -- Then a row of Column HeadingsstrBuff = strBuff & "
"For i = 0 to objRS.Fields.Count - 1  strBuff = strBuff & ""NextstrBuff = strBuff & ""
And you finally write the entire code to the response object:
' -- Finally write out the data	Response.Write strBuff
The WriteTableBottom is a similar, simple routine that outputs the TABLE and DIV ending tags.
Step 6: Add form controls to provide page navigation.
At the top of the page, you create the form controls that will manage the navigation between pages. You can design them in various ways. A simple approach is to provide four buttons to move from page to page, in a manner that was made famous by Microsoft Access. In addition, you can also figure out how to let the user jump directly to a specific page.  In the following form code, notice that the command buttons are HTML buttons and not HTML submit controls. If you use submit controls, the form will get submitted. If you use button controls, it will run the code associated with its ONCLICK event when the button is clicked.
      
This code will produce a form that looks like Figure 1. Notice that the form contains a hidden variable called PageNo. You will use this variable to keep track of what page is currently on screen. Clicking each button will fire a JavaScript routine.
Step 7: Add client-side JavaScript to handle page navigation.
If you are not familiar with JavaScript, I would recommend you learn it using one of the many online resources available (http://www.irt.org is one of the best) or using a JavaScript book. JavaScript is a client-side scripting language that is very similar to C.  In JavaScript, you write functions using the "function" statement. These functions have to be enclosed within  tags in your HTML page:
One advantage of using ASP pages is that you can use ASP to dynamically write values within your JavaScript code. In the example, the number of pages you are displaying is a variable value and you can plug this value into the JavaScript code.
The function ShowFirstPage is the easiest to code. All it does is call another function, ShowPage, passing it the number of the page to display, 1:
function ShowFirstPage() {	ShowPage(1);}
Similarly, you can code the ShowLastPage routine. However, this time, you will use your ASP code variable "lngPage", which contains the total number of pages you have outputted, and plug it within your JavaScript code. Here you are dynamically writing values within JavaScript code.
function ShowLastPage() {	ShowPage(<%= lngPage %>);}
If you had outputted five pages, the previous code when viewed in the browser would appear as:
function ShowLastPage() {	ShowPage(5);}
The other two routines need to handle the situation where you fall over the edges of the total number of pages in your output (if you have ever coded Data Access Objects (DAO) move previous and move next buttons by hand, you will find this code very similar):
function ShowPrevPage() {	var v = document.frmData.PageNo.value;	if(v>1)		ShowPage(--v);	else		ShowPage(1);		}
The ShowPrevPage routine first checks the value of the page currently on screen. As long as it is not the first page, it is okay to proceed to the previous page. The variable "v" contains the page number of the page currently on screen.  The statement "--v" lowers the value of v by one. So if the current page was page 4, it would become 3. Otherwise, page 1 is displayed (this line is optional, you can remove it if you want). The opposite is done in the ShowNextPage routine.
The main routine that performs all the work is ShowPage:
function ShowPage(p) {  var oldp = document.frmData.PageNo.value;  if(document.all) {    document.all['pg'+oldp].style.visibility = 'hidden';	document.all['pg'+p].style.visibility = 'visible';      }  if(document.layers) {    document.layers['pg'+oldp].visibility = 'hide';	document.layers['pg'+p].visibility = 'show';      }    document.frmData.PageNo.value = p;}
You need to be careful to write code for the two browsers that support DHTML:  IE and Netscape.  The Document Object Model (DOM) they each use is slightly different.  IE uses the "all" collection to refer to all the elements of the page. Netscape does not have an "all" collection. Netscape uses the "layers" collection to refer to the LAYERS (and DIV) tags within a document. IE does not have a "layers" collection. So, you use two different "if" statements to figure out which browser you are in and take separate action accordingly.  The statement "if(document.all)" will evaluate to an object under IE and will evaluate to "undefined" under Netscape and vice versa for the statement "if(document.layers)".
A second difference between Netscape and IE is the way you access the STYLE attribute of an element. IE uses the syntax "element.style.key=value" while Netscape uses the syntax "element.key = value".  So, if you wish to manipulate the "visibility" key value, you need to either say "element.style.visibility='value'"  for IE or "element.visibility = 'value'" for Netscape.
A third difference is the word used to signify visibility between IE and Netscape. IE uses the words "visible/hidden" to indicate visibility or no visibility. Netscape uses the words "show/hide".  So your ShowPage code needs to branch and use the appropriate code based on the kind of browser being used.
The first thing you do within this page is obtain the value of the current page being displayed from the hidden form variable. You store this value in a local variable called "oldp":
var oldp = document.frmData.PageNo.value;
You then need to display the current page (passed as a number "p" in the function argument), and hide the previous page. You set the value of the hidden form control to the currently displayed page.
For IE, you set the current page to 'visible' and set the previous page to 'hidden':
if(document.all) {  document.all['pg'+p].style.visibility = 'visible';  document.all['pg'+oldp].style.visibility = 'hidden';}
You do the same thing for Netscape, using 'show' and 'hide':
if(document.layers) {  document.layers['pg'+p].visibility = 'show';  document.layers['pg'+oldp].visibility = 'hide';}
Finally, you set the value of the hidden form variable to the current page number:
document.frmData.PageNo.value = p;
To complete the script, you set the value of the hidden form variable to 1 when the page loads, so that you always begin with 1. You are required to set this value in script in order to prevent its value from being perceived as a string variable.
document.frmData.PageNo.value = 1;
When you run the page, you will see page 1. All the other pages are being downloaded in the background, but they are invisible. When you click on the 'Next Page' button, you get to see page 2 instantly (see Figure 2). There is no round-trip to the server and your server is not tied up. To see a working example of this solution, as well as to get hold of a copy of the code here.
 devxblackblue
About Our Editorial Process
At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.
See our full editorial policy.
About Our Journalist
 
 Charlie Frank
 
 Charlie has over a decade of experience in website administration and technology management. As the site admin, he oversees all technical aspects of running a high-traffic online platform, ensuring optimal performance, security, and user experience.
  View Author 
 
   Show More  
 
  
   Linkedin      Twitter   
  
  
©2024 Copyright DevX - All Rights Reserved. Registration or use of this site constitutes acceptance of our Terms of Service and Privacy Policy.
Sitemap — Privacy Policy
" & _    objRS.Fields(i).Name & "