CFC: Bind issue

[teedoff]

ok I've tried to work through this myself for several days, but there's something I'm missing. Either my queries are wrong, or my functions are wrong.

I'm using Ben Fortas CF AJAX dynamic select drop downs tutorial and everything is working with no errors, but I'm not getting the desired functionality.

I have a cars database with two tables: one for makes which includes two columns manufactureID and manufactureBrand

The second table is models: which includes modelID, model, manufactureID(FK), and modelYear.

Here is my cfc:

<cfcomponent output="false">
<!--- Set datasource --->
<cfset THIS.dsn="rlbulbs">

<!--- Function to get data from datasource --->
<!--- Get array of car manufactures --->
<cffunction name="getMakes" access="remote" returntype="array">
  <!--- Set variables --->
  <cfset var data="">
  <cfset var result=ArrayNew(2)>
  <cfset var i=0>
  
  <!--- Query DB --->
  <cfquery name="data" datasource="#THIS.dsn#">
		SELECT manufactureID, manufactureBrand
		FROM manufacture 
        ORDER BY manufactureBrand
      </cfquery>
  <!--- loop through makes and convert to array--->
  <cfloop index="i" from="1" to="#data.RecordCount#">
    <cfset result[i][1]=data.manufactureID[i]>
    <cfset result[i][2]=data.manufactureBrand[i]>
  </cfloop>
  
  <!--- Return results --->
  <cfreturn result>
</cffunction>

<!--- Get Models by Make --->
<cffunction name="getModels" access="remote" returnType="array">
  <cfargument name="manufactureID" type="numeric" required="true">
  
  <!--- Get data --->
  <cfquery name="data" datasource="#THIS.dsn#">
        	SELECT modelID, models
            FROM models
            WHERE manufactureID = #ARGUMENTS.manufactureID#
            ORDER BY models      
        </cfquery>
  
  <!--- Convert to Array --->
  <cfloop index="i" from="1" to="#data.RecordCount#">
    <cfset result[i][1]=data.modelID[i]>
    <cfset result[i][2]=data.models[i]>
  </cfloop>
  
  <!--- and return results --->
  <cfreturn result>
</cffunction>

<!--- Get models by year --->
<cffunction name="getYears" access="remote" returnType="array">
  <cfargument name="modelID" type="numeric" required="true">
  
  <!--- Get Data --->
  <cfquery name="data" datasource="#THIS.dsn#">
        	SELECT modelID, modelYear
            FROM models
            <!--- WHERE modelYear = #ARGUMENTS.modelID# --->
            GROUP BY modelYear
            
        </cfquery>
  
  <!--- Convert to Array --->
  <cfloop index="i" from="1" to="#data.RecordCount#">
    <cfset result[i][1]=data.modelID[i]>
    <cfset result[i][2]=data.modelYear[i]>
  </cfloop>
  
  <!--- and return results --->
  <cfreturn result>
</cffunction>
</cfcomponent>

and my cfm:

<cfform>
  <table>
    <tr>
      <td>Select Your Make:</td>
      <td><cfselect name="manufactureID"
                	bind="cfc:cars.getMakes()"
                	bindonload="true" /></td>
    </tr>
  <tr>
    	<td>Select Your Year</td>
        <td><cfselect name="modelYear"
        			  id="modelYear"
        			  bind="cfc:cars.getYears({modelID})" />
         </td>
    </tr>
    <tr>
    	<td>Select Your Model</td>
        <td><cfselect name="modelID"
        			  bind="cfc:cars.getModels({manufactureID})" /></td>
    </tr>
  </table>
</cfform>

As I said, the select lists populate fine, and when I select another make, the models populate based on the make selected. My problem is when I try to select a Year, the models dont change based on that selection. Any help would be appreciated. Thanks!

[jayst19143]

Where is your CFIDE Dir ? If you're on a Windows machine go into IIS and make sure you have a Virtual Directory setup pointing to your CFIDE folder which should be in C:\Coldfusion#\...

[teedoff]

Where is your CFIDE Dir ? If you're on a Windows machine go into IIS and make sure you have a Virtual Directory setup pointing to your CFIDE folder which should be in C:\Coldfusion#\...

Thanks for the reply, though not sure what that has to do with my issue.

[arrgh]

My problem is when I try to select a Year, the models dont change based on that selection

If that's the desired result, your binds are backward. Year is bound to model. So when the year list changes when a model is selected, not the other way around.

[teedoff]

If that's the desired result, your binds are backward. Year is bound to model. So when the year list changes when a model is selected, not the other way around.

Yes thats true. I've also tried it, I think, with binding the model to the year. This only returns one model for each year in the select list. Like so:

<cfcomponent>

<cfset THIS.dsn = "rlbulbs">

