Displaying Information in Sequential Pages with Visual InterDev

Mike Pope
Microsoft Corporation

August 1998

Click to copy the sequence sample files associated with this article.

Summary: Shows three methods for retrieving data and displaying it across a series of Web pages. Familiarity with DHTML and scripting will help. (16 printed pages) Discusses:

Contents

Introduction
Designing Paging with the Grid Control
Designing Paging with Design-Time Controls in Client Pages
Designing Paging with Design-Time Controls in Active Server Pages
That's Just the Beginning

Introduction

If you're writing Web pages that display information from a database, you'll probably come across situations where the amount of data to be displayed is simply too much for a single page. If a database query returns hundreds or even thousands of records, you probably don't want to dump them all into a single page and then send it to the user.

One solution is to create pages of data. A familiar example is that of search pages. If you search the Web, the query might return dozens of pages meeting your criteria. Typically, the search site will return these to you in pages of 5 or 10 or 25 hits per page.

Microsoft Visual InterDev 6.0 offers you a variety of ways to create paged listings from a database. The primary differences have to do with how much control you want over the process. Not surprisingly, the more control you want, the more work you need to do yourself. However, in no case is it particularly difficult.

In this article I'll show you how to create paged output in these ways:

Both client-side and server-side DTCs require a bit of scripting. The client page allows you to use DHTML to display the data on the page, which can be relatively simple if you use the inherent data-binding capabilities of DHTML elements, or slightly more complex if you want more precise control of paging. The server page has to use server script to output HTML containing the data.

As an example, let's assume that you want to create a list of employees. I'll just use the Employee table in the standard Microsoft SQL Server™ Pubs database, displaying the employee ID, last name, and first name. For the sake of simplicity, I won't include any search or sort criteria. In reality, however, it doesn't matter how you generate your query; the only thing we're concerned with is what you do with the resulting recordset.

The examples all assume that you know how to work with data connections and design-time controls, especially the Recordset DTC. If you need a review, you can find information in the Visual InterDev documentation set in the following topics.

Table 1. Documentation available on Data Connections and Design-Time Controls

Subject Topic Documentation Path
Data Connections Connecting to a Database Getting Started with Visual InterDev 6.0/Introducing Visual InterDev 6.0/Database Basics
  Data Access Architecture Using Visual InterDev/Integrating Databases
Data Binding Using Visual InterDev/Integrating Databases
Recordset Control Querying the Database Getting Started with Visual InterDev 6.0/Introducing Visual InterDev 6.0/Database Basics
  Recordset Design-Time Control Reference/Design-Time Controls/Design-Time Controls/Recordset Design-Time Control

Designing Paging with the Grid Control

By far the easiest way to create paged output is by using the Grid control. The control comes with built-in support for paging, making the whole process as easy as setting properties for the control. You can use a Grid control for either server-side or client-side paging.

To use a Grid control for paging

  1. Add a Recordset control to the page and set its properties to point to the table or execute the query that you want.

    If you're working with an .asp file, Visual InterDev prompts you to confirm that you want to enable the scripting object model. You do.

    Figure 1. Creating a Recordset control

  2. Drag a Grid design-time control from the Toolbox onto your page.

  3. Right-click the Grid control, choose Properties, and then choose the Data tab. Under Recordset, choose the Recordset control you created in Step 1.

  4. Under Available fields, select the Employee fields you want to see in the Grid.

    Figure 2. Data tab in the Grid Properties window

  5. Choose the Navigation tab, and then under Page navigation, make sure that Enable paging is checked.

    Figure 3. Navigation tab in the Grid Properties window

  6. Choose OK.

That's it. To test, right-click the page and choose View in Browser.

Figure 4. Paged output using the Grid control

The Grid control offers you a large and interesting variety of options for how to format individual rows. I won't go into many details here except for a couple of paging options in the Navigation tab of the Grid Properties window. There you can set:

Designing Paging with Design-Time Controls in Client Pages

A limitation of the Grid control is that it can only display data in a single format: a grid. This might not be what you want. To create a more customized output, you can use design-time controls.

Here's an example of the same data we displayed using the Grid control, but formatted quite differently.

Figure 5. Customized paging using design-time controls

Creating this page requires two types of DTCs. As with the Grid, the data is managed by a Recordset DTC. The only other DTCs are the buttons used to navigate through the recordset.

The data itself is displayed in an HTML table. You can do this a couple of ways. One is to use the inherent data binding capabilities of a table in DHTML. This approach is easy; it's not much more involved than using a Grid DTC. However, as with the Grid control, you have limited control over how you can page the data.

