devxlogo

More Useful Form-Building Routines

More Useful Form-Building Routines

ast month’s column contained some ASP routines that made the task of building a form easy and elegant. This month I’ll extend the concept by showing you how to build List and Combo boxes automatically. Usually, these form controls need to display data from a database; therefore, the form-building routines must accommodate database access, which makes the code interesting.

Last month’s article discussed:

  • A manual way to build a form containing labels and text boxes
  • Automating the form-building process with functions that create label and text boxes
  • Handling form field validation
Generating Combo and List Boxes Automatically
In HTML, the SELECT tag specifies either a drop down combo box or a list box. The SELECT tag’s SIZE attribute value if critical. When the SIZE is 1, the tag creates a combo box; any value greater than 1 creates a list box that number of lines in height. The SELECT tag is a block element that contains one or more OPTION tags that specify the items of the combo or list box. For example, the following code produces a combo box containing three items:

   

The same code, with just the SIZE attribute changed, produces a list box with three items:

   

If you were writing code to generate these three combo boxes, for each combo box you would have to:

  • Access the database and obtain a recordset of all records from the appropriate table
  • Output the SELECT tag
  • Within the SELECT tag, for each record in the recordset, output an OPTION tag
  • If the form field already contains a predetermined value, then make sure to pre-select it in the combo box control.
  • Repeat the same steps for the other combo boxes.
You can simplify all the above by writing a function that will handle everything and simply call the function three times to obtain the three combo boxes. In the tradition of our earlier article, this is the code necessary to generate the three combo boxes in addition to the other fields:

   WriteFormBegin    “frmInfo”, “pgApply.asp”, _      “POST”, “return ValidateForm(this);”      WriteTableBegin 600         DisplayTextField “First Name:”, “first_name”, _      objRS(“first_name”), 60, 35, True      DisplayTextField “Last Name:”, “last_name”, _     objRS(“last_name”), 60, 35, True      DisplayTextField “Title:”, “title”, _      objRS(“title”), 60, 80, False      DisplayTextField “Company:”, “company”, _      objRS(“company”), 60, 80, True      DisplayTextField “Address:”, “address_1”, _      objRS(“address_1”), 60, 80, True                           DisplayTextField “”, “address_2”, _      objRS(“address_2”), 60, 80, False      DisplayTextField    “City:”, “city”, _      objRS(“city”), 40, 35, True      DisplayTextField    “Zip:”, “zipcode”, _      objRS(“zipcode”), 40, 10, True      DisplayComboBox “Industry:”, _      “Industry_ID”, _      objRS(“Industry_ID”), _      “tblIndustry”, _      “Industry_ID”, _      “Industry_Name”, _      True      DisplayComboBox “Market:”, _      “Market_ID”, _      objRS(“Market_ID”), _      “tblMarket”, _      “Market_ID”, _      “Market_Name”, _      True      DisplayComboBox “State:”, _      “State_ID”, _      objRS(“State_ID”), _      “tblState”, _      “State_ID”, _      “State_Name”, _      True      DisplaySubmitButton  “Submit”, “cmd”         WriteTableEnd

The new code is the call to the “DisplayComboBox” sub routine. For the “Industry” combo box, it reads as follows:

   DisplayComboBox “Industry:”, _      “Industry_ID”, _      objRS(“Industry_ID”), _      “tblIndustry”, _      “Industry_ID”, _      “Industry_Name”, _      True

The DisplayComboBox Routine
Let’s take a look at what this routine does. The DisplayComboBox routine accepts the 7 parameters shown in Table 1.

Table 1: A list of the parameters required by the DisplayComboBox routine and a description of each.

ParameterDescription
strLabelThe text for the label to use
strFieldNameThe name of the combo box
strValue The value preselected in the combo box. Not the text, but the value associated with the text of the item.
strLookupTableThe database table to use to find all available values for this Combo box
strLookupID The primary key, unique id within the table
strLookupDesc The text of the item to display
blnRequired A flag to indicate this combo box is a required control on the form.

?

The Code Explained
For all you sharp-eyed, advanced ASP programmers out there who have spotted many areas in the above code that can be optimized to make the code “better” — bear with me while I explain the code. We’ll take a look at how the code can be optimized later on.

The initial lines of code for the DisplayComboBox and DisplayTextBox routines are identical. They both display two cells of a single row of a table, with the label on the left side.

   ‘ — Output the Label   Response.write “”    If blnRequired Then      Response.write “”   End if   Response.Write strLabel & ” ”   ‘ — Now the combo box table cell   Response.Write ““You can then begin to output the SELECT tag:

   ‘ — Output the select tag   Response.Write “
The next lines are tricky. You need to obtain a list of items to fill the combo box from a database table. The routine needs to know what to display in the combo box and what value to assign to the text entry if the user selects it. The arguments “strLookupID”, “strLookupDesc” and “strLookupTable” contain those values. The the “Industry” example, uses the table “tblIndustry”. The “Industry_ID” field is the primary key or unique id, and the “Industry_Name” field contains the text to display. Therefore, a the valid SQL select statement is:

   SELECT Industry_ID, Industry_Name from tblIndustry
The next few lines of code create the SQL statement based on the arguments sent to the function, including an ORDER BY clause to make sure the items are sortedproperly:

   ‘ — build your SQL statement   strSQL = “SELECT ” & strLookUpID & “,” & _      strLookUpDesc & ” FROM ” & strLookUpTable      ‘ — order by clause if needed   strSQL = strSQL & ” ORDER BY ” & strLookUpDesc   
Finally, you would replace the following with the code to generate a recordset based on the SQL statement:

   ‘ — use the SQL Statement with your standard data    ‘ — access method to obtain a recordset
The reason I did not include the code is that the actual method you will use to populate a recordset will depend on your specific data access method of choice. If you have been following my previous articles in this site, you will find that I prefer to call another useful data access routine called “GetRecordSet” to populate a recordset variable (see the article Add New Data Access Routines to your Arsenal). In my case therefore, the code will be:

   Set objRSCbox = GetRecordSet(strConnString, strSQL)
The variable “strConnString” in the preceding code contains a valid connection string to the database and “strSQL” is the SQL statement.

You are not restricted to using this technique alone. In reality, I would not advocate building a SQL statement like this on the fly in a production environment. A stored procedure (if your database supports it) is far more efficient. In that case, all you have to do is to substitute the name of a stored procedure instead of the argument for the Table Name and modify the code to call the stored procedure instead of building a SQL statement on the fly. All other code can remain intact.

Once you obtain a recordset filled with data, the code generates the item list for the combo box. First check to make sure you have data, if not, create one item containing “None”:

   If (objRSCbox.BOF and objRSCbox.EOF) Then      strResult = “
Otherwise generate an OPTION tag for each record in the recordset, placing the “VALUE” attribute and the text for the OPTION tag in the correct locations.

      strResult = strResult & “
As you iterate through the records, make sure to add the “SELECTED” attribute if the current value of the field equals the value of the record in the recordset. Adding the SELECTED attribute causes the client to display that option as selected when the page loads. If you do find an item that needs to be pre-selected, set the flag “blnFound” to true.

Loop through the recordset until you’ve processed all the records:

      objRSCbox.MoveNext   Loop
Finally, add a top-level item. If there were no matching records, pre-select it.

   if not blnFound then      strResult = “” & _      strResult   Else      strResult = “” & strResult   End if

Adding Client Side Validation

   If blnRequired Then      mstrFormValidationScript = _      mstrFormValidationScript & _      “if(f.” & strFieldName & “.value==””””) {” & _         vbCrLf & _      ”   msg += ‘Please select a value for the ‘” & _         strLabel & “‘ field.
‘;” & vbCrLf & _ ” retVal = false;” & vbCrLf & _ “}” & vbCrLf & vbCrLf End If
Because the “value” of the first item in the list is a null string, this client side validation checks to see if the value of the combo box is a blank string. If so, it flags an error that will be captured when the form is submitted.

And there you have it. A single line call will access the database and populate a combo box with values. To modify the code to build a list box instead of a combo box, you can simply copy the DisplayComboBox routine and call it DisplayListBox. All it needs is a new argument to specify the size in items of your list box and a change in the line:

      ‘ — Output the select tag      Response.Write “
Change the “SIZE=1” value to a “SIZE=N” value where N is an argument you pass.

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 ‘‘ 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 = “”   Else      Do While Not objRSCbox.EOF         strResult = strResult & objRSCBox(“theField”)         objRSCbox.MoveNext      Loop   End If    ‘ if (objRSC.BOF and objRSC.EOF) Then      strResult = “” & 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.Lock   Application(“cbo” & strFieldName) = strResult   Application.UnLock 
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:



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 [email protected]

devx-admin

Share the Post: