This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.
Building a Custom Data Grid: Performing Ad Hoc Web Reporting with a VBScript 5.0 Class Object
A flexible, customizable grid for displaying data is a useful tool for ASP developers. It allows Web visitors to customize their view of your data. This article takes the data grid presented in "Ad Hoc Web Reporting with ADO 2.0" by Johnny Papa and Charles Caison (MIND, December 1998) and adds handy features such as a finds feature that supports multiple finds and a mode for adding and editing records. This version also improves response time by allowing asynchronous record download and it componentizes the code so it can be used as a standalone VBScript class object that can be reused in other pages.
|This article assumes you're familiar with ASP, ADO, HTML, VBScript |
|Level of Difficulty 1 2 3 |
|Code for this article: Datagrid.exe (37KB) |
f you've done much ASP-based programming using databases, you've probably learned that a good data grid for displaying a recordset is absolutely invaluable. The Grid DTC that comes in Visual InterDevÂ® 6.0, in conjunction with the Recordset DTC, is a pretty good tool. However, there are times when I don't like to use this feature regardless of its benefits.
I grew up building ASP from scratch, so I don't like anything that generates lots of code behind the scenes but doesn't tell me exactly what it's doing. If an error occurs in the DTC, it can be difficult to track down the source of the problem. And it can be difficult to customize the DTC to do anything more than it was specifically designed to do. As a result, when I saw the data grid built entirely with ASP, ActiveXÂ® Data Objects (ADO), JScript, and HTML in the article "Ad Hoc Web Reporting with ADO 2.0" by Johnny Papa and Charles Caison in the December 1998 issue of Microsoft Internet Developer
, I eagerly dissected the code so I could understand how it worked. I used the code in my own projects and gradually enhanced and improved it over time. Later, I decided to componentize the code using the new class object capability of VBScript 5.0 to make it even easier to use in my ASP pages.
This article will focus on the enhancements that I made to the wonderful code Papa and Caison wrote. It will also cover the steps I took to convert it to a VBScript class object. To follow along, you will need to review the Papa and Caison article and have a basic understanding of how their code works.
Finding a Better Way
Papa and Caison's code is great. It provides for paging, sorting on any field, user-defined page size, and has other nice features. However, one major feature that was hinted at, but not implemented, was a find featureâ"the ability to search for records containing a particular string in a particular field.
ADO 2.1 provides a very nice find capability that you will find very handy. It can be incorporated into the data grid enhancements. For instance, this statement
objRS.Find "FirstName LIKE 'a%'", 1 , adSearchForward
will find the next record in the objRS recordset, using the current record as a starting point, where the field FirstName begins with the letter a. The 1 indicates that the current record in the recordset will not be included in the search, and adSearchForward forces a forward search. Backward searches can be performed as well. When complete, the recordset pointer will be positioned at the next found record. However, if no matching records were found, the recordset pointer will be negative.
Then it was only necessary for me to add a Find button and a search string input field for each field in the database onto the grid as shown in Figure 1
. I decided to allow the developer to specify which fields will include the find function by setting class parameters. When setting the parameters, the field names on which the find should be implemented must exactly match the field names specified in the SQL statement like so:
myDataGrid.SQL = "SELECT " & _
" '<A href=GetEmpInfo.asp?ID=' + " & _
" LTrim(Str(EmployeeID)) + " & _
" '>' + Str(EmployeeID) + " & _
" '</A>' As [Employee #], " & _
" LastName As [Last Name], " & _
" FirstName As [First Name] " & _
" FROM Employees"
Next, the code shown in Figure 2
had to be added to actually perform the find. This code was placed after the paging section of the original program.
This code will handle multiple finds. That is, the find string will persist, and hitting the Find button again will find the next matching record. This is accomplished by resetting the recordset pointer to the last found record if a find was done, or to the top of the page if a find was not done after displaying a page, as you can see in Figure 3
. This leaves the pointer exactly where it needs to be to find the next record.
As you can see in the following lines of code taken from Figure 2
, Find searches for records where the specified field begins with the input string.
strFind = "[" & objRS(intFindCol).Name & "] LIKE '" & _
Request("find" & intFindCol) & "%'"
However, it would be very easy to search for records containing a string by using a % at the beginning of the search string instead.
Adding and Editing Records
Numerous other enhancements were added to the code to make it more useful and configurable. Two tasks you'll frequently want to perform are adding a record to the list and editing an existing record. To provide this functionality, the grid was modified to understand the concept of a display mode. In this case, the grid can be in either edit mode or view mode. If the grid is called in view mode, it looks and works as it always has. However, if it is called in edit mode, another link will appear above the grid; the user can click on this link to add a record (see Figure 4
). Figure 4 Edit Mode
To facilitate this, another parameter was added to the grid that allows the developer to set the page called when the Add A Record link is clicked. No special Visual Basic code was needed to make a particular column clickable when in edit mode. Instead it was done easily with a SQL statement:
SELECT '<A HREF=GetEmpInfo.asp?ID=' + LTrim(Str(EmployeeID))
+ '>' + Str(EmployeeID) + '</A>' 'Employee #' From Employees
This statement will turn the EmployeeID field in the grid into a hyperlink, which will go to a specified page for processing and pass that page the EmployeeID value that was clicked on through the URL. Note that this command is specific to MicrosoftÂ® Access and Microsoft SQL Serverâ¢, and may be different for other database systems because strings are handled differently across implementations of SQL.
A couple of other parameters were created to facilitate the edit mode. For instance, if the user clicks on the Add A Record link, they will be taken to some other screen that will allow them to input data for the new record. Normally, they would then be taken back to the list to perform an edit or to add another record. Since the recordset has been saved in a Session object, however, returning to the list causes the grid to simply pull up the saved recordset. The new record will not appear because it was not there originally. If you want to have the newly created record appear, you need only add a parameter to the URL the user can click to return to the list. The parameter should cause the recordset, including the new record, to be reloaded from the database, as shown in this statement:
A second parameter that facilitates edits lets the user return to the page that they left after performing an edit record function. Since the user may edit several records in one sitting, you wouldn't want to force them to start at the top of the list after each edit. The code saves the current page into a session variable called Session("PageNum"). To use this, simply pass the page number back on the URL that the user clicks on to return to the list. The following URL is an example of setting the destination page number on return, where xx is the page number to which you want to return:
Improving Response Time
A final enhancement that was made to improve response time was the use of asynchronous fetches. This is a feature of ADO that allows some records to be downloaded to the client while the remaining records are still being pulled from the database. To the client, it appears as if the query has completed, several records have been returned, and they're available to page through. The difference is that as the user pages forward and backward, a new connection is made to the server and more records are passed to the client.
When all records have been returned, paging is nearly instantaneous because pages are cached on the client. Asynchronous fetches can obviously only be used with client-side cursors. However, they can really make it seem to the user as if data retrieval is happening faster. They can page through records and even click on hyperlinked fields without having to wait for all records to download. The downside is that if the records are to be sorted, the command will not complete until all records are downloaded.
The following lines show how this enhancement was implemented. The initial fetch size dictates how many records are sent from the server to the client during each connection.
'â" Use a client side cursor so we can sort later
'â" and so we can create a disconnected recordset.
objRS.CursorLocation = adUseClient
objRS.Source = SQL
objRS.CursorType = adOpenDynamic
objRS.Properties("Initial Fetch Size") = 11
set objRS.ActiveConnection = objConn
'â" Open the report's recordset in asynchronous mode
' so that an initial subset of records can be returned while
' the rest continue to download
Componentizing the Code
The final step in preparing the code to be used as a standalone class object is to make it a little more configurable. To that end, several items were pulled out of the original code and were made into parameters. For instance, your users will probably want to be able to set the grid title and they will definitely need to be able to set the database connection string and recordset SQL command. In addition, code already exists to alternate line colors in the grid to make it easier to read, so the colors used for the alternating lines were made into parameters. These parameters were added to the class by creating Let and Get functions for them so that their values could be set or retrieved. Since their values were previously hardcoded, it was simply a matter of substituting variables for those values in the code. You can see how this was done by looking for the Title, Conn, SQL, RowColor1, and RowColor2 variables in Figure 5
Another parameter that turned out to be extremely useful is the recordset name. The recordset is stored in a session variable so it will persist as the user pages through it, as shown here.
and Request.QueryString("Reload")><"Y" then
'â" Retrieve the disconnected recordset.
set objRS = Session(RSName)
'â" Build the recordset
This session variable was made into a parameter for a couple of reasons. First, a typical application may have several pages with data grids, each displaying a different recordset. If the user is within a single session and wants to return to a page he was on previously, it is advantageous to be able to display that recordset immediately without reloading, particularly for large recordsets. This can be accomplished by giving the recordset for each data grid in your application a different name.
Second, if the creation of a recordset is more complicated than can be encapsulated within a single SQL statement, the recordset can be created prior to its display in the data grid. It can be stored in a session variable, which can then be passed to the class object.
And finally, to assist in componentizing the code, some of the HTML tags (such as <HTML>, <HEAD>, and <BODY>) were removed from the original code so that the data grid could be placed on any existing page.
Converting to a VBScript Class Object
Now that the code is self-contained and a useful set of parameters has been identified and coded, the final step is to convert the code to a VBScript class object. Class objects were introduced in VBScript version 5.0. They let you create a class object that can be used within your ASP pages without the need to register the object. The parameters discussed previously were defined as parameters within the object. For example, the following code defines the SQL parameter for setting the SQL command to use when creating the recordset:
private m_strSQL 'â" The SQL used to generate the report.
'â" Declare and define class properties
Public Property Get SQL
SQL = m_strSQL
Public Property Let SQL(strSQL)
m_strSQL = strSQL
This allows you to reference the SQL property in the code with a statement like this
objRS.Source = SQL
and lets you set the SQL property to an appropriate value before calling the class object.
Next, all of the data grid code was put into a public function called ShowDataGrid. Finally, all of this code was put into a Classï¿½ End Class block and a class object was born.
Using the Class Object
Now that the class object has been created, how do you use it? Figure 5
demonstrates how to set the parameters and call the class object. As you can see, the code looks eerily similar to the way you would call a COM object, but this class object doesn't have to be registered. The downside is that the class object only has access to the same local and session variables as the calling page and values do not persist from call to call unless you use HTML techniques such as hidden fields and session variables.
Wrapping it Up
The complete code for the enhancements discussed in this article can be downloaded from the link at the top of this article. Before running the code, make sure you have installed the latest version of VBScript (version 5.1) on your Web server, available at: http://www.microsoft.com/msdownload/vbscript/scripting.asp
That's all there is to it. Feel free to modify and enhance this code, as I did. Perhaps the next logical step would be to convert this object to a WindowsÂ® Scripting component (leaving it as VBScript) or make a Visual Basic-based COM object with it. For related articles see:
http://www.microsoft.com/mind/1298/ado/ado.htm For background information see:
http://www.asptoday.com/articles/19990701.htm Randall Kindig is a senior consultant for InfoTech Consulting (soon to be renamed Fusion Alliance) specializing in Web development. He lives just outside of Indianapolis, Indiana with his wife, three kids, and pets too numerous to count.
From the July 2000 issue of MSDN Magazine.