Performance and ease of use are top application development goals. Sometimes these two goals are mutually exclusive, but they don't need to be. In this issue, I show you how to solve an ease-of-use and performance problem with one simple Active Server Pages (ASP) page.
This article's sample application retrieves and displays Contact table data in an intranet application for our training company's sales and management teams. The application's ease-of-use goals are threefold: to display a contact list from the Contact table in SQL Server, to help users find contacts quickly, and to let users manipulate the contact data by sorting it different ways. The application also needs to perform well by displaying the results quickly. ASP can meet these goals easily because it lets you create flexible pages in script. I use VBScript because of its ease of use and its prevalence in Web applications. You can easily benefit users by using script to build flexible features such as sorting into your applications.
Listing 1 shows the T-SQL code that creates the Contact table. Web Listing 1 shows database.asp, which contains the ADO and ASP code you need to access the database that contains the Contact table. (For download instructions, see the More on the Web box, page 62.) I don't show database.asp here because it's very similar to adohelper.asp, which I wrote about in "Standardizing Data Access with ADO and ASP Scripts," http://www.sqlmag.com, InstantDoc ID 15986.
Displaying the Contact Data
The first problem our company needed to solve was how to display the contact data. We didn't want to list all the contacts when a user visited the intranet page because the Contact table is large and growing rapidly. Displaying all of a page's data drains SQL Server and pushes a large volume of data across the network, slowing down the page and delaying users as they try to sort and find the information they need.
So, we filtered the data by limiting the initial data display to contacts whose last names started with A. The filtering process gives users three options: selecting a displayed last name beginning with A, selecting other last names by their first letter, or selecting All Contacts. Figure 1, page 62, shows the page's interface, which is clean and works like the interface for the Contacts table in Microsoft Outlook. Users can sort the display by clicking any column header in the table, and in Edit mode, they can open a contact by clicking it.
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.