Dynamically populating list boxes

In Form controls, you hard-coded a form's list box options. Instead of manually entering the information on a form, you can dynamically populate a list box with database fields. When you code this way, the form page automatically reflects the changes that you make to the database.

You use two tags to dynamically populate a list box:

To dynamically populate a list box:

  1. Open the file formpage.cfm.
  2. Modify the file so that it appears as follows:
    3. <html>
<head>
<title>Input form</title>
</head>
<body>
<cfquery name="GetDepartments" datasource="CompanyInfo">
SELECT DISTINCT Location
FROM Departmt
</cfquery>

<!--- Define the action page in the form tag. 
   The form variables will pass to this page 
   when the form is submitted --->

<form action="actionpage.cfm" method="post">

<!-- text box -->
<p>
First Name: <input type="Text" name="FirstName" size="20" maxlength="35"><br>
Last Name: <input type="Text" name="LastName" size="20" maxlength="35"><br>
Salary: <input type="Text" name="Salary" size="10" maxlength="10">
</p>

<!-- list box -->
City

<select name="City">
<cfoutput query="GetDepartments">
<option value="#GetDepartments.Location#">
#GetDepartments.Location#
</option>
</cfoutput>
</select>

<!-- radio buttons -->
<p>
Department:<br>
<input type="radio" name="Department" value="Training">Training<br>
<input type="radio" name="Department" value="Sales">Sales<br>
<input type="radio" name="Department" value="Marketing">Marketing<br>
<input type="radio" name="Department" value="HR">HR<br>
</p>

<!-- check box -->
<p>
Contractor? <input type="checkbox" name="Contractor" value="Yes" checked>Yes
</p>

<!-- reset button -->
<input type="reset" name="ResetForm" value="Clear Form">

<!-- submit button -->
<input type="submit" name="SubmitForm" value="Submit">
</form>
</body>
</html>
  3. Save the page as formpage.cfm.
  4. View formpage.cfm in a browser.

    5. The changes that you just made appear in the form.

    Remember that you need an action page to submit values.

Reviewing the code

The following table describes the highlighted code and its function:

Code Description 
<cfquery name="GetDepartments"
datasource="CompanyInfo">
SELECT DISTINCT Location
FROM Departmt
</cfquery>

Get the locations of all departments in the Departmt table. The DISTINCT clause eliminates duplicate location names from the returned query results. 

<select name="City">
<cfoutput query="GetDepartments">
<option value="#GetDepartments.Location#">
#GetDepartments.Location#
</option>
</cfoutput>
</select>

Populate the City selection list from the Location column of the GetDepartments query. The control has one option for each row returned by the query.

View comments on LiveDocs