<cffunction name="getYear" access="remote" returntype="array">
      
  <!--- Set variables --->
  <cfset var data="">
  <cfset var result=ArrayNew(2)>
  <cfset var i=0>
  <cfquery name="data" datasource="#THIS.dsn#">
      	SELECT  modelID, modelYear
        FROM models
        GROUP BY modelYear
      </cfquery>
  <cfloop index="i" from="1" to="#data.RecordCount#">
  	<cfset result[i][1]=data.modelID[i]>
	<cfset result[i][2]=data.modelYear[i]>
  </cfloop>
  <cfreturn result>
</cffunction>

<cffunction name="getModels" access="remote" returntype="array">
<cfargument name="modelID" type="numeric" required="true">
	
    <cfquery name="data" datasource="#THIS.dsn#">
    	SELECT modelID, models, modelYear
        FROM models
        WHERE modelID ='#ARGUMENTS.modelID#'
        ORDER BY models
    </cfquery>

	<cfloop index="i" from="1" to="#data.RecordCount#">
  	<cfset result[i][1]=data.modelID[i]>
	<cfset result[i][2]=data.models[i]>
  </cfloop>
  <cfreturn result>
</cffunction>
</cfcomponent>

Then my select lists are:

<cfform>
<table>
	<tr>
    	<td>Select Your Year: </td>
        <td><cfselect
        		name="modelYear"
                bind = "cfc:car3.getYear()"
                bindonload="true" />
        </td>
    </tr>
    <tr>
    	<td>Select Your Model: </td>
        <td><cfselect
        		name="models"
                bind = "cfc:car3.getModels({modelYear})" />
        </td>
    </tr>
</table>
</cfform>

If I leave out the WHERE clause in getModels() then ALL models are returned no matter what year is selected and the query runs very slow, almost locking up my browser.

I had this working fine with just two select lists for manufacture and models. I could bind the models to the manufactureID and return the models for each manufacture. When i threw the years into the mix it all went to crap..lol

[arrgh]

> cfc:car3.getModels( {modelYear} )
> WHERE modelID = '#ARGUMENTS.modelID#'

It's because the getModel query doesn't make any sense ;) You're passing in a "year" number, but the query is treating it as a modelID. If you want the models for a specific manufacturer AND year, you need to pass in both values. Then filter on the right columns.

[teedoff]

If that's the desired result, your binds are backward. Year is bound to model. So when the year list changes when a model is selected, not the other way around.

That makes perfect sense, but when I try to bind the model to the year, i cant get it to work at all.

This function tries to pass in the modelYear argument, but throws an error:

<!--- Get Models by Make --->
<cffunction name="getModels" access="remote" returnType="array">
  <cfargument name="modelYear" type="string" required="true">
  
  <!--- Get data --->
  <cfquery name="data" datasource="#THIS.dsn#">
        	SELECT modelID, models
            FROM models
            WHERE modelYear = #ARGUMENTS.modelYear#
            ORDER BY models      
        </cfquery>
  
  <!--- Convert to Array --->
  <cfloop index="i" from="1" to="#data.RecordCount#">
    <cfset result[i][1]=data.modelID[i]>
    <cfset result[i][2]=data.models[i]>
  </cfloop>
  
  <!--- and return results --->
  <cfreturn result>

While this same function with manufactureID as the argument works fine:

<!--- Get Models by Make --->
<cffunction name="getModels" access="remote" returnType="array">
  <cfargument name="manufactureID" type="numeric" required="true">
  
  <!--- Get data --->
  <cfquery name="data" datasource="#THIS.dsn#">
        	SELECT DISTINCT models, modelID 
            FROM models
            WHERE manufactureID = #ARGUMENTS.manufactureID#
            ORDER BY models      
        </cfquery>
  
  <!--- Convert to Array --->
  <cfloop index="i" from="1" to="#data.RecordCount#">
    <cfset result[i][1]=data.modelID[i]>
    <cfset result[i][2]=data.models[i]>
  </cfloop>
  
  <!--- and return results --->
  <cfreturn result>
</cffunction>

And my cfselect:

<cfselect name="modelYears"
				  bindonload="true" 
				  bind="cfc:data.getYears()" >
			
		</cfselect>
		
		<cfselect name="models"
				  bindonload="true" 
				  bind="cfc:data.getModels({modelYear})" >			
		</cfselect>

This produces several errors:

Bind Element not found: modelYear
Error invoking CFC: variable Result is undefined.

I got sidetracked with another project for the last few weeks, and just now got back to this one monday. Any help is greatly appreciated!

[arrgh]

cfselect name="modelYears"
bind="cfc:data.getModels({modelYear})" >

The bind parameter doesn't match the name of your form field. It should be plural ie modelYears

Binding is harder to debug than a regular query. Take it in steps. Always run cfc code manually first. If the cfc doesn't work by itself, it wont' work in a bind either. The problem will just be harder to find ;-)

http://www.yoursite.com/data.cfc?method=getModels&modelYears=2012

Once it's working enable ajax debugging in the cf admin to see the data being sent back and forth