A more involved approach is to take complete control of the paging logic—that is, to design manual paging. The disadvantage, of course, is that you have to write more script. I'll show you one way to do this, just as an example. In fact, once you take control of the data yourself, there really is no limit to how you can display the data.

Displaying Pages in Data-Bound Tables

In DHTML, tables include simple support not only for data binding, but for paging as well. To bind a table to a data source, you include the DATASRC attribute in the <TABLE> tag. To specify a page size—that is, the number of records to show at a time—you set the table's DATAPAGESIZE attribute.

You can easily bind a table to a Recordset DTC. The only trick is to know that when you use a Recordset DTC, its data-binding name includes the suffix _RDS. For example, if your Recordset DTC is called Recordset1, its data-binding name is Recordset1_RDS. Within the table, you can bind individual columns (cells) to database columns by including a <DIV> element in each cell and setting the <DIV> element's DATAFLD attribute.

Here's an example of a table that is bound to Recordset1, shows five records at a time, and displays three columns from the Employee table:

<TABLE ID="Table1" DATASRC="#Recordset1_RDS" DATAPAGESIZE=5>
<THEAD>
     <TH ALIGN="left" WIDTH=150>Emp ID</TH>
     <TH ALIGN="left" WIDTH=200>Last Name</TH>
     <TH ALIGN="left" WIDTH=200>First Name</TH>
</THEAD>
<TR>
     <TD><DIV DATAFLD="emp_id"></DIV></TD>
     <TD><DIV DATAFLD="lname"></DIV></TD>
     <TD><DIV DATAFLD="fname"></DIV></TD>
</TR>
</TABLE>

The only thing you have to add now is navigation. Add a couple of Button DTCs to the page and call them btnPrevious and btnNext. You can then create small scripts that call the table object's previousPage and nextPage methods, as in the following:

<SCRIPT LANGUAGE=VBScript>
Function btnPrevious_onclick()
   Table1.previousPage()
End Function

Function btnNext_onclick()
   Table1.nextPage()
End Function
</SCRIPT>

You're done. Between the Recordset DTC, the data-bound table, and the Button DTCs, all the paging work is done for you.

That's the easy way to do paging in DHTML. You might notice, however, that you are missing a few features. For example, the table object does not support methods that allow you to page to the first or last page. You also do not know at any given point what page you are on.

As an exercise, therefore, I'll show you a more elaborate way to created paged output by doing everything manually. You'll then be equipped to create client-side paging completely automatically (using the Grid DTC), semi-automatically (using a data-bound table), or entirely manually. Or you can mix and match.

Designing Manual Paging

A relatively easy way to design manual paging is to base navigation around a "current page." For example, when the user presses the First button, the button's handler sets the current page to 1, and then calls a display routine. The Next button increments the current page and then calls the display routine, and so on.

The following table outlines the tasks required for this kind of paging, and provides some comments about when to accomplish the tasks.

Table 2. Tasks for Designing Paging

