SonghaySystem(::)

• Songhay Home
• Privacy Policy
• Send Mail to Us

Using a Generic, Stateless VBScript Object to Display Data in a Tabular Format

In Using Classes with VBScript 5.0, Eric Lippert writes, “Encapsulation and classes are not new concepts, but developers using VBScript have long gone without.” Since the Songhay System technology plan has been tied to Microsoft products since Access 2.0, I can ruefully detail what its ASP-based enterprise has “gone without”:

• An Intra/Internet-based equivalent of the Access Report that is simple, generic, scaleable and portable.
• An Intra/Internet-based “equivalent” of the Access Form view that is simple, generic, scaleable and portable.
• An Intra/Internet-based “equivalent” of the Access Datasheet view that is simple, generic, scaleable and portable.

Anyone with basic ASP skills can easily find a solution to the Access Report and Form problems (if we relax our requirements for a solution that is generic, scaleable and portable). The last problem, however, takes some doing.

The Datasheet Problem

The opinion here is that the first two problems depend on the solution to the Datasheet Problem. Both the Report and Form problems are less generic than the Datasheet problem. In a scenario that is somewhat friendly to a generic solution, the user wants to search for data and get back a search result. The user would like to browse through the search results and edit them if need be. In the terms written here, this means Form-Datasheet-Form: the user searches with a form, gets back a search result in a Datasheet and handles the data in another form.

This implies that everything depends on the solution to the Datasheet problem. The solution must respond to these user demands:

• I want to search for my own data like I search for information on the Internet.
• I want to create, edit and delete my data from a web browser.
• I want my data validated in a web browser like forms in Access.

And the solution must solve these developer problems:

• How can we develop a browser-based Datasheet that is not “hard-coded” to a particular set of data?
• How do we prevent the user from bringing back “too much” data?
• How do we link the search results to an “editing facility” (a form) without hard-coding this link?
• How can we make all of this as reusable as possible so we can use code for this solution in other projects?

All of these demands and problems have been grossly simplified and limited to protect the innocent. However, I think we have enough to go on for quite a few pages of text!

Researching the Datasheet Problem

In Ad Hoc Web Reporting with ADO 2.0, Johnny Papa and Charles Caison developed (way back in 1998) an excellent solution to this Datasheet problem. But unfortunately they write, “What makes this even faster is that we only query the database once. That’s right, we create the ADO recordset once, and store it in an ASP Session variable.” By storing the ADO Recordset in a Session variable they are not satisfying my need for a scalable solution.

Randall Kindig’s Building a Custom Data Grid: Performing Ad Hoc Web Reporting with a VBScript 5.0 Class Object, basically gives me the solution to my Datasheet problem in a hot, Seattle coffee cup. But even in July of 2000 we still find him writing, “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.”

In my struggle to find a stateless solution, I ran across the Microsoft Knowledge Base article Q202125 (HOWTO: Page Through a Recordset from ASP). This article reminds us that ADO Recordsets have the PageSize, PageCount, and AbsolutePage properties. This information addresses the need to be scaleable: to not use the Session variable!

Solution to the Datasheet Problem

Based on the aforementioned research, the solution to the Datasheet problem has two components: a reusable, portable, generic class called clsHTMLTable and an ASP page that “hosts” the class. The hosting ASP page would be “hard coded” to the specific solution but it is still worth mentioning here to illustrate how clsHTMLTable can be implemented. Before we do that, let’s examine clsHTMLTable:

<A HREF="./MyDataTable.asp?Get=CUST001">One Co.</A>
<A HREF="./MyDataTable.asp?Get=CUST002">Two Co.</A>
<A HREF="./MyDataTable.asp?Get=CUST003">3 Co.</A>
…

This Solution Is Simple

Here is a code sample:

'Get table.
Set objHTMLTable = New clsHTMLTable
Set objHTMLTable.Recordset = objADOrst
vHTML = objHTMLTable.GetTable
Response.Write vHTML
Set objHTMLTable = Nothing
objADOrst.Close
Set objADO = Nothing

I hope this helps.

This Solution Is Generic

The design of the class should address this issue. Also note that the class depends heavily on CSS and W3C-compliant HTML. (The Songhay System DTD at the moment is HTML 4.0 Transitional.)

This Solution Is Scaleable

By making sure that this solution is stateless, we have ensured that it is scaleable. My only concerns are any performance issues surrounding how ADO creates a Recordset with its AbsolutePage property set. I think that ADO retrieves the records first and then applies the AbsolutePage property. This may mean performance problems during very large queries. I would defer to the tools in the DBMS to solve this problem (specifically stored procedures).

This Solution Is Portable

Now that I have expressed this historically Microsoft solution as a Class, it is portable. I don’t just mean that it can be translated into a COM object via Visual Basic. I am really concerned about leaving the Microsoft platform altogether—and taking my tools with me. I am talking about what Microsoft may consider unthinkable: translating VB Classes into, say, Java Classes! Gasp!

In this specific case, when shopping for a new platform to run my enterprise, this new technology must implement the equivalent of the Recordset object and the ability to page through its dataset. Additionally, the new web server will have to have something like the IIS QueryString object. (Of course the new platform must have its rendition of a Class!) These are the only vendor-specific dependencies I can find at the moment. By trying hard to not just make the code generic but to also make the code as platform independent as possible I make the solution portable.

By the way, using ASP server-side controls (from ASP+ and the new .NET framework) in my solutions would make them extremely platform dependent.

The Hosting ASP Page and It’s Web

We have seen the star of the show, clsHTMLTable, now it’s time to explore its implementation. The table below lists the files of a simple application that handles data from Songhay System’s Funky Knowledge Base:

This is what The Funky KB “search form” looks like:

This is what some search results look like:

Finally, we have the form:

Future Enhancements

One major feature missing from my current build is the ability to sort by column on the fly. I personally do not have much call for it. I would rather have the user specify a sort during the initial search. The research material mentioned in this article supply quite enough information needed to implement this requirement.

Another major “user-friendly” feature missing is handling of pattern matching. Currently, wildcard searches depend on the user’s knowledge of the pattern matching characters of the DBMS. What is friendlier is to add Exact Phrase, Starts with, Ends with and/or Contains options in the initial search screen.

Finally, to address the performance issue, more research into implementing the Item “Initial Fetch Size” in the Properties Collection of the Recordset object and the use of adAsyncFetchNonBlocking, is needed. These features may not work with all OLEDB Providers under ADO. Moreover, once these features are in use, via a supporting Provider, there are miscellaneous limitations that must be addressed. For example Kindig writes, “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.”