Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Automate Your Classic ASP Web Forms : Page 3

If you've ever created data-driven forms, especially complex ones, you know how hard it is to make all the control names and database column names match up correctly—and you'll immediately appreciate how the techniques shown in this article can simplify development, reduce errors, and minimize maintenance.

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 <table> (<field1>,<field2> ) VALUES (<FieldValue1>, <FieldValue2>)

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.

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