Browse DevX
Sign up for e-mail newsletters from DevX


More Useful Form-Building Routines  : Page 4

Use automatic control-building routines to create and populate HTML combo and list box controls from a database.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Optimizing the Code
The example shows you how to display the contents of a recordset as a combo box or as a list box; but there's still room for optimization. As I promised earlier, here are some optimization tips.

First, you don't need two almost identical copies of the code to handle a ListBox and a ComboBox display. You can combine the two into a single function call. There are several different ways to do it. One is to add a new argument to the DisplayComboBox routine that will accept the SIZE attribute. If you want a ComboBox, pass a value of 1. If you need a Listbox, pass a value greater than 1. You can also use another technique. To make your code simpler to read and more obvious, you can transfer the entire contents of the DisplayComboBox routine to a brand new function called, for example, DisplayHTMLSelect, which accepts the additional SIZE argument. Therefore, the DisplayComboBox routine will contain only a single call to the DisplayHTMLSelect routine, passing the SIZE argument with a value of 1. The DisplayListBox routine will do the same thing, but passes the SIZE argument it receives from you:

   Sub DisplayComboBox(byval strLabel, _
      byval strFieldName,  _
      byval strValue, byval strLookUpTable, _
      byval strLookUpID, byval strLookUpDesc, _
      byval blnRequired)
      Call DisplayHTMLSelect (strLabel, strFieldName,  _
         strValue, strLookUpTable, _
         strLookUpID, strLookUpDesc, _
         blnRequired, 1)
   End Sub
   Sub DisplayListBox(byval strLabel, _
      byval strFieldName,  _
      byval strValue, byval strLookUpTable, _
      byval strLookUpID, byval strLookUpDesc, _
      byval blnRequired, byval intSize)
      Call DisplayHTMLSelect (strLabel, strFieldName,  _
         strValue, strLookUpTable, _
      strLookUpID, strLookUpDesc, _   
      blnRequired, intSize)
   End Sub
Second, you can optimize the code significantly. Instead of concatenating the string "strResult" that holds all the OPTION items within your ASP page, you can force your database engine to do the concatenation and return a neat list of OPTION tags for you.

Changing the original SQL statement-building to:

   ' -- build your SQL statement
   strSQL = "SELECT '<OPTION VALUE=""' + " & _
      "CONVERT(VARCHAR(5), " & strLookUpID & ") + " & _
      "'"">' + RTRIM(" & strLookUpDesc & ") + " & _
      "'</OPTION>' AS TheField FROM " & strLookUpTable 
   ' -- order by clause if needed
   strSQL = strSQL & " ORDER BY " & strLookUpDesc
results in a recordset where every record contains one pre-built OPTION tag. The code to concatenate the output then changes to:

   if (objRSCbox.BOF and objRSCbox.EOF) Then
      strResult = "<OPTION " & _
      "VALUE="""" SELECTED>None Found</OPTION>"
      Do While Not objRSCbox.EOF
         strResult = strResult & objRSCBox("theField")
   End If    ' if (objRSC.BOF and objRSC.EOF) Then
   strResult = "<OPTION VALUE="""">None Selected</OPTION>" & strResult         
The resulting ASP code is smaller and faster, however you've now sacrificed the ability to preselect an item. That's easily fixed. In the optimized version, we use the VBSCRIPT REPLACE function to do the trick.

   ' -- Handle the Pre-Selection of the SELECT control
   ' -- All we need to do is to add the word SELECTED
   ' -- next to the item whose value = strValue
   strResult = Replace(strResult, "VALUE=""" & strValue _
      & """","VALUE=""" & strValue & """ SELECTED")
The preceding code adds the attribute "SELECTED" to the item whose value matches the strValue argument received by the function. If you pass a null string, the code selects the item that reads "None Selected."

However, the optimization technique from which you can benefit the most is caching. The example code hits the database once EVERY TIME you build the page. Because the lookup data does not change very often, this is a very poor design. A much better way would be to "cache" the lookup data that you obtain from the database and reuse it WITHOUT HITTING the database. If you planning to implement caching, DO NOT ever cache a RECORDSET object, or for that matter ANY apartment-threaded object. Caching an apartment-threaded object at Application scope severely cripples your web application, forcing IIS to operate in a single threaded mode. Instead, cache the STRING "strResult" and reuse that. I explained this optimization technique earlier in the article Filter Data from a Database.

The lookup database data that fills the combo boxes is common for ALL users, it makes sense to cache the output in an Application variable. To do so, use the following code:

   Application("cbo" & strFieldName) = strResult
By using the argument passed to the routine, you avoid overwritingany existing application variable with the same name. Before you assign a value to an application variable, always Lock and then UnLock the Application object to make sure no other page is trying to access the variable while you are setting its value. Therefore, your code will actually be:

   Application("cbo" & strFieldName) = strResult
You want to hit the database on only the first call. In each subsequent call, you want to reuse the cached value.

      ' -- Check the CACHE first
      strResult = Application("cbo" & strFieldName)
      If strResult = ""
         ' -- Nothing in cache. Hit the database
         ' -- build your SQL statement
   ... rest of the code
Check the value of the Application variable. If it is contains a value, you can use that value to build the SELECT list; otherwise, a null string, you must aquire the items from the database. At the end of the If block, the code caches the concatenated list of OPTION items in the Application variable.

You can obtain the complete source code utilizing the above approach here. Remember to "include" the source code file "IncFormRoutines.asp" within your asp page to be able to use its functionality:

<!--#include file="IncFormRoutines.asp" -->

The next Tactical ASP will consider routines to automatically generate check boxes and radio buttons. There are many more enhancements that I am sure you can think of. If you have common form-building routines that you would like to share with everyone else, I would love to hear from you. Drop me a line at rama@imperium.com

Rama Ramachandran , is Vice President of Technology with Imperium Solutions and is a Microsoft Certified Solution Developer and Site Builder. He has extensive experience with building database systems and has co-authored several books including Professional Visual InterDev 6 Programming and Professional Data Access (Wrox). Rama also teaches Visual Basic and Web Development at Fairfield University and University of Connecticut.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date