he previous two 10-Minute Solutions covered a couple of different techniques to present a recordset of data in the form of logical pages so that the user could navigate from page to page. Instead of continuing with the third article in the series that deals with using the Remote Data Services, let’s take a break and see how you can optimize and enhance the paged recordset techniques you have learned so far. Specifically, you will see how you can improve the data access performance to load your pages faster, as well as how you can incorporate sorting and filtering on the data.

Before you get started, take a look at the previous articles on this topic. The first article, “Display Recordset Data in a Paged Fashion, Part I,” showed how to display data from the database by bringing back a recordset and jumping to the proper location within the recordset before dumping one page full of contents on to the browser. The second article, “Display Recordset Data in a Paged Fashion, Part II,” dumped all the data from the recordset in the form of multiple pages, hiding and displaying each page selectively by using client-side JavaScript manipulation.

While both these techniques work great, especially the second one, they are inefficient. In both the techniques, when a browser requests a page from the Web server, your Web server in turn requests data from the database server. The entire recordset is fetched from the database and sent over the wire to the Web server. On the Web server, your ASP page then crunches through the data and displays the pages it needs and discards the other data. Now, a database server is usually a powerful machine and is quite capable of performing data-intensive operations. Instead of asking for all data to be sent over the wire to the Web browser, it makes more sense to only request the data that is needed so that minimal data is sent across a network. If you reduce the amount of data sent back from the database server, you will get much better performance.

Remember, your goal is to reduce the amount of data sent from the database server to the Web server over the network. There are a few different ways in which you can reduce the amount of data that has to travel. First, you can narrow down the records you wish to retrieve by using a filter criteria. Second, you can request only the specific records you want by using some sort of record counter.

In order to narrow down the records you wish to retrieve, use a WHERE clause in your SQL statement limiting the number of records that are returned. Instead of presenting the entire table of data to your user, have your “paged recordset” Web page appear after the user has gone through the motions of selecting some sort of filter criteria. For example, if you are presenting customer data, you might want to first present a list of states allowing the user to pick one or more, and then use this list within your SQL statement’s WHERE clause. Or you might present data alphabetically and allow the user to pick the alphabet character before bringing back records of customers beginning with the chosen letter?like a phone book or directory.

Requesting only the records you want by record number is tricky. Building a WHERE clause in your SQL statement is fine when you want to choose field value criteria (WHERE STATE = ‘CA’, for example), but you need a WHERE clause to restrict the number of records being returned. It would be nice if you could say “WHERE RECORD NUMBER IS BETWEEN 20 AND 30”. However, relational databases do not have a concept of a “record number.”

Oracle uses a construct called rownum that provides a phantom record number. You can use this construct within your WHERE clause to provide you with the records you want. For example, in Oracle, to obtain the third page of data (records 31 to 40), you would use this SQL statement:

SELECT *FROM   tableXWHERE  rowid in (   SELECT rowid FROM tableX   WHERE rownum <= 40   MINUS   SELECT rowid FROM tableX   WHERE rownum < 31);

The MINUS clause provides an intersection between the two sets of data (all data where rownum < 31 and all data where rownum <= 40) resulting in records 31 to 40 alone.

You can't really do the same in MS SQL Server or Access. MS SQL Server does not have a similar record number construct, and the Microsoft Access record number is an artificially created number shown on your Access form. One of our readers, Mike McKee, provided an alternative approach that would work with MS SQL Server. In SQL Server, you can use a temporary table to store all the records from the target table and create your own row number in the process by using the IDENTITY data type. The IDENTITY data type is similar to the MS Access AutoNumber field and it automatically increments a field value on insertion. Assume that TableX is the table you want and it has TableX_ID as its primary key field. To obtain the records from 31 to 40 that make up page 3, you would use this code:

Select TableX_ID, IDENTITY(int) AS rownum INTO #tmpTableX    FROM TableXGOSelect TableX.* From TableX INNER JOIN #tmpTableX    ON #tmpTableX.TableX_ID = TableX.TableX_ID    WHERE rownum >= 31 AND rownum <= 40GO

As you can see, first you select only the primary key from the table (TableX_ID from TableX) and dump it into a temporary table (#tmpTableX):

Select TableX_ID, IDENTITY(int) AS rownum    INTO #tmpTableX FROM TableX

The pound sign (#) at the beginning of a table name tells SQL Server that it is a temporary table created in cache that is deleted or dropped when your code finishes executing. A temporary table is useful as a scratch pad area for you to manipulate data before returning data back to the calling program. In the first step, you also include a new field called rownum, which is made up of an IDENTITY data type. Because it is an identity field, you do not have to supply any values. If five records were selected because of the first step, they would be placed in the temporary table along with the identity field. The identity field would then have the values 1, 2, 3, 4, and 5, respectively, in each record.

You then join this temporary table back to the main table on the primary key and make a retrieval of only the data you need based on the row number you generated.

Select TableX.* From TableX INNER JOIN #tmpTableX    ON #tmpTableX.TableX_ID = TableX.TableX_ID    WHERE rownum >= 31 AND rownum <= 40

The WHERE clause limits the number of records that are returned back. If you were to use the "Pubs" database that ships with MS SQL Server and wanted to retrieve only five authors from the Author table (number 16 to 20), then you would use this code:

SET NOCCOUNT ONSelect Authors.au_id, IDENTITY(int) as rownum    INTO #tmpAuthors FROM AuthorsGOSELECT #tmpAuthors.rownum, Authors.* FROM #tmpAuthors    INNER JOIN Authors ON #tmpAuthors.au_id = Authors.au_id    WHERE #tmpAuthors.rownum >15 and #tmpAuthors.rownum <= 20GO

This produces the result set:

Rownum	au_id			other fields.16		807-91-665417 998-72-356718 899-46-203519 341-22-178220 274-90-9391

You cannot use this technique in MS Access. However, if you are building Web pages with Access as your back-end database, you do not have a separate database server anyway and will not benefit from this method. Instead, focus on filtering your data to reduce the amount of data retrieved.

Sort Data in Paged Recordsets
Once you present your users with your nifty paged recordsets, they are going to clamor for more functionality. The most frequent request is to be able to sort the data in the grid. The most intuitive way to implement this functionality is to follow what Windows Explorer's right-side pane does. Clicking on the column head should sort the data by that column. Clicking it again should sort it in the opposite direction (ascending or descending).

To provide sorting within your paged recordsets, you need to know what field the user wishes to sort by. Assume it is field number 2 (starting from zero). Once you know the field number, sorting is simple. You can use the ADO recordset's sort method to sort a recordset before outputting its contents within your paged recordset. To sort the field, you can make the column title of each column a link that calls the same page but sends a different number to indicate the field to be sorted by. For example, if your page is called 10MinPagedRecs2A.asp, then you can code Column 3 as follows:

Column 3 Title

When the user clicks on this link, the same page will be called, but this time, you can interrogate the Request object and obtain the value of the sort order field (so), which will equal 2.

<%Dim lngSortField			' The field to sort byDim strSortField			' and its name' -- get Sort Field from Request objectlngSortField = Request("so")' -- If blank, set it to -1: No Sortingif lngSortField = "" Then lngSortField = -1%>

Once you retrieve your recordset, you can check to see if the user has asked for sorting by inspecting the value of the variable lngSortField. If it is zero or greater, then proceed to sort the recordset.

' -- Do we have a sort field?If lngSortField >= 0 Then	' -- what is the field's name	strSortField = objRS.Fields(Cint(lngSortField)).Name	' -- Sort the recordset	objRS.Sort = strSortFieldEnd if

The sort method of the ADO recordset uses a string field name notation. Therefore, you obtain the field name from the Fields collection of the ADO recordset and use it to sort the recordset. This will produce a grid sorted by the third column.

You also need to modify the piece of code that generates the column headings to include your hyperlink. To do so, change the code within the subroutine WriteTableTop that reads:

' -- Then a row of Column HeadingsstrBuff = strBuff & ""For i = 0 to objRS.Fields.Count - 1	strBuff = strBuff & "" & _	objRS.Fields(i).Name & ""NextstrBuff = strBuff & ""

to this code:

' -- Then a row of Column HeadingsstrBuff = strBuff & ""For i = 0 to objRS.Fields.Count - 1	strBuff = strBuff & "" & _	"" & _	objRS.Fields(i).Name & _	""Next

Take a look at this page: 10MinPagedRecs2A.asp. Click on any column to sort by that column. You can even download the code used in the working sample. A caveat: Be careful when using this technique, because you will have trouble if you allow your users to sort a large data field, or a memo (text) field.

Sort Ascending and Descending
The next step is to allow the user to sort the column ascending or descending by clicking on the same column again. To do this, you need to use a new variable, say, Sort Direction (sd), to indicate whether you want ascending sort or descending sort. To provide this functionality, you change your hyperlink so that it passes the new variable, too. This link will inform the ASP page that the user wants to sort in ascending order:

Column 3 Title

While this link will indicate that the user wants to sort in descending order:

Column 3 Title

You can store the sort direction in a variable for later use:

' -- get sort direction from request objectstrSortDirection = Request("sd")

To sort a recordset in ascending or descending order, you can include the optional argument ASC or DESC to the sort field's name when invoking the ADO recordset's sort method. This line will sort the recordset in descending order:

objRS.Sort = "fieldname DESC" 

To handle the cases where your field name can contain spaces (if you are using MS Access), enclose the field name in box brackets ([]):

objRS.Sort = "[fieldname] DESC"

In order to properly sort the recordset, you need to inspect the lngSortField and strSortDirection variables. Based on their values, you generate the "[fieldname] ASC " or "[fieldname] DESC" sort strings:

' -- Do we have a sort field?If lngSortField >= 0 Then	' -- what is the field's name: Enclose in box brackets	strSortField = "[" & objRS.Fields(Cint(lngSortField)).Name & "]"	' -- what is the sort direction	if Trim(strSortDirection) = Trim("0") Then		strSortField = strSortfield & " ASC "		' not really required since ASC is default	Else		strSortField = strSortField & " DESC"	End if	' -- Sort the recordset	objRS.Sort = strSortFieldEnd if

And, finally, you need to modify the code that generates the column head. Within this code, you need to inspect the value of the lngSortField and strSortDirection variables and take action accordingly. If the column matches the field being sorted, you use the strSortDirection variable to decide how to sort it again. If the column does not match the field being sorted, then you default to sort direction ASC (0). Within your WriteTableTop routine, you need to change the code (see Listing 1).

You now have a paged recordset approach that allows users to rapidly navigate between pages and even allows them to sort by clicking on column heads. You can further improve this technique by placing small graphic icons next to the columns being sorted to indicate to the user what column has been used in the sorting, and in what direction. Download the code for 10MinPagedRecs2B.asp here.

Next month's 10-Minute Solution will look at using Remote Data Services to transfer a recordset as-is from the server to the client, and then allow the client to manipulate the recordset. After that, you'll see how to accomplish the same thing by sending an entire recordset to the client as an XML data island allowing the client browser (at this time only Internet Explorer 5) to manipulate the data.