From the June 2001 issue of MSDN Magazine.

onotony dulls the brain. It annoys me to no end to repeat a coding process when I know that I could make the code reusable. For example, Web sites have a need to show search results, report data, and display other lists. Wouldn't it be great if you could simplify the development of all of these pages' In this column I'll discuss one technique that allows you to rapidly create several data display Web pages using ASP, Visual Basic®, COM+, and JScript®. So pull up a chair, kick back, and let's go.
      In the December 1998 issue of Microsoft Internet Developer, Charles Caison and I wrote about a technique that displays any SQL data set on a Web page. Readers have been asking me to update the code to use more modern techniques that fit well into a scalable enterprise Web application. (Yes, I was listening.) So in response to those readers, I'll use my first column in MSDN® Magazine to show you how to implement a generic Web display tool that allows sorting, paging, and a few new features all running under COM+ or Microsoft Transaction Services (MTS).
      Let's start out by taking a look at the finished results. Then I'll explain the code that accomplishes this feat. The complete source code is included at the link at the top of this article, so feel free to download it and give it a whirl. The code offers a cross-browser solution; it works in the most recent versions of both Internet Explorer and Netscape Navigator.

The Page

      Figure 1 shows a sample of a flexible Web page that uses ADO paging and sorting features as well as a plethora of JScript and DHTML techniques.


Figure 1 Data Display

Notice that the Web page only shows five records on each page. Users can navigate to other pages using the Previous and Next links or by jumping directly to a specific page using the combobox in the upper-right corner of the page. By using paging, the Web page can show a user-defined number of records at a time, and allow the user to move forward and backward to different pages of records. For example, if the user clicks on the Next link on the Web page in Figure 1, the browser would display the second page of data, as shown in Figure 2.


Figure 2 Second Page of Data

      Figure 1 shows the first five authors on the report while Figure 2 shows the next group of five authors. Users can also select a specific page number from the "Go to page" combobox to display the data for that particular page, as shown in Figure 3.


Figure 3 Selecting a Specific Page

      The number of records to be displayed is a user-configurable setting. Users can set the number of rows to be displayed on each page using the Records per page textbox. By changing this value and clicking the Refresh link in the upper-left corner, the Web page displays the number of records that the user entered. The Web page remembers the page number the user was on, too. For example, let's assume a user is on the second page of a 25-record report that displays five records per page. Then the user changes the page size from five to seven records per page and clicks Refresh.


Figure 4 Records Per Page

