Auto refresh new form field

I'm a beginner with coldfusion. I'm having the hardest time with inserting a record to the database from a form. The problem is that I want the site to be open to all colleges across the nation. I have a list of all of the colleges, but it's WAY too many for a pick list. I know how to have them search the database for their college, but they might type "ASU" instead of "Arizona State" and therefore wouldn't find their college. I want them to choose their state in one form field and have the page auto refresh with a new pick list of just the colleges in that state under it. Is there an easier way? If not, how in the world do I do that? Thanks.

 

There are other ways, but auto refreshing the page is the simplest.

To do this, populate your list of states and use the list's onChange event to refresh (submit) the form. OnChange is a javascript event. But how does this relate to into "inserting a record to the database from a form" ?

Not tested

<!--- by default nothing is selected --->
<cfparam name="url.selectedState" default="" />

<cfquery name="getColleges" datasource="yourDataSource">
    SELECT  State
    FROM    AllStates
 </cfquery>

<form action="yourPage.cfm" method="get">
   <!--- I hard coded the list of states as an example only --->
   <select name="selectedState" onChange="this.form.submit();">
      <cfoutput query="getStates">
        <option value="#State#" <cfif getStates.State is url.selectedState>selected</cfif>>#State#</option>
      </cfoutput>
   </select>
</form>

<!--- If a state was selected, display the colleges for that state --->
<cfif len(trim(url.selectedState)) gt 0>
    <cfquery name="getColleges" datasource="yourDataSource">
        SELECT  CollegeName
        FROM    yourTable
        WHERE   State = <cfqueryparam value="#url.selectedState#" cfsqltype="cf_sql_varchar" />
    </cfquery>

    <cfoutput query="getColleges">
        #CollegeName# <br />
    </cfoutput>
    <hr />
    <b>[ #getColleges.recordCount# ] colleges found</b>
</cfif>

Code (markup):