cfselect and bind

[wam4]

I have 2 cfselects that I want related. I'm getting the error "Bind Failded for select box, columns matching the value or display attributes specified on the cfselect tag are not present in the query. If I comment out the second cfselect, the first cfselect displays correctly with no problem. I've read through the forums and samples and livedocs but I'm still not getting it or missing something.  I do not have a cfinvoke tag but even when I add these I still get the same error.  Do I need a cfajaxproxy bind tag on the form page?  Does anything need to be modified in the coldfusion administrator? I'm on ColdFusion 9.

Here is the form page code:

  <cfselect name="section1"
    bind="cfc:adminfunctions.getSection()"
    bindonload="true"
    value="section" display="section"
   / > 

<br>

  <cfselect name="subsection"
    bind="cfc:adminfunctions.getSkill({section1})"
    value="id" display="subsection"
    bindonload="true"/>

And here's the cfc page titled adminfunctions.cfc

<cfcomponent>

   <cffunction name="getSection" access="remote" returntype="query" output="false" >
   <cfquery datasource="dsn" name="getSections">
            select distinct(section)
            from SurveySkills
            where status = 'A'
            order by section
</cfquery>

   <cfreturn getSections>
  </cffunction>
 
<!---get the skills based on what is returned from the first cfselect on skill.cfm--->
  <cffunction name="getSkill" access="remote" returntype="any" >
   <cfargument name="section"  type="any" required="true">
<!--- Define variables --->
<cfset var data="">

    <cfquery datasource="dsn" name="data">
            select subsection
            from SurveySkills
            where status = 'A'
            and section = '#ARGUMENTS.section#'
             order by section
</cfquery>
  <cfreturn data>

  </cffunction>
</cfcomponent>

[Fernis]

I havent't tested the exact scenario - have only been playing with 2-dimensional arrays when it comes to cfselect binding.

However, it is clearly said in the docs clearly state that:

"If the bind specifies a CFC function, a query, or, if the bind specifies a URL, a JSON representation of a query. The query must include columns whose names are the values of the cfselect tag value and display attributes."

To begin with, you're returning only one column in your query. That could be the cause.

Also, I don't know when binding to a function (and not directly to a query), can you return a query object (maybe you can, haven't tested) or does it have to be a two-dimensional array. You try it out, and let us know?

Edit: Oh, and try returntype="json" with that returned query (adding a second column value to the columns if it doesn't work otherwise)

-Fernis

[wam4]

Thanks Fernis,

My coworker had struggled with the same thing and we came up with a working solution.  And yes, the json is the way to go.  Using bind with URL worked with the returnformat = json.  I was able to get this to work 3 deep in the form drill down.  Below is the working solution.

<!--- For this first select I put the query on the page and not in a cfc just to test but I'm sure it can be moved into the cfc  --->

<cfselect name="section" required="yes" message="Please Choose a Section">

     <option value="#section#">Section #section# - #wording#</option>

</cfselect>

<!---  2nd select --->

<!---  this one pulls from the cfc - adminfunctions which will be listed below for others --->

<cfselect name="category" bind="url:adminfunctions.cfc?method=getCategory&returnFormat=json&section={section.value}" bindonload="true" />

<!--- 3rd bound select  --->
  <cfselect name="subcategory" bind="url:adminfunctions.cfc?method=getSubCategory&returnFormat=json&category={category.value}" bindonload="true" />

<! ---   This is the adminfunctions.cfc page  --->

<cfcomponent>

<!---get the skills based on the section returned from the first cfselect on skill.cfm--->
  <cffunction name="getCategory" access="remote" returntype="array" >
   <cfargument name="section"  type="any" required="yes">

  <!--- Define variables --->
    <cfset var data="">
      <cfset var result=ArrayNew(2)>
      <cfset var i=0>

    <cfquery datasource="#dsn#" name="data">
   select  distinct(category) as category, section
   from SurveySkills
   where status = 'A'
   and section = '#ARGUMENTS.section#'
   order by category
   </cfquery>
<!--- Convert results to array --->
      <cfloop index="i" from="1" to="#data.RecordCount#">
     <cfset result[1]=data.category>
        <cfset result[2]=data.category>
      </cfloop>
  
<cfreturn result>
  </cffunction>
 
<!--- ************  Get the subcategory **************  --->
  <cffunction name="getSubCategory" access="remote" returntype="array" >
   <cfargument name="category"  type="any" required="yes">

  <!--- Define variables --->
    <cfset var data="">
      <cfset var result=ArrayNew(2)>
      <cfset var i=0>

    <cfquery datasource="#dsn#" name="data">
   select  distinct(subcategory) as subcategory, section
   from SurveySkills
   where status = 'A'
   and category = '#ARGUMENTS.category#'
   order by subcategory
  </cfquery>
 
<!--- Convert results to array --->
      <cfloop index="i" from="1" to="#data.RecordCount#">
     <cfset result[1]=data.subcategory>
        <cfset result[2]=data.subcategory>
      </cfloop>
  
<cfreturn result>
  </cffunction>

</cfcomponent>