As shown in Figure 4, the code reconfigures the page size to display seven records at a time (with a total of four pages), displaying the second page. Alternatively, if the user is on the last page (let's say page five of five) and increases the records per page so that there is no longer a page five, the last available page displays. But you, the developer, can change all of that if it suits your needs.

Sorting the Data

      In addition to the ADO paging features that are inherent to ADO and available in this application, you can easily sort the data in ascending or descending order. The ability to sort is a common user request. Joe wants the sales report sorted by product, Cal wants it sorted by order date, and Karen wants it sorted by sales region. What a mess! In situations like these, this tool comes in handy since it accommodates a variety of sorting needs.


Figure 5 Data Sorted by Last Name

      The sorting capabilities of ADO allow the user to sort the data in ascending order when the user clicks the column heading hyperlink. To sort the data in descending order, the user clicks the same column heading again and the sort order reverses itself. Figure 5 shows the author data sorted in descending order by last name.

Row-based Linking

      If you need to edit the data, you can simply click on the Edit button to go to another page where you can edit your data. This tool has the capability to specify several links from each row of data that can go to an editing screen, a details screen, a form to send an e-mail message, or any other action related to the row of data. Figure 6 shows that if you click the Edit button of the Akiko Yokomoto record, a message appears displaying the record's primary key value. In other words, you can grab the primary key of the row and pass it to a new window where you could display or edit the record, send an e-mail regarding the record's contents, or any of several other tasks.


Figure 6 Getting the Primary Key

      Keep in mind that all of these features are customizable. Now that you've seen what you can do with this tool, let's take a look at how it works.

Behind the Scenes

      The code is architected using the n-tier architecture. I have a database (I used the Pubs database that's included in Microsoft SQL Server™ 7.0 and SQL Server 2000), business objects (Visual Basic-based ActiveX® server running under MTS), and ASP and client-side code. Figure 7 shows this architecture. The ASP code, HTML, and script code actually live in the same Web page. However, ASP pages have traditionally sat on the logical border between business services and presentation services. (ASP.NET will change that aspect of ASP, but that is a topic for another discussion.)


Figure 7 N-tier Architecture

ASP and Client-side Code

      Starting at the top (ASP/HTML), let's take a look at the main players in this production. This project consists of View_Authors.asp, GenericDisplayToolPart1of3.asp, GenericDisplayToolPart2of3.asp, GenericDisplayToolPart3of3.asp, and Styles.css. The View_Authors.asp page is the Web page where users type in the URL to run the tool. This is the only page in the tool that requires any customization. The other pages are completely generic and can be reused for as many data sets as you need to display. So what do you need to do to customize in this page' You'll need to specify the title of the Web tool, the images to display in each row, and any onclick events for the images. Then you'll need to write the data retrieval code from your COM object.
      Figure 8 shows the basic customization that you'll need to set up the title and the images. You can set the title and the images as you see fit. When the primary image is clicked by the user, the client-side Edit function will be called. And likewise, the client-side Browse function will be called when the secondary image is clicked. You need to write these functions yourself and put them in the page. Figure 9 shows some sample functions that I wrote to demonstrate this technique. It displays a message to the user showing the primary key of the record they selected. The primary key value is automatically set by the generic pages, which you'll soon see in further detail. You simply replace the code I wrote in the Edit and Browser functions with whatever you want them to do. Or, you could call any client-side function you like by changing the onclick code shown in Figure 8.
      The final aspect that you need to customize is the call to your business object to retrieve the data.

  <%
'————————————————————————————
'— Data Retrieval section
'————————————————————————————
Set oAuthors = Server.CreateObject("WebDisplayTool.QAuthor")
vRSArray = oAuthors.GetRecords(sUserID, sPassword, _
                               lPageSize, lCurrentPage, vSortOrder)
Set oAuthors = nothing
'————————————————————————————
%>
  

      As you can see in this code, the ActiveX server is invoked and it passes back a special array containing the data and some metadata. The metadata contains information such as the name of the fields, the captions you want to display for them, the fields that are primary keys, and the fields that should be visible in the display tool. (I'll take a closer look at this data in the next section.)
      That's it for customizing the ASP code. The next step is to create the ActiveX server that will return this data to the ASP page for viewing in the browser.

Creating the ActiveX Server

      For this tool, I wrote an ActiveX server using Visual Basic 6.0. The server, WebDisplayTool, contains one class (QAuthor) and one standard module (basCommon). The QAuthor class contains one public method called GetRecords, which is called from the ASP code to retrieve the author data. GetRecords starts out by opening a connection to the Pubs database (shown in Figure 10) and executing the prAPP_Get_Authors stored procedure, which simply get all of the authors from the author's table.
      Looking at Figure 11 you can see that once the ADO Recordset is full of data, the data is sorted in the order as specified in the vSortOrder parameter that was passed in to the GetRecords method. This parameter is an array that stores the columns to sort by and the sort direction (ascending or descending). Establishing the paging requires moving the current row cursor of the Recordset to the appropriate record. For example, if the user wants to move to the second page and five records are being displayed per page, move the cursor to record six (the first record on page two). Then, call GetRows to get the next five records. This is how the tool handles the paging and sorting.

The Metadata

      The GetRows method returns the data in a two-dimensional array. I take that array and stick it in the element with the index of 0 in another array called vRs. Why' Because this vRs array is going to contain not only the data, but also the field metadata and the recordset metadata. The default field-level metadata can be retrieved via the call to the GetFieldMetaData function (as shown in Figure 12).
      This function creates a one-dimensional array with as many elements as there are fields. Each array element contains another array. This nested array stores the name, caption, visibility, and primary key status of each field. The ASP tool uses this metadata to specify the columns to display, the captions to use, and the fields that constitute the primary key. You can override these default settings if you need to by setting them yourself (see Figure 12). This is not necessary, but it can provide greater flexibility. Finally, I put the field-level metadata array into the first index element of the vRs array.


Figure 14 vRs Array

      The recordset-level metadata goes into the second index element of the vRs array. As you can see in Figure 13, this is a one-dimensional array that contains values for the total number of records, the total number of pages, and the current page number. Once these values are set, the vRs array can be returned to the ASP client. Figure 14 shows how the vRs array looks.

The Generic ASP Pages

      Now that I have analyzed the ActiveX server, it is time to compile it and place it in a package inside of MTS. Once this is done, you are all set to run the code. But first, let's discuss the generic ASP pages that read the data and format the Web page. These Web pages are broken into three parts. Each file is included in the main file (View_Authors.asp, in this case).
      The first page to be included in the View_Authors.asp page is GenericDisplayToolPage1of3.asp. This page is responsible for defining the constants and variables that the generic tool will use. This page also determines how the page will be sorted and paged and then stores that information in local ASP variables that will later be passed into the ActiveX server. After this file is included, the View_Authors.asp page calls the ActiveX server to retrieve the custom data array.
      The second page, GenericDisplayToolPage2of3.asp, is then included. It is responsible for grabbing the custom data out of the custom data array (see Figure 15). The code in this page gets the data array, field metadata, and recordset metadata. Then, for readability purposes, I set the recordset-level metadata to some local variables, as you can see in the code.
      The third and final page to be included in the View_Authors.asp page is GenericDisplayToolPage3of3.asp. This page is responsible for rendering the HTML that displays the data on the page. Throwing in a few custom styles using cascading style sheets spruces this page up a bit.

Conclusion

      As you've seen, this tool offers a quick way to display any set of data on a Web page. It also offers easy-to-use sorting and paging features. By coding an ActiveX server for your business objects and customizing your main viewing pages, you can easily get up and running with this code.
      The are several ways you could modify this code to make it do even more. For example, you could add a filtering frame that would reduce your data set using the user-specified filter. You could also add a find feature that would go to the first record that contains a value the user typed in. I added the ability to jump to another page, passing to it the primary key of the row the user selected. This feature makes it easy to open another window to let the user edit the data. The bottom line is that this code is only the beginning. My hope is that you'll take this code, try it out, modify it, and make it even better.
      I have included the entire source at the link at the top of this article, including the stored procedure, Visual Basic-based code, and Web code. All you need is the SQL Server Pubs database and Microsoft Internet Information Services and you can get up and running with this sample code. Of course, you can customize this code to work with other databases, as well.