We will be modifing code from Part 1 so if you need to view Part 1 Click here 

To begin we must look at the form that we will use for the search screen.

formA.cfm Code

<cfform name = "form1" action="formB.cfm" method="Post">    

State:  

<cfif ISDEFINED("states_temp") AND states_temp NEQ ')>

<cfinput type="hidden" name="states_temp" value="#states_temp#">

<cfselect name="states" bindOnLoad="Yes" bind="cfc:data.getStates({states_temp})" />

<cfelse>

<cfselect name="states" bindOnLoad="Yes" bind="cfc:data.getStates()" />

</cfif> 

We will use the same method with the city dropdown but will be passing the state

City:    

<cfif ISDEFINED("city_temp") AND city_temp NEQ ')> 

<cfinput type="hidden" name="city_temp" value="#city_temp#"> 

<cfselect name="city" bindOnLoad="Yes" bind="cfc:data.getCity({states},{city_temp})" /> 

<cfelse>

<cfselect name="city" bindOnLoad="Yes" bind="cfc:data.getCity({states})" /> 

</cfif> 

<cfinput type="submit" name="submit" value="Search"> 

</cfform>

formB.cfm Code  

Store states as a temp variable this will be used to repopulate the dropdown in the formA 

<cfif ISDEFINED("states")>

<cfset city_TEMP = #city#>

</cfif>  

<cfif ISDEFINED("form.states_TEMP") AND states_TEMP NEQ '>

<cfset form.states_TEMP = #form.states#>

</cfif> 

Store city as a temp variable this will be used to repopulate the dropdown in the formA

<cfif ISDEFINED("city")>

<cfset city_TEMP = #city#>

</cfif> 

<cfif ISDEFINED("form.city_TEMP") AND form.city_TEMP NEQ '>

<cfset form.city_TEMP = #form.city#>

</cfif>  

Include the search so the user can do another search. 

<cfinclude template="formA.cfm"> 

Our simple search to get a persons name and phone number. 

 <cfquery name="searchQry" datasource="#myDN#">

     SELECT first_name, last_name, phone_number FROM dbo.main_

     WHERE state = '#form.state#' AND city = '#form.city#'

     ORDER BY last_name

</cfquery> 

Display search results 

<table>

<tr>

<th>First Name</th>

<th>Last Name</th>

<th>Phone</th>

</tr>

<cfoutput query = "searchQry">

<tr>

<td>#first_name#</td>

<td>#last_name#</td>

<td>#phone_number#</td>

</tr>

</cfoutput>

</table> 

data.cc Code

All the magic happens in the cfc.  Here is the new code.  The explaination will follow.   

<cfcomponent output="false">

    <cffunction name="getStates" access="remote" returntype="array">

            <cfargument name="state_temp" type="string" required="false">          

        <cfquery name="states" datasource="#myDN#">

        SELECT DISTINCT state FROM dbo.data

                        <cfif ISDEFINED("state_temp") AND state_temp NEQ '>

                        WHEREstate <> '#state_temp#'

                        </cfif>

        ORDER BY state