Task Comments
Calculate the total number of pages required for the record set. Do this when the record set is complete, and store the value in a global variable.
Move to the first, previous, next, or last page. Use an individual button DTC for each navigation task. The handler for each button sets the current page appropriately. (The Previous and Next buttons should check that they don't overrun the record set.) The handlers do not perform navigation; they simply calculate a new current page.
Display the data. Determine which record is the first on the current page, and then move to that record. Then extract the records for the current page and display them.

Getting a Record Count and Calculating a Page Count

To get the total number of records in a recordset, get the Recordset object's getCount property. You can then calculate the total page count for the report.

Here's one algorithm, which divides the total record count by the number of records you want to display on each page. If the result doesn't come out evenly, allow for an extra page. In the following, gRecordsPerPage is a global variable containing the number of records per page. The Mod operator checks for a remainder. This calculation can be done in the ondatasetcomplete event handler.

' Global variables
Dim gCurrentPageNumber
Dim gMaxPageNumber
Dim gRecordsPerPage
gRecordsPerPage = 5

Function Recordset1_ondatasetcomplete()
   totalRecordCount = Recordset1.getCount()
   gMaxPageNumber = Int(totalRecordCount / gRecordsPerPage)
   If (totalRecordCount Mod gRecordsPerPage) > 0 then
      gMaxPageNumber = gMaxPageNumber + 1
   End If
End Function

Creating Navigation Buttons

The handlers for each button change the current page by setting the global gCurrentPageNumber variable. They then call a display routine. The four handlers might look like this:

Function btnFirst_onclick()
   ' Returns to page 1
   gCurrentPageNumber = 1
   DisplayData()
End Function

Function btnPrevious_onclick()
   ' If not already at page 1, goes back one page
   if gCurrentPageNumber > 1 Then
      gCurrentPageNumber = gCurrentPageNumber - 1
      DisplayData()
   End If
End Function

Function btnNext_onclick()
   ' If not already at the end of the recordset, goes to next page
   if gCurrentPageNumber < gMaxPageNumber Then
       gCurrentPageNumber = gCurrentPageNumber + 1
       DisplayData()
   End If
End Function

Function btnLast_onclick()
   gCurrentPageNumber = gMaxPageNumber
   DisplayData()
End Function

Navigating, Extracting, and Displaying

The burden of the work occurs when the data is displayed. There are three distinct tasks. The first is to use the current page number to determine what the starting record on the page should be. Here's one formula to do that:

startRecord = ((gCurrentPageNumber-1) * gRecordsPerPage) + 1

The second task is to navigate to the right record, for which you can use the Recordset object's moveAbsolute method. The third task is to extract and display the data. However, the navigation, extraction, and display should be in a loop that repeats as many times as you have records on the page. Here's a slimmed-down version:

Sub DisplayData()
   startRecord = ((gCurrentPageNumber - 1) * gRecordsPerPage) + 1
   For recordPtr = startRecord To (startRecord + gRecordsPerPage - 1)
      ' Move to a specific row in the recordset
      If recordPtr > Recordset.getCount() Then
         ' Stop extracting data
      else
         Recordset1.moveAbsolute(recordPtr)
         ' Otherwise, get the data out of the record set ...
         empID    = Recordset1.fields.getValue("emp_id")
         empLName = Recordset1.fields.getValue("lname")
         empFName = Recordset1.fields.getValue("fname")
         
         ' ... and display it here
         
      End If      rowCtr = rowCtr + 1   Next End Sub

Using DHTML to Display Data

That's all that's required for the actual paging logic. As noted earlier, the display logic is a separate problem, which can be accomplished in different ways.

Note   Remember that for simple scenarios, you can bind a table directly to a data source. See the "Displaying Pages in Data-Bound Tables" of this article.

For my example, I did the following:

Designing Paging with Design-Time Controls in Active Server Pages

Creating paged output using client-side DTCs is not hard, and the results are easy to customize. However, it only works with browsers that support DHTML, such as Internet Explorer 4.0.

You might be designing a Web site that has to be accessible to all sorts of different browsers, whether they support DHTML or not. What then? Good news: you can still use the same DTCs. You just specify that the DTC scripting platform is Server.

Well, that's not the only change. If you use DTCs on your page, the paging logic is similar, but not identical, to the logic created in the client script described earlier. To redesign the client-based paging for an ASP page, you need to think about the following:

There is a performance issue to bear in mind when you design server-side paging. Each time the user navigates, the recordset is regenerated. This regeneration can be very quick, but it can also be somewhat slow, depending on what database you're using and how big the table is that you're querying. The result might be that each time the user clicks a navigation button, there can be a delay before the next set of records appears. You'll definitely want to test performance before releasing the application for production.

For this section, let's assume we're creating a page called Emplist_dtc.asp. The goal will be to have a page similar to the one discussed above, but which will run on many different browsers, not just those that support DHTML.

Maintaining Global Values

The paging logic designed in the client example required some information to be available globally, specifically:

A convenient way to maintain these values it to use page object properties. These properties allow you to treat the values as global, while letting the PageObject DTC do the work of maintaining state information between requests for the page. For information about how to create page object properties, see "Extending the Script Object Model Across Pages" in the Visual InterDev documentation. (In this instance, you're not extending anything across pages; you're just taking advantage of the page object's ability to maintain properties.)

For this example, I dragged a PageObject DTC onto my page and named it emplist. I then opened the PageObject Properties page and in the Properties tab created the following properties:

Figure 6. Creating page object properties

In my example, I set the scope of these properties to Session. Technically, you can set their scope to Page instead, but you might want to test that before relying on it.

When do you initialize these global values? I set two of them in the Recordset object's ondatasetcomplete event handler. When you create page object properties, Visual InterDev provides get and set methods for reading and writing their values, as you'll see in the following example. In a few cases, I use local variables to temporarily hold property values, which reduces the overhead of getting the property values. Here's the code:

Function  Recordset1_ondatasetcomplete()
   ' Sets the page size for global consumption
   recordsPerPage = 5
   empList.setRecordsPerPage(recordsPerPage)
   ' Calculates the last page number based on the total record count
   totalRecordCount = Recordset1.getCount()
   mpn = Int(totalRecordCount / recordsPerPage)
   If (totalRecordCount Mod recordsPerPage) > 0 then
      mpn = mpn + 1
   End If
   empList.setMaxPageNumber(mpn)
End Function

One difference in the server version is that I set the page size (RecordsPerPage property) inside this ondatasetcomplete handler. Although you could initialize the value in other procedures, you need to be sure that you understand the order of execution for server script. In particular, the order in which events are fired in the scripting object model (DTC-based script) dictates when you can access various objects. For details, see the article "Visual InterDev 6.0 Scripting Object Model Event Sequence ."

The last global value, CurrentPageNumber, I could not set in the ondatasetcomplete handler, as explained at the end of the next section.

Navigating

The button click event handlers for the server page use the same logic as on the client page, except that they have to get and set page object properties instead of global variables. The button click handlers therefore look like this:

Function btnFirst_onclick()
   ' Returns to page 1
   empList.setCurrentPageNumber(1)
End Function

Function btnPrevious_onclick()
   ' If not already at page 1, goes back one page
   cpn = empList.getCurrentPageNumber()
   if cpn > 1 Then
      empList.setCurrentPageNumber(cpn - 1)
   End If
End Function

Function btnNext_onclick()
   ' If not already at the end of the recordset, goes to next page
   cpn = empList.getCurrentPageNumber()
   if cpn < empList.getMaxPageNumber() then
        empList.setCurrentPageNumber(cpn + 1)
   End If
End Function

Function btnLast_onclick()
   ' Goes to last page (calculated earlier)
   empList.setCurrentPageNumber( empList.getMaxPageNumber() )
End Function

You also need to make sure that you display the correct records when the page first appears. In the client version, we could wait until the recordset was complete, set the current page to 1, and then jump to the display routine. In the server version, you can't do that; the recordset is reinitialized each time the user navigates, because effectively, the page is being requested again from the server. You therefore can't assume that the display starts at page 1 each time the ondatasetcomplete event fires.

Instead, set the initial page in the onenter handler for the page object. Make sure you test that you're setting this only the first time the page is displayed:

Function empList_onEnter()
   ' The first time the page is entered, sets the current page to 1.
   If empList.firstEntered Then
      empList.setCurrentPageNumber(1)
   End If
End Function

Displaying the Data

There are many, many ways to put data onto a page using server script. I chose to dynamically create rows within a skeleton table. This is similar to how it was done in the client version, except that it can be done using inline server script. Otherwise, there are few differences. I added a test to be sure that the value of the recordPtr variable was not printed out if there were no more records.

The entire display block within the ASP file looks like this:

<HR>
<TABLE BORDER=0>
<TR>
     <TH ALIGN="left" WIDTH=35></TH>
     <TH ALIGN="left" WIDTH=150>Emp ID</TH>
     <TH ALIGN="left" WIDTH=200>Last Name</TH>
     <TH ALIGN="left" WIDTH=200>First Name</TH>
</TR>
<% 
   pageNumber = empList.getCurrentPageNumber()
   recordsPerPage = empList.getRecordsPerPage()
   ' Calculate which record is first on the current page
   startRecord = ((pageNumber - 1) * recordsPerPage) + 1
   lastRecord = Recordset1.getCount()
   For recordPtr = startRecord To (startRecord + recordsPerPage - 1)%>
        <%If Recordset1.EOF = True Then%>
            <TR>
               <TD>&nbsp;</TD>
               <TD>&nbsp;</TD>
               <TD>&nbsp;</TD>
               <TD>&nbsp;</TD>
            </TR>
       <%Else%>
            <%Recordset1.moveAbsolute(recordPtr)%>
            <TR>
               <% If recordPtr <= lastRecord Then %>
                     <TD><%=recordptr%></TD>
                  <%Else%>
                     <TD>&nbsp;</TD>
               <% End If %>
               <TD><%=Recordset1.fields.getValue("emp_id")%></TD>
               <TD><%=Recordset1.fields.getValue("lname")%></TD>
               <TD><%=Recordset1.fields.getValue("fname")%></TD>
            </TR>
      <%End If%>
   <%Next%>
</TABLE>
<HR>

That's Just the Beginning

You've now seen three ways to implement paged output:

To help you see how all this works, the downloadable version of this article includes three Web pages with everything in place, named Emplist_grid.htm, Emplist_dhtml.htm, Emplist_dtc.htm, and Emplist_dtc.asp. Because the pages contain design-time controls, you must add them to a Visual InterDev 6.0 project before you try to run them.

You'll have to change the Recordset controls so they point to your version of the Pubs database (or to some other database entirely), and perhaps change which fields you extract from the database. But the rest of each page should work just fine.

Is there more? You bet. There are all sorts of different ways to implement paging. But no matter what algorithm you use, you'll have the same issues: how to maintain state information between pages and how to display the data.

You might also want to implement paged output that can be updated. That's a topic for another day, but it's another task that is made much easier if you use the design-time controls that come with Visual InterDev 6.0. Good luck!