Automate Your Classic ASP Web Forms

TML form handling consists of a set of named form controls on an HTML page and a Submit button for sending the information contained by the controls to a server, which presents a confirmation/summary page to the user, validates the information and then updates a database. Most important forms insert one more step?displaying the entered data in a preview page so that users can examine the information and correct any errors or incomplete data. When the user approves the information, the preview page’s Submit button sends the data to the server, where server-side code writes the information to a database.

If you’ve developed data-driven Web applications, this scenario should be very familiar. Unfortunately, as forms become more complex, all this passing data back and forth makes developing such pages labor-intensive. It becomes increasingly difficult to ensure that developers perform the data-passing operations correctly, matching the form field control names to the label names for the data on the preview page, and eventually, to the correct columns in the database.

What You Need
Classic ASP, standard HTML form controls, an ASP-enabled Web server for building and troubleshooting your ASP code, and a SQL-aware database.

Consider the code fragments shown below, which contain:

  • Typical code for a generic HTML form
  • The server side code to format values submitted by the form into hidden fields on the preview page.
  • The server-side code to build a SQL INSERT statement to insert the data into a database.

The first task involves building a standard HTML form. Developers must select control names and associate those names with individual data fields.Generic HTML Form:

         

In the second task, developers gather submitted values from the form and insert them into fields for a preview page, touching each data value twice more.ASP Preview (Server-side Code)

      My Control Value:  value="<% =    Request.Form("Control_Name")%>">

Finally, after the user approves the data on the preview page, developers must gather the hidden field values and construct an INSERT statement?touching the data values yet again. ASP Submit Page:

   <%   ' DataConnection code goes here   ' Construct an INSERT statement from    '   the submitted hidden field value(s)   SQL = "Insert into Table (FieldNames) " & _      "Values ("Control_Name")"   DataConnection.Execute (SQL)   %>

The process is extremely error-prone. As you can see from the code fragments, there are four separate places where a developer must identify a data value with the name “Control_Name.” Misspelling the form control name can result in a variety of problems. If you misspell the control name in the “> section of the preview page, no value will display. Finding such an error is obvious for this simplistic form, but becomes considerably less obvious when you’re assembling a preview page for a complex form. Unavoidably, the more complex the form, the easier it gets to miss the fact that one or more of the fields has a mismatched name.

Worse things can happen: if you accidentally use a control name that corresponds to an existing, but different control?a value will appear, but not the one you want. Or if you misspell the name in the hidden field and not in the value field, you’ll see the right value in preview mode, but that value won’t get sent to the database?and the UPDATE or INSERT won’t necessarily report an error. For example, if your database field is Foo_1 and you assign Fooo_1 to the “>, what happens to your data on submittal? If the submit string expects Foo_1 somewhere in the Request.Form stream, that value will not show up. The data contained by Fooo_1 disappears into cyberspace, never to be seen again.

When you’re building forms with a lot of controls, you’re in real danger of data loss if you don’t handle the field names consistently. Wouldn’t it be better for ASP to do all that for you?

In the course of finding a method to do this I found the following code on www.aspfaq.com:

   <%    For x = 1 To Request.Form.count()       Response.Write(Request.Form.key(x) & _         "(" & x & ")" & " = ")       Response.Write(Request.Form.item(x) & _         "
") Next %>

This code displays all the form elements (including the SUBMIT and RESET buttons) passed to it from a POST in the order they appear on the source form. This is the first step toward flawless form handling! You can use this technique to display control values and build a list of hidden control values automatically that will pass to a SUBMIT page where you can then construct the necessary SQL string for writing/reading/inserting the database. The following sections contain examples of each operation.

Previewing Form Data In Table Format
The following code shows how to extract a list of form field elements and values and view them in a table for the preview page. This technique is extremely useful for debugging.

   <%   I = 0   Response.Write "<table border = ""1"" & _      width = ""500"">" & _      "<tr><td width=""%100"" colspan = ""3"">" & _      "<p align = ""center"">" & _      "<b>Table Title</b></td></tr>"      For x = 1 To Request.Form.count()       item = Request.Form.key(x)      Select Case item        Case "Control_Name1_Do_Not_List"        Case "Control_Name2_Do_Not_List"        Case " Control_Name3_Do_Not_List"        Case "B1" 'Submit Button Name        Case Else           j = i mod 3           If j = 0 Then              response.write "<tr><td>" & Item & "</td>"           Else              response.write "<td>" & Item & "</td>"           End If           i = i + 1      End Select       Next   %>

There are some considerations to keep in mind. The loop in the preceding code finds every named control on the form page; however, not every control contains a data value destined for the database. At minimum, you’ll need to skip the Submit Button name/value. I use the Select Case construct for skipping values I don’t want displayed because it is easier to read and edit than If…Then logic.

Gathering Hidden Field Values
As you might expect, gathering the data for the submit operation is even more straightforward than constructing a table to preview the form data. When the user submits the main form page, you use a loop similar to the one you’ve already seen to retrieve the form values and output a list of hidden field tags that you’ll use later when the user approves the data and submits the preview form.

   <%   MonthlyDate = Request.Form("Date_month") & "-" & _     Request.Form("Date_day") & "-" & _     Request.Form("Date_year")      For Each item In Request.Form   Select Case item          Case "Date_month"          Case "Date_day"          Case "Date_year"          Case "B1"          Case Else       ' create a hidden field containing the data value       quote=chr(34)       Response.Write "<input type=""hidden""" & _       "name=" & quote & item & quote & " value=" & _       quote & Request.Form(item) & quote & ">"   End Select       Next       Response.Write "<input type=""hidden""" & _           name=""MonthlyDate"" value=" & quote & _       MonthlyDate  & quote &">"   %>

The preceding code skips some form names and values?the Submit button and three other fields used to create the value for the MonthlyDate variable. The MonthlyDate value requires special formatting to ensure the entire value gets written to the hidden form field. In this case, the last line wraps the MonthlyDate value in quotes before creating the hidden input tag. Again, using the Select Case construct makes it easy to create and maintain the logic to skip special and/or unwanted field names and values.

Author Note: field values with spaces may get truncated in the hidden field list if the value is not wrapped in quotes. The code above should do this automatically. In addition, form values with apostrophes may cause the submit operation to fail. You can catch this possibility when building the hidden fields by using the Replace operation in the code:

   response.write ""

Submitting the Data to the Database
When the user accepts the data on the preview page by clicking the Submit button, you need to gather the hidden values and construct an SQL INSERT statement to add the new values to your database.

The generic SQL syntax for inserting values into a table is:

   INSERT INTO  (, )       VALUES (, )

To build the appropriate INSERT statement programatically without yet another layer of name translation, the names of your form controls must match the corresponding database field name exactly. Therefore, when you add or change an HTML form element, you must also make sure the field exists in the underlying database. This should not be difficult, even with very large collections of form elements. Here’s how you build the SQL string:

   <%   MonthlyMeetingSubmitTime = Now()      For Each item In Request.Form      Select Case item              Case "Submit Monthly Information"              Case Else            InsertString = InsertString & item & ", "      End Select   Next      InsertStringLength = Len(InsertString)   InsertStringLengthTrim = InsertStringLength -2    InsertStringName = Left(InsertString, _      InsertStringLengthTrim)      mySQL = "INSERT INTO MonthlyMeetingData(" & _      InsertStringName & ""      mySQL = mySQL & ", MonthlyMeetingSubmitTime)"      For Each item In Request.Form      Select Case item            Case "Submit Monthly Information"              Case Else            ValueString = ValueString & _            request.form(item) & "', '"      End Select   Next        ValueStringLength = Len(ValueString)   ValueStringLengthTrim = ValueStringLength -3   ValueStringName = Left(ValueString , ValueStringLengthTrim)   ValueStringName = "'" & ValueStringName       mySQL = mySQL & "VALUES (" & ValueStringName & ""      mySQL = mySQL & ", " & "'" & MonthlyMeetingSubmitTime & "'" & ")"      DataConnection.Execute mySQL      %>

Note that you extract the values passed to the INSERT SQL string directly from the hidden fields written in the Preview page. By wrapping the form field values in quotes and using the Replace(Request.Form(item), “‘”, “””) you can avoid the possibilities of truncating data from form field values containing embedded spaces or malformed SQL INSERT commands when field values contain apostrophes.

This preceding code generates INSERT statements automatically when working with complex forms. In addition, because the For?Next loops for building the INSERT INTO string and the VALUES string run in ‘parallel’ (they pick up exactly the same form elements in the same order), the form fields always match the form values. An INSERT SQL string generated from this code would look something like this:

   INSERT INTO TableName (FieldName1, FieldName2,       FieldName3, SubmitTime)VALUES ('', '',       'Children and Youth', 'Unknown',       '10/14/2003 1:34:47 PM') 

The code that builds the SQL statement should be easy to follow. The string trimming line at the end of the string building code removes the trailing “, “ resulting from the loop used to build the field name list from the form control names and the trailing “‘, ‘” from building the field value data. Using the single-quote character to surround all the values ensures that values with spaces (such as strings) get captured. You don’t need to worry about this with the field names because the name of the control matches the database column name. The sample uses the Now() operator to set the MonthlyMeetingSubmitTime datestamp value used to identify the date and time when the data was sent to the database.

Debugging Tips
You may get errors at this point. To help you debug, insert a line reading Response.Write mySQL before executing the SQL. That will return the constructed SQL statement to the browser so you can inspect it. Errors at this point are generally caused by:

  • A mismatch between the data types in the database and the types of the submitted field values.
  • Failing to skip a field you don’t want to send to the database, resulting in an SQL statement that tries to insert a value into a nonexistent database column.
  • Failing to format the field value list with quotes.
  • You should inspect the completed INSERT statement to make sure you’re capturing the complete value. Some database systems may need special delimiters for some field types. You may need to insert these field values at the end of the string, as I’ve illustrated with the datestamp section of the code.

To summarize:

  1. Build your form as desired.
  2. For each control in the field, you must create field name in the backend database that has exactly the same name. Specify data types as appropriate (binary for check boxes and radio buttons, Memo for large text boxes, etc.).
  3. When building the preview page, use Select Case to skip form elements that you don’t want displayed (for example, the Submit button name).
  4. Using the same method, build a list of hidden fields to pass to the database SUBMIT page. You may need to wrap certain field values in quotes in order to pass the complete value.
  5. Build the INSERT INTO strings as specified.
  6. If you add or change a form field control, make sure to change the database field name to match the new/edited control’s name.

That’s it! With a little preparation you can create a HTML form with any number of controls and, as long as the control name attribute value matches the corresponding column name in the backend database, you can create preview and submit pages (or other pages in the stream) and carry your data seamlessly to the database.

Additional Techniques
One HTML form I wrote in the course of a project consists of 167 form fields (checkboxes, radio buttons, text boxes, drop-downs, etc.). The form is divided into several sections. I needed to present the preview information in a way that showed the grouping of the form. For example, one section deals with Attendance, another with Service Needs, another with Agency Participation, etc. In addition, I needed to set up an “alarm” message that would display a “Section Incomplete” message if users failed to select a value within a group. Given the length and complexity of the form, users need to view the various groups of data on their preview page in a printable and compact way. The preview would also need to warn users if they missed an entire section. I wouldn’t recommend constructing such a complex form but this was specified in committee!

Previewing Groups of Elements
You can use naming conventions to allow ASP programming logic to find and group the form field contents into sections on the preview page and also use programming logic to determine whether a section has anything selected. For example:

   <%     ' Initialize counters for checkbox groups on the form   AttendanceChecked = 0   InvolvedChecked = 0   BasicNeedsChecked = 0   ...Other Counters As Needed...      ' For each group, specify a prefix or suffix in the    ' form control name. Use the instr function in ASP to    ' identify that a given value exists (a checkbox was    ' ticked)      For x = 1 To Request.Form.count()          Attendance = Instr(Request.form.key(x), _            "_Attendance")         Involved = Instr(Request.form.key(x), _            "_Involved")         BasicNeeds = Instr(Request.form.key(x), _            "BasicNeed_")         ' ...Other Groups as Needed...            ' Increment the counter for each          ' value that exists         AttendanceChecked = AttendanceChecked + _            Attendance          InvolvedChecked = InvolvedChecked + Involved          BasicNeedsChecked = BasicNeedsChecked + _            BasicNeeds         '...Other Counters as Needed...      Next       ' Build a table to list all the Attendance elements    ' if any have been passed to the preview page       If AttendanceChecked > 0 then      Response.Write "
" & _ "" For X = 1 To Request.Form.count() Item = Request.Form.key(x) SectionsChecked = instr(Request.form.key(x), _ "_Attendance") If SectionsChecked > 0 Then ' Use string trim function to display ' just the name of the field and not the ' _Attendance suffix ItemLength = Len(Item) ItemLengthTrim = ItemLength - 11 ItemName = Left(Item, ItemLengthTrim) j = i Mod 3 If j = 0 Then Response.Write "" Else response.write "" End If i = i + 1 End If Next Response.Write "
" & _ "

Referring " & _ "Agencies

" & _ ItemName & "" & ItemName & _ "
" Response.Write "
" Else ' If there were no Attendance fields checked then ' response.write in red a warning that this section ' contains no information Response.Write "

" & _ "No referring agencies specified!" & _ "

" End If %>

Creating an Edit Page
If you submit data to a remote database to which you don’t have direct access, you can easily create an ASP page based on the HTML form that can pull up the data for edits. To do that, copy the HTML form, save it as an .ASP file and pull up the needed data from the database using the appropriate SQL SELECT statement. To view the information in the ASP form, simply re-name the form fields by wrapping the name in <%= myRS.Fields("FieldName") %>. Since your HTML form field names match the database fields, this is a relatively trivial operation. After you have made your edits, you can pass the information back to the database using a SQL UPDATE statement.

Some systems may have limits on the number of fields that an UPDATE statement can handle. For example, MS Access will not process an UPDATE with more than 155 fields in the list. You can use the ability to group sets of fields based on standard suffixes or prefixes to build a set of UPDATE statements to work around this limitation.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: