Browse DevX
Sign up for e-mail newsletters from DevX


More Useful Form-Building Routines  : Page 2

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

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 "<TD ALIGN=""RIGHT"" VALIGN=""TOP"">" 
   If blnRequired Then
      Response.write "<FONT COLOR=""BLUE"">"
   End if
   Response.Write strLabel & "&nbsp;</FONT></TD>"
   ' -- Now the combo box table cell
   Response.Write "<TD ALIGN=""LEFT"" VALIGN=""TOP"">"
You can then begin to output the SELECT tag:

   ' -- Output the select tag
   Response.Write "<SELECT NAME=""" & _
      strFieldName & """ SIZE=""1"" >"
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 = "<OPTION " & _
      "VALUE="""" SELECTED>None Found</OPTION>"
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 & "<OPTION " & _
         "VALUE=""" & objRSCbox(strLookUpID) & """"
      If Trim(objRSCbox(strLookUpID)) = _
         Trim(strValue) Then
         strResult = strResult & " SELECTED"
         blnFound = True
      End if
      strResult = strResult & ">" & _
         objRSCbox(strLookUpDesc) & "</OPTION>"
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:

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

   if not blnFound then
      strResult = "<OPTION " & _
      "VALUE="""" SELECTED>None Selected</OPTION>" & _
      strResult = "<OPTION " & _
      "VALUE="""">None Selected</OPTION>" & strResult
   End if

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