The ASP Code
Web Listing 2 shows sqlcontactlist.asp, the page's code. The first few lines of code, which callout A in Web Listing 2 shows, define several variables that the ASP code uses. The include file brings in the database.asp code that you use to access SQL Server. You must either place the include file in the same folder you use for the main file or change the include path to point to the file's location. The functions in database.asp also rely on constants from the ADO type library. You can reference the type library by opening the project that contains these files in Visual InterDev, selecting Project References from the Project menu, then selecting the ActiveX Data Objects library. To make the database functions work, you must change the connection information in GetDSN(), which is in database.asp.
The first two lines of code at callout B in Web Listing 2 set script-level variables that contain the filter and sort criteria for the page. The variable sLastNameSelect contains either the letter a user wants to filter on or the string All. The sSort variable, which contains the sort criteria, sets only when a user clicks a column header to re-sort the display. When a user visits the page for the first time, the sort value is empty.
Two or three variables hold the SQL statement that extracts the contacts. The sSQL variable includes the base part of the SQL statement:
sSQL = "SELECT * FROM Contact "
I built the SQL statement dynamically by storing the filter in the sWhere variable. I can then either add the ORDER BY clause directly to sSQL or use the default ORDER BY from the sOrderBy variable. The first IF statement in callout B defaults sLastNameSelect to A if sLastNameSelect is blank. The second IF statement instructs the WHERE clause to filter the selection as long as sLastNameSelect isn't All. If sLastNameSelect is set to All, you don't need the WHERE clause, because an empty WHERE clause returns all records. The last statement in callout B sets the default ORDER BY clause that executes when a user accesses the page for the first time.
The code at callout C in Web Listing 2 contains the SELECT statement that sets the ORDER BY clause when a user clicks a column header. If you've already set the WHERE clause, it appears in sSQL when the SELECT statement executes. The SELECT statement's Else clause adds the default ORDER BY clause. The last statement in callout C executes the SQL statement by passing it from database.asp to the RunWithRS() function, which takes a SQL statement as a parameter, executes it, and returns an ADO recordset.
The code at callout D in Web Listing 2 builds the HTML that provides the interface for filtering the page by letter. The interface is a set of anchor tags in one cell of an HTML table. Each tag contains a different query string that sets the sLastNameSelect variable. The code at callout E in Web Listing 2 contains the header for the HTML table that displays the contact data. The column headers are anchor tags that set the sSort variable to the appropriate column name and set the sLastNameSelect variable to the current filter state. To set the sort mode and maintain the current filter in the resulting SQL, you must set both variables. The code at callout F in Web Listing 2 loops through the recordset (rsContacts) that matches the sort criteria, then displays the output in HTML.
Fast and Easy
This ASP page exemplifies an application that is easy to use and performs well, yet is simple to develop. First, the page holds little database code, because you use database.asp to encapsulate database access. Second, filtering the data on the first letter of the last name adds zip to the application and reduces clutter for the user. Third, the sort routine is clean; it simply sets the SQL statement's ORDER BY clause. In addition, you can take the selector lines (the anchor tags) and put them in another page to add the same functionality to that page. Because the HTML URL links are in the anchor tags, with one click, you can access the filter for the initial page that the user sees. Finally, to make the application run even faster, you can easily convert it into one or more stored procedures because you've isolated the SQL code.