asked on

Help with dependent dop downs

I get BIND failed error when I execute the code listed

dmis.cfc

<cfcomponent displayname="dmis" output="no">

 <cffunction name="getCountry" hint="Gets all country codes from the database" returntype="query" access="remote">
   <cfargument name="TIMPOLookupDSN" type="string" required="yes" default="">
    <cfset var countrydata="">
    <cfquery datasource="#TIMPOLookupDSN#" name="countrydata">
	 SELECT distinct Facility_Country_Code
	 FROM DMIS
     where Facility_Country_Code is not null
	 ORDER BY Facility_Country_Code
   </cfquery>
   <cfreturn countrydata>
 </cffunction>
 
 <cffunction name="getState" hint="Gets all state codes from the database" returntype="query" access="remote">
   	<cfargument name="TIMPOLookupDSN" 	type="string" 	required="yes" default="">
    <cfargument name="countrycode" 		type="string" 	required="yes" default="US">
    <cfset var statedata="">
    <cfquery datasource="#TIMPOLookupDSN#" name="statedata">
	 SELECT distinct Facility_State_Code
	 FROM DMIS
   where Facility_Country_Code = '#ARGUMENTS.countrycode#'
	 ORDER BY Facility_State_Code
   </cfquery>
   <cfreturn statedata>
 </cffunction>
 
</cfcomponent>

test.cfm

<cfform>
<TR>
<TD align="right" nowrap>Country:</TD>
<TD>
<cfselect name="countrycode" 
      bind="cfc:dmis.getCountry()" bindonload="true" 
      display="Facility_Country_Code" 
      value="Facility_Country_Code" />

</TD>
</TR>

<TR>
<TD align="right" nowrap>State:</TD>
<TD>
<cfselect name="statecode" 
      bind="cfc:dmis.getState({#TIMPOLookupDSN#},{countrycode})" bindonload="false" 
      display="Facility_State_Code" 
      value="Facility_State_Code" />
</TD>
</TR>
</cfform>

Open in new window

cfsatx

ASKER

If I select a country code, I would like to display all state codes under that country without having to resubmit the form. How do I accomplish this?

_agx_

Just bind your state cfselect to the countryCode list

<cfselect name="countrycode" ...rest of code ...>
<cfselect name="state"
bind="cfc:dmis.getStates({countrycode})"
display="Facility_State_Code"
value="Facility_State_Code" />

But store the datasource name in the component, instead of passing it as an argument and use cfqueryparam in all queries (for better security)

<cfcomponent displayname="dmis" output="no">
    <cfset variables.dsn = "YourDatasourceNameHere">
   
     <cffunction name="getCountry" hint="Gets all country codes from the database" returntype="query" access="remote">
        <cfset var countrydata="">
        <cfquery datasource="#variables.dsn#" name="countrydata">
             SELECT distinct Facility_Country_Code
             FROM   DMIS
             WHERE Facility_Country_Code is not null
             ORDER BY Facility_Country_Code
       </cfquery>
       <cfreturn countrydata>
     </cffunction>
 
     <cffunction name="getStates" hint="Gets all state codes for a single country" returntype="query" access="remote">
        <cfargument name="countrycode" type="string" required="yes" default="US">
        <cfset var statedata="">
        <cfquery datasource="#variables.dsn#" name="statedata">
             SELECT distinct Facility_State_Code
             FROM DMIS
             WHERE Facility_Country_Code =  '#ARGUMENTS.countrycode#'
             ORDER BY Facility_State_Code
           </cfquery>
        <cfreturn statedata>
     </cffunction>
 
</cfcomponent>

Open in new window

cfsatx

ASKER

That worked. Thanks

I would like the selected value for country code to be US.

Also during update, I would like the drop down lists to pick the database value.

How I can accomplish that?

_agx_

> I would like the selected value for country code to be US.
> How I can accomplish that?

Normally you use cfselect's "selected" attribute. But it only works if you have CF9.0.1+. Some pre-CF 9.0.1 alternatives

http://www.dansshorts.com/post/cfselect-binding-and-selectedvalues
http://www.coldfusionjedi.com/index.cfm/2007/8/7/Selecting-default-items-using-ColdFusion-8s-AJAX-Controls

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial