Home » ColdFusion Ajax Tutorial 2: Related Selects
Many of us have built related select controls, forms with two (or more) drop down
Select Media Type:
Select Art:
The form contains two controls, one named “mediaid” and the other named “artid”.
“mediaid” is bound to cfc:art.getMedia(), and so to obtain the list of media types to populate the control, the client makes an asynchronous call to the getMedia method in art.cfc, and populates the list with the returned array. As we’d want this control to be automatically populated when the form loads, bindonload is set to “true", this way the getMedia() call is fired automatically at form load time.
“artid” is bound to the getArt method in art.cfc. This method requires that a mediaid be passed to it, and so {mediaid} is used so as to pass the currently selected value of control mediaid (the first ). Because these two controls are bound together, the second dependant on the first, ColdFusion automatically generates JavaScript code that forces artid to be repopulated with newly retrieved data whenever mediaid changes.
This example binds just two controls, but this mechanism can be used to relate as many controls as needed, and not just controls either.
164 responses to “ColdFusion Ajax Tutorial 2: Related Selects”
Ken Hobbs
This post is following a couple different threads. The one that brought me here is the inability to select an option with a bind expression. I solved the issue for my app by setting the bindonload attribute to "no" and initially populate the select options on my own. I’ve pasted a little bit of code below as an example.
<cfselect bind="cfc:components.ui.getClientProjectsForSelect({clientID})" bindonload="no" name="projectID" id="projectID" style="width: 200px;">
<cfif Task.taskID eq 0>
<option value="0" selected>No project</option>
<cfelseif Task.taskID gt 0>
<option value="0"<cfif Task.projectID eq 0> selected</cfif>>No project</option>
<cfloop query="ClientProjects">
<option value="#pro_projectid#"<cfif Task.projectID eq pro_projectid> selected</cfif>>#pro_name#</option>
</cfloop>
</cfif>
</cfselect>
Further to Jared’s solution to the issue of the error "window:global: Exception thrown and not caught".
His solution worked nicely… in my case I initially put my CFC outside of the directory containg my application.cfm file to avoid the content created by my application.cfm. However I wanted to put the CFC within my application’s directory and not have to add extra logic to application.cfm to prevent it from outputting any content when being called by a CFC. Then the answer struck me.
At the start of the method within my CFC that returns the dynamic data to the AJAX call I simply added the following:
<cfcontent type="text/html" reset="yes">
and now it works just fine. The "reset=yes" clears the pre-existing page content without needing to add any logic to your application.cfm/cfc to do the same.
The solution is essentially the same, just a bit less complicated to apply.
To add a default value to the cfselect try modifying your cfc as such:
<cfset result[1][1]="">
<cfset result[1][2]="———">
<cfloop index="i" from="1" to="#data.RecordCount#">
<cfset result[i+1][1]=data.artID[i]>
<cfset result[i+1][2]=data.artName[i]>
</cfloop>
This will append a default selection to the cfselect
I have this example working on IE, but not Firefox. Anyone else with that problem? I get this error from Firefox: Bind failed, element not found. Works great in IE, though.
Hi Daniel,
I too am having the same issue.
I had an existing form and wanted to add the selects to it.
once i added a set of WORKING cfselects, now i get the bind error element not found but only in FF and only on the existing form. There is something on that page that is causing this issue but for the life of me I can not find it.
Here is the form witch works on a stand alone page with nothing else.
I tried to debug it with FireBug but no luck
<cfform action="#cgi.script_name#" method="post" name="styleEditForm" id="styleEditForm">
<input type="hidden" name="StyleID" value="1">
<tr>
<td><b>Style Name:</b></td>
<td><input name="StyleName" class="forms" size=40 value=""></td>
</tr>
<tr>
<td><b>Category</b></td>
<td>
<table>
<tr><td><b>Category</b></td><td><b>Sub Category</b></td><td><b>Sub Style</b></td></tr>
<tr><td>
<cfselect class="forms" name="catlist" bind="cfc:cfc.cats.getcats()" bindonload="true" display="CategoryName" value="categoryID" size=5>
<option value="0">–categories–</option>
</cfselect></td>
<td><cfselect class="forms" name="subcatlist" bind="cfc:cfc.cats.getSubcats({catlist.value})" size=5 display="SubCategoryName" value="SubcategoryID">
<option value="0">–Choose a category–</option>
</cfselect></td>
<!— <td><cfselect class="forms" name="substyle" bind="cfc:cfc.cats.getStyles({subcatlist.value})" size=5 display="Name" value="ID">
<option value="0">–Choose a Sub Style–</option>
</cfselect></td> —>
</tr>
</table>
</td>
</tr>
<tr>
<td><b>Active?</b></td>
<td><input type="checkbox" name="Active" value="1" ></td>
</tr>
<tr>
<td> </td>
<td><input type="submit" name="Action" value="Update" class="forms"> <input type="submit" name="Action" value="Remove" class="forms"> <input type="button" value="Cancel" class="forms" onClick="location.href=’styles_add.cfm’"></td>
</tr>
</cfform>
Hi Daniel,
if you are still searching for the issue I found it by more searching.
basically if you have a cfform inside of a table it fails in FF.
more the cfform outside of your tables.
Tim
Tim,
The form is in a table on my page as well. Thanks for figuring that out. That is completely weird. I have no idea how a table would present a problem, and probably never would have figured that one out!
I get "ColdFusion is undefined" JS error as soon as I take the files out of wwwroot and put them somewhere else. Anyone know what that’s all about? Probably something easy. I guess it’s not finding CFIDE or something.
How would the selects be adapted to work in an edit form, where you want to have prepopulated fields, including prepopulated values of the related selects?
I am losing my mind trying the make a simple ajax-driven multiple select exercise work. I am parsing a flat text file, placing the results I want to keep in a 2D array. Regardless of the several hundred variations I try I ALWAYS get:
"Bind failed for select box siteInfo, bind value is not a 2D array or valid serialized query"
…However, a "cfinvoke" immediately above the cfform/cfselect clear returns the result I expect.
Here is the code:
(CFC first)
<cfcomponent output="false">
<cffunction name="getSites"
access="remote"
returntype="string"
hint="Get list of web sites">
<cfset domains=ArrayNew(2)>
<cfset x=2>
<cfset domains[1][1]="Name">
<cfset domains[1][2]="Value">
<cfloop index="record" file="c:templivesites.txt">
<cfif (left(record,3) is "www")>
<cfset domains[x][1] = rereplace(record, "www.([w-_]+?)..*?STARTED.*", "1")>
<cfset domains[x][2] = domains[x][1]>
<cfset x = x + 1>
</cfif>
</cfloop>
<cfreturn SerializeJSON(domains)>
</cffunction>
<cffunction name="getRevisions"
access="remote"
returnType="string"
hint="Get list of web site revisions">
<cfset var revs="">
<cfquery name="revs" datasource="somewhere">
select revs from somewhere
</cfquery>
<cfreturn revs>
</cffunction>
</cfcomponent>
(Now the CFM)
<cfform name="test">
<table>
<tr>
<th colspan="2">
Select a site, then a revision
</th>
</tr>
<tr>
<td>Site:<br>
<cfselect name="siteInfo"
bind="siteInfo.getSites()"
bindonload="true" />
</td>
<td>
<td>Revision:<br>
<cfselect name="revision"
bind="siteinfo.getRevisions()"
display="revs"
value="revs" />
</td>
</tr>
<tr>
<td colspan="2">
<input type="submit" value="Switch Revision">
</td>
</tr>
</table>
</cfform>
"livesites.txt" contains the output from an "iisweb /query" command that provides a list of sites on a particular server, their status and IP address(es). My regex parses out the one piece I want: the domain name. The output from the "cfinvoke" confirms the array is being populated correctly. The getRevisions() method is stubbed out at this point, and the issue I’m seeing exists even if I completely remove that portion of the code.
I have tried everything I can think of from the rational to irrational to get this working. It’s loosely based on the CF Getting Started example on page 363 of the CF8 book Vol 1. I’m trying to use an array rather than a query, which I thought works.
Any help would be appreciated greatly, I’ve already spent too much time monkeying with this problem.
Thanks in advance,
Laker
Oops! Realized I posted some errant code. Here is the correct (original) getSites() function. (I’ve been making so many changes, I lost track for a moment):
<cffunction name="getSites"
access="remote"
returntype="array"
hint="Get list of web sites">
<cfset domains=ArrayNew(2)>
<cfset x=2>
<cfset domains[1][1]="Name">
<cfset domains[1][2]="Value">
<cfloop index="record" file="c:templivesites.txt">
<cfif (left(record,3) is "www")>
<cfset domains[x][1] = rereplace(record, "www.([w-_]+?)..*?STARTED.*", "1")>
<cfset domains[x][2] = domains[x][1]>
<cfset x = x + 1>
</cfif>
</cfloop>
<cfreturn domains>
</cffunction>
Ben,
As usual, you are the MAN! Thanks for this quick and easy example. Do you have any idea how many times you’ve helped me with ColdFusion over the years! More than I can count!
I think, for this to be really useful, we need to know how to be able to use this on an edit form, where previously selected list values need to be selected. This all works fine if it’s a form the user will see only once, but in the circumstance of say, a user profile that can be edited, this solution falls well short.
Ok im having a hell of a time trying to get my third select box to work in IE. it works fine in FF but it wont load the values in the the box. When i run the ajax debugger it shows the values getting loaded to the form but they dont show up. has any one had a similar problem.
Here is my CFC
<cfcomponent output="false">
<cfset THIS.dsn="dbMODE">
<!— Get array of media types —>
<cffunction name="country" access="remote" returnType="array">
<!— Define variables —>
<cfset var data="">
<cfset var result=ArrayNew(2)>
<cfset var i=0>
<!— Get data —>
<cfquery name="data" datasource="#THIS.dsn#">
SELECT DISTINCT country
FROM plant
ORDER BY country
</cfquery>
<!— Convert results to array —>
<cfloop index="i" from="1" to="#data.RecordCount#">
<cfset result[i][1]=data.country[i]>
<cfset result[i][2]=data.country[i]>
</cfloop>
<cfcontent type="text/html" reset="yes">
<!— And return it —>
<cfreturn result>
</cffunction>
<!— Get art by media type —>
<cffunction name="state" access="remote" returnType="array">
<cfargument name="country" required="true">
<!— Define variables —>
<cfset var data="">
<cfset var result=ArrayNew(2)>
<cfset var i=0>
<!— Get data —>
<cfquery name="data" datasource="#THIS.dsn#">
SELECT DISTINCT state
FROM plant
WHERE country = "#ARGUMENTS.country#"
ORDER BY state
</cfquery>
<!— Convert results to array —>
<cfloop index="i" from="1" to="#data.RecordCount#">
<cfset result[i][1]=data.state[i]>
<cfset result[i][2]=data.state[i]>
</cfloop>
<cfcontent type="text/html" reset="yes">
<!— And return it —>
<cfreturn result>
</cffunction>
<cffunction name="site" access="remote" returnType="array">
<cfargument name="country" required="true">
<cfargument name="state" required="true">
<!— Define variables —>
<cfset var data="">
<cfset var result=ArrayNew(2)>
<cfset var i=0>
<!— Get data —>
<cfquery name="data" datasource="#THIS.dsn#">
SELECT DISTINCT site
FROM plant
WHERE state = "#ARGUMENTS.state#" AND country = "#ARGUMENTS.country#"
ORDER BY site
</cfquery>
<!— Convert results to array —>
<cfloop index="i" from="1" to="#data.RecordCount#">
<cfset result[i][1]=data.site[i]>
<cfset result[i][2]=data.site[i]>
</cfloop>
<cfcontent type="text/html" reset="yes">
<!— And return it —>
<cfreturn result>
</cffunction>
</cfcomponent>
Here is my form
<cfform>
Select Country:
<cfselect name="country" bind="cfc:components.getmedia.country()" bindonload="true" />
Select State:
<cfselect name="state" bind="cfc:components.getmedia.state({country})" />
Select Site:
<cfselect name="site" bind="cfc:components.getmedia.site({country@none},{state})" />
</cfform>
If anyone can help me out i would really appreciate it im getting very frustrated.
Thanks
-Brandon
Ben
I have something really bizarre happening and I really hope you can help. I am able to get the select form to work perfectly when it is not associated with an application.cfc. If I comment out the OnRequestStart and OnRequestEnd (which load the header and footer respectively via cfinclude) the related selects work. However if not commented out they do not. I suspected that something in the header or footer file was the culprit and if I comment out the entire contents header.cfm and footer.cfm it works. But if there is a single character not commented it breaks . I have been pulling out my remaining hair trying to figure this one out. Any help would be much appreciated.
"I have something really bizarre happening and I really hope you can help. I am able to get the select form to work perfectly when it is not associated with an application.cfc. If I comment out the OnRequestStart and OnRequestEnd (which load the header and footer respectively via cfinclude) the related selects work. However if not commented out they do not. I suspected that something in the header or footer file was the culprit and if I comment out the entire contents header.cfm and footer.cfm it works. But if there is a single character not commented it breaks . I have been pulling out my remaining hair trying to figure this one out. Any help would be much appreciated."
This is really bizarre if delete the entire contents of the header and or footer linked via the cfinclude the related selects work. However if I put a single character ie "a" it will break.
"I have something really bizarre happening and I really hope you can help. I am able to get the select form to work perfectly when it is not associated with an application.cfc. If I comment out the OnRequestStart and OnRequestEnd (which load the header and footer respectively via cfinclude) the related selects work. However if not commented out they do not. I suspected that something in the header or footer file was the culprit and if I comment out the entire contents header.cfm and footer.cfm it works. But if there is a single character not commented it breaks . I have been pulling out my remaining hair trying to figure this one out. Any help would be much appreciated."
"This is really bizarre if delete the entire contents of the header and or footer linked via the cfinclude the related selects work. However if I put a single character ie "a" it will break."
cfdebug gives me no errors. It looks like the cfselects are being popluated but the drop down menus are blank.
""I have something really bizarre happening and I really hope you can help. I am able to get the select form to work perfectly when it is not associated with an application.cfc. If I comment out the OnRequestStart and OnRequestEnd (which load the header and footer respectively via cfinclude) the related selects work. However if not commented out they do not. I suspected that something in the header or footer file was the culprit and if I comment out the entire contents header.cfm and footer.cfm it works. But if there is a single character not commented it breaks . I have been pulling out my remaining hair trying to figure this one out. Any help would be much appreciated."
"This is really bizarre if delete the entire contents of the header and or footer linked via the cfinclude the related selects work. However if I put a single character ie "a" it will break."
cfdebug gives me no errors. It looks like the cfselects are being popluated but the drop down menus are blank. "
It doesn’t appear to matter if the cfinclude is local or in the application.cfc. If any cfinclude is included linking to a page with any html content the drop down menus are blank.
??
Thanks Dave but I’m not sure I’ve got it right. It’s still not working. Do you see what might be wrong?
Here is my methods in the cfc
<!— Get SelectA —>
<cffunction name="getselectA" access="remote" returnType="array">
<!— Define variables —>
<cfset var data="">
<cfset var result=ArrayNew(2)>
<cfset var i=0>
<cfcontent type="text/html" reset="yes">
<!— Get data —>
<cfquery name="data" datasource="#ds#">
SELECT distinct SelectAID, SelectAName
FROM TableA
ORDER BY SelectAName
</cfquery>
<!— Convert results to array —>
<cfloop index="i" from="1" to="#data.RecordCount#">
<cfset result[i][1]=data.SelectAID[i]>
<cfset result[i][2]=data.SelectAName[i]>
</cfloop>
<!— And return it —>
<cfreturn result>
</cffunction>
<!— Get SelectB —>
<cffunction name="getSelectB" access="remote" returnType="array">
<!— Define Arguements—>
<cfargument name="SelectAID" type="numeric" required="true">
<!— Define variables —>
<cfset var data="">
<cfset var result=ArrayNew(2)>
<cfset var i=0>
<cfcontent type="text/html" reset="yes">
<!— Get data —>
<cfquery name="data" datasource="#ds#">
SELECT SelectBID, SelectBName
FROM TableB
WHERE SelectAID = #ARGUMENTS.SelectAID#
ORDER BY SelectBName
</cfquery>
<!— Convert results to array —>
<cfloop index="i" from="1" to="#data.RecordCount#">
<cfset result[i][1]=data.SelectBID[i]>
<cfset result[i][2]=data.SelectBName[i]>
</cfloop>
<!— And return it —>
<cfreturn result>
</cffunction>
<!— Get Select C —>
<cffunction name="getSelectC" access="remote" returnType="array">
<!— Define Arguements—>
<cfargument name="SelectAID" type="numeric" required="true">
<!— Define variables —>
<cfset var data="">
<cfset var result=ArrayNew(2)>
<cfset var i=0>
<cfcontent type="text/html" reset="yes">
<!— Get data —>
<cfquery name="data" datasource="#ds#">
SELECT SelectCID, SelectCName
FROM TableC
WHERE SelectAID = #ARGUMENTS.SelectAID#
ORDER BY SelectCName
</cfquery>
<!— Convert results to array —>
<cfloop index="i" from="1" to="#data.RecordCount#">
<cfset result[i][1]=data.SelectCID[i]>
<cfset result[i][2]=data.SelectCName[i]>
</cfloop>
<!— And return it —>
<cfreturn result>
</cffunction>
Here is the form selects
<cfform>
<cfselect name="SelectAID"
bind="cfc:mysite.linked.cfc.mycfc.getselectA()"
bindonload="true"/>
<br />
Select Issue:
<cfselect name="SelectBID"
bind="cfc:mysite.linked.cfc.mycfc.getselectB({SelectAID})"/>
<br />
Select Size:
<cfselect name="SelectCID"
bind="cfc:mysite.linked.cfc.mycfc.getselectC({SelectAID})"/>
<br />
</cfform>
Thanks for any help it’s greatly appreciated!
You also need to prevent your functions from returning anything except the data for the CFSELECT tags, set the function attribute "output" to "no"… e.g.:
<cffunction name="getselectA" access="remote" returnType="array" output="no">
I don’t know if this is helpful to any of you by now or not, but i found that i was looking for too long to find a way to first build the CFSelect with binding and then to find a way to make sure any pre-selected values were still there when I went back to edit the form. Obviously after reading this page and going to links within, you can see that Steve Savage (http://www.realitystorm.com/experiments/coldfusion… ) has a great fix for this issue, but until Adobe accepts it and incorporates it, I don’t want to have to babysit Coldfusion through upgrades to make sure it is still in place. So this is the code I am using until then…
Add Page code:
<!— First Select –
** calls out the cfc in bind parameter (filepath and filename are separated by periods as you do in the component parameter in a cfinvoke and then you also add in the function name and a set of empty parenthesis);
** bindonload set to true;
** value set to value to pass to action and binding query;
** display value that is to show up in the dropdown list. —>
<cfselect name="firstselectname" bind="cfc:cfcfilepath.cfcfilename.cfcfirstfunctionname()" bindonload="true" value="ID_value" display="display_value" />
<!— Second Select –
** calls out the cfc in bind parameter (filepath and filename are separated by periods as you do in the component parameter in a cfinvoke and then you also add in the function name);
** place the first CFSelect’s name in curly brackets inside the parenthesis;
** value set to value to pass to action and binding query;
** display value that is to show up in the dropdown list. —>
<cfselect name="secondselectname" bind="cfc:cfcfilepath.cfcfilename.cfcsecondfunctionname({firstselectname})" value="Sub_ID_value" display="Sub_display_value" />
Edit page code:
<!— First Select –
** calls out the cfc in bind parameter (filepath and filename are separated by periods as you do in the component parameter in a cfinvoke and then you also add in the function name);
** place fieldname from query that sends pre-selected value to cfc argument (SELECTEDID);
** bindonload set to true;
** value set to value to pass to action and binding query;
** display value that is to show up in the dropdown list. —>
<cfselect name="firstselectname" bind="cfc:cfcfilepath.cfcfilename.cfcfirstfunctionname(#qryName.SelectedID#)" bindonload="true" value="ID_value" display="display_value" />
<!— Second Select –
** calls out the cfc in bind parameter (filepath and filename are separated by periods as you do in the component parameter in a cfinvoke and then you also add in the function name);
** place the first CFSelect’s name in curly brackets inside the parenthesis and place fieldname from query that sends pre-selected value to cfc argument (SELECTEDSUBID) separated by a comma and no spaces and MUST be in the same order as you list them as arguments in the CFC;
** value set to value to pass to action and binding query;
** display value that is to show up in the dropdown list. —>
<cfselect name="secondselectname" bind="cfc:cfcfilepath.cfcfilename.cfcsecondfunctionname({firstselectname},#qryName.SelectedSubID#)" value="Sub_ID_value" display="Sub_display_value" />
CFC that takes care of both:
<!— set output to no —>
<cfcomponent output="no">
<!— First function sets up values for your first CFSelect statement; set access to remote; returntype in this case is array —>
<cffunction name="cfcfirstfuntionname" access="remote" returntype="array" hint="first select list – send selectedid if coming from edit page">
<!— if call is coming from an edit page where a value was already pre-selected, it will be passed here —>
<cfargument name="selectedid" default="" required="no">
<!— Define variables and clears them —>
<cfset var qrySelectedID="">
<cfset var qryListWBind="">
<cfset var arList="">
<!— if call from edit page, query to single out pre-selected values —>
<cfif isDefined("arguments.selectedid") and arguments.selectedid neq "">
<cfquery name="qrySelectedID" datasource="#datasource#">
SELECT ID_value, display_value
FROM table_with_first_values
WHERE ID_value = #arguments.selectedid#
</cfquery>
</cfif>
<!— Define values for your array to place in first select—>
<!— if call from edit page, eliminate pre-selected values from the rest of the values with cfif statement in where clause —>
<cfquery name="qryListWBind" datasource="#datasource#">
SELECT ID_value, display_value
FROM table_with_first_values
WHERE <cfif isDefined("arguments.selectedid") and arguments.selectedid neq ""> AND ID_value != #arguments.selectedid#</cfif>
ORDER BY display_value
</cfquery>
<!— Build array for first select. If editing, lists the preselected values first. —>
<cfset arList = arraynew(2)>
<cfif isDefined("arguments.selectedid") and arguments.selectedid neq "">
<cfset arList[1][1] = qrySelectedID.ID_value>
<cfset arList[1][2] = qrySelectedID.display_value>
<cfset arList[2][1] = 0>
<cfset arList[2][2] = ‘*SELECT ONE*’>
<cfelse>
<cfset arList[1][1] = 0>
<cfset arList[1][2] = ‘*SELECT ONE*’>
</cfif>
<cfloop from="1" to="#qryListWBind.recordcount#" index="i">
<cfif isDefined("arguments.selectedid") and arguments.selectedid neq "">
<cfset x = i + 2>
<cfelse>
<cfset x = i + 1>
</cfif>
<cfoutput>
<cfset arList[x][1] = qryListWBind.ID_value[i]>
<cfset arList[x][2] = qryListWBind.display_value[i]>
</cfoutput>
</cfloop>
<cfreturn arList>
</cffunction>
<!— Second function sets up values for your second CFSelect statement; set access to remote; returntype in this case is array —>
<cffunction name="cfcsecondfuntionname" access="remote" returntype="array" hint="second select list – send selectedsubid if coming from edit page">
<!— the ID_value is automatically passed after you specify what select statement to get the ID_value from. You must list these in the same order you list them in the CFSelect bind () —>
<cfargument name="ID_value" type="numeric" required="yes">
<!— if call is coming from an edit page where a value was already pre-selected, it will be passed here —>
<cfargument name="selectedsubid" default="" required="no">
<!— Define variables and clears them —>
<cfset var qrySelectedSubID="">
<cfset var qryListWSubBind="">
<cfset var arSublist="">
<!— if call from edit page, query to single out pre-selected values —>
<cfif isDefined("arguments.selectedsubid") and arguments.selectedsubid neq "">
<cfquery name="qrySelectedSubID" datasource="#datasource#">
SELECT Sub_ID_value, Sub_display_value
FROM table_with_second_values
WHERE Sub_ID_value = #arguments.selectedsubid#
</cfquery>
</cfif>
<!— Define values for your array to place in second select—>
<!— if call from edit page, eliminate pre-selected values from the rest of the values with cfif statement in where clause —>
<cfquery name="qryListWSubBind" datasource="#datasource#">
SELECT lsi.Sub_ID_value, lsi.Sub_display_value, lsi.FKfromFirstTable
FROM table_with_first_values li INNER JOIN
table_with_second_values lsi ON li.ID_value = lsi.FKfromFirstTable
WHERE lsi.FKfromFirstTable = <cfqueryparam value="#arguments.ID_value#" cfsqltype="cf_sql_integer"><cfif isDefined("arguments.selectedsubid") and arguments.selectedsubid neq ""> AND lsi.Sub_ID_value != #arguments.selectedsubid#</cfif>
ORDER BY lsi.FKfromFirstTable, lsi.Sub_display_value
</cfquery>
<!— Build array for first select. If editing, lists the preselected values first unless you choose something else in the first select list. —>
<cfset arSublist = arraynew(2)>
<cfif isDefined("arguments.selectedsubid") and (arguments.selectedsubid neq "")>
<cfif qrySelectedSubID.recordcount>
<cfset arSublist[1][1] = qrySelectedSubID.Sub_ID_value>
<cfset arSublist[1][2] = qrySelectedSubID.Sub_display_value>
<cfset arSublist[2][1] = 0>
<cfset arSublist[2][2] = ‘*SELECT NEXT*’>
<cfelse>
<cfset arSublist[1][1] = 0>
<cfset arSublist[1][2] = ‘*SELECT NEXT*’>
</cfif>
<cfelse>
<cfset arSublist[1][1] = 0>
<cfset arSublist[1][2] = ‘*SELECT NEXT*’>
</cfif>
<cfloop from="1" to="#qryListWSubBind.recordcount#" index="i">
<cfif isDefined("arguments.selectedsubid") and arguments.selectedsubid neq "">
<cfif qrySelectedSubID.recordcount>
<cfset x = i + 2>
<cfelse>
<cfset x = i + 1>
</cfif>
<cfelse>
<cfset x = i + 1>
</cfif>
<cfset arSublist[x][1] = qryListWSubBind.Sub_ID_value[i]>
<cfset arSublist[x][2] = qryListWSubBind.Sub_display_value[i]>
</cfloop>
<cfreturn arSublist>
</cffunction>
</cfcomponent>
Sorry so long. Hope it helps. I put in hints along the way to describe everything. I like detail. 🙂
Found another work around to the issue of Application and OnRequest End causing trouble for the binds (i.e. the Exception Thrown but not caught error)
It might not be ellegant, but it was a split second fix, so maybe someone will get some value from this. I tossed a blank ‘Application.cfm’ and ‘OnRequestEnd.cfm’ file into my cfc directory. This prevents CF climbing up to my site root and pulling in the actuall Application.cfm file I’m using.
Hope this helps.
Hi, That Actually Worked. Just I changed the Location of Cfc outside the folder and it worked. I tend to think why coldfusion behave so awkward.
Regards http://www.randhawaworld.com
Ben, I read through all the post and have seen a huge amount of Thank You’s. So seriously, thank you for this Tutorial. It is huge and was I was able to implement this whole concept into my site. I have totaly cut down on the amount of code and I have been able to call the same cfc from multiple form pages. Appreciate it very much.
I do have two question for everyone………….
1) Did anyone find a fix for the speed issues when binding to cfc.
I am currently doing this in my cfselect
bind=cfc:getmethod.getSeries({Sel_getMakes@blur},{Sel_getYears@none})"
This works really well, but very slow, especially when having the enduser step through each CFselect form field.
I tested the cfinvoke and it is fast, I also looked at passing the url, but not sure how to pass url with bind and still refrence the other cfselects data.
any examples on url with refrence to other cfselects?
2. Anybody know how to, or know where I could look on getting a spinning icon next to each cfselect that is waiting on the return data. I have seen this on googles website, but can not seem to find an easy tutorial on how to accomplish this. I have the image I want to use, just need to write a script which sinces the data loaded into the CFselect.
Thanks to everyone that has contributed on this blog. I have reeped much knowledge from everyone.
Thank you for very useful post !!!
When I try to create a similar example using my own tables I receive a Java Script alert message that says "Bind Failed for Select Box because bind value is not a 2D array or serialized query". However, when I try to dump the array variable, it looks absolutely fine to me. Has any one had a similar problem? Any suggestions on a possible cause?
that one appears to be a 2D array, but you are calling out the description twice instead of an ID for the description. The id is typically what you would bind to in the next array to make your bingings talk to each other.
Also, I need to first be able to display a single cfselect using a component method bind before i start thinking about binding multiple cfselects, which is my eventual intent.
I looked over your code and was able to duplicate your error and from what you have so far all you are missing is the "cfc:" and the "()". See how it should read below…
bind="cfc:application.GetCategories()"
Hallo, I made related selects with your help. You can see in action on http://www.agriturismoincampania.it. I have only problem to display second related select on explorer 6 onloading…..
Could someone suggests why do this?
Ben, thank you so much for this tutorial! It’s been very helpful. I think I’m allllmost there, but I’m still getting a binding error, and am not sure why. The cfdebug output looks like my binding is happening AFTER I get the binding error.
.cfm
<cfselect name="type_id" bind="cfc:calls.call_types()" bindOnLoad="true" />
<cfselect name="concern_id" bind="cfc:calls.get_concerns({type_id})" />
calls.cfc
<cfcomponent>
<cfset dsn = ‘admi-prod’>
<!— get types of calls —>
<cffunction name="call_types" access="remote" returntype="array">
<cfset var result = arrayNew(2)>
<cfset var data = querynew("concern_type, type_id")>
<cfcontent type="text/html" reset="yes">
<cfset queryAddRow(data)>
<cfset querySetCell(data,"concern_type", "Special Projects")>
<cfset querySetCell(data, "type_id", 16)>
<cfset queryAddRow(data)>
<cfset querySetCell(data,"concern_type", "QA")>
<cfset querySetCell(data, "type_id", 36)>
<cfquery name="data2" dbtype="query">
SELECT concern_type, type_id
FROM data
</cfquery>
<cfloop index="i" from="1" to="#data2.RecordCount#">
<cfset result[i][1] = data2.type_id[i]>
<cfset result[i][2] = data2.concern_type[i]>
</cfloop>
<cfreturn result>
</cffunction>
<cffunction name="get_concerns" access="remote" returntype="array">
<cfargument name="type_id" type="numeric" required="yes">
<cfset var data = "">
<cfset var result = arrayNew(2)>
<cfset var i = 0>
<cfquery name="data" datasource="#dsn#">
SELECT concern, concern_id
FROM pbr_concerns_masters
WHERE concern_type = #arguments.type_id#
ORDER BY concern
</cfquery>
<cfloop index="i" from="1" to="#data.RecordCount#">
<cfset result[i][1] = data.concern_id[i]>
<cfset result[i][2] = data.concern[i]>
</cfloop>
<cfreturn result>
</cffunction>
</cfcomponent>
cfdebug:
info:bind: Assigned bind value: ’16,Special Projects,36,QA’ to type_id.value
info:http: CFC invocation response: [[16,"Special Projects"],[36,"QA"]]
error:bind: Bind failed, element not found: type_id
Maybe the bind value is wrong? Maybe it’s binding to that entire list instead of just the numbers?
Thanks Ben for this: makes me start in minutes !
Default values related tip.
Well, a a *very simple* trick to populate default value for the first CFselect, rather than adding values to the Query and then re-sort it, if found cleaner to deal with the Array.
So there it is (note that query and columns name has been changed, for my use only) :
In the CFC’s first function (related to the firs cfselect)
<!— Add an Array default Value —>
<cfset result[1][1]=0>
<cfset result[1][2]="— Any type —">
<!— Add QUERY results to the array —>
<cfloop index="i" from="1" to="#infoType.RecordCount#">
<cfset result[i+1][1]=infoType.type_id[i]>
<cfset result[i+1][2]=infoType.type_libelle[i]>
</cfloop>
One thing you should notice is "result[i+1]", as I first inserted 1 row.
Should you insert more than 1 row, then adapt it to the proper value (ad try to deal with multiple default selected values …).
Right, so the default value is at top position in my list, with value "0".
Then, I have to setup an exception rule in my query :
In the second CFC’s function (related to the second Cfselect):
<cfquery name="getInformations" datasource="#this.DSN#">
SELECT *
FROM informations
<cfif ARGUMENTS.infoType NEQ 0>
WHERE info_type_id = #ARGUMENTS.infoType#
</cfif>
ORDER BY info_dateDebutPub
</cfquery>
DBA’s will shout about the "ugly joker", so just ask for whatever you need in the query; just testing here ;-).
That’s it you’re set up for two related cfselect w/default value
I’ve been working on a project that will fill in the adress information on a form when a user is selected from a select box. Since for my purposes I can use the same query, is there a way run the query one time and use the results in each function.
For a better idea of what I’m trying to do, please visit this link: http://www.cfdeveloper.co.uk/forum/forum_posts.asp?TID=659&TPN=1&get=last#2159
Thanks!!!
I’m taking the code in the example provided and modifying it to be a three select. The third query is taking 2 parameters. When I load the page the following error is thown:
Error invoking CFC /stclive/network.cfc: Internal Server Error [Enabling debugging by adding ‘cfdebug’ to your URL parameters to see more information]. This error pops up twice; however, when the page finishes loading the selects work. When an option from the first select is picked the same error is thown but only once.
Checking the CF Administrator the following entry is found in the application logs:
The BLADE argument passed to the getPort function is not of type numeric.If the component name is specified as a type of this argument, its possible that a definition file for the component cannot be found or is not accessible. The specific sequence of files included or processed is: E:Inetpubwwwstclivenetwork.cfc, line: 66.
I have verified that the datatype in the query matches the datatype in the table/column.
Code listed below:
Select Switch:
<cfselect name="switchid"
bind="cfc:network.getSwitch()"
bindonload="true" />
Select Blade:
<cfselect name="blade"
bind="cfc:network.getBlade({switchid})" />
Select Port:
<cfselect name="port"
bind="cfc:network.getPort({switchid},{blade})" />
<cfcomponent output="false">
<cfset THIS.dsn="datasource">
<cffunction name="getSwitch" access="remote" returnType="array">
<!— Define variables —>
<cfset var data="">
<cfset var result=ArrayNew(2)>
<cfset var i=0>
<!— Get data —>
<cfquery name="data" datasource="#THIS.dsn#" CACHEDWITHIN="#CreateTimeSpan(0,0,2,0)#">
Select switchid,ipaddress
from resnet_switches
order by ipaddress
</cfquery>
<!— Convert results to array —>
<cfloop index="i" from="1" to="#data.RecordCount#">
<cfif i IS "1">
<!— Set the display of the first value so the select displays empty set —>
<cfset result[1][1]= "9999">
<cfset result[1][2]= "">
<cfelse>
<cfset result[i][1]=data.switchid[i]>
<cfset result[i][2]=data.ipaddress[i]>
</cfif>
</cfloop>
<!— And return it —>
<cfreturn result>
</cffunction>
<cffunction name="getBlade" access="remote" returnType="array">
<cfargument name="switchid" type="numeric" required="true">
<!— Define variables —>
<cfset var data="">
<cfset var result=ArrayNew(2)>
<cfset var i=0>
<!— Get data —>
<CFQUERY NAME="data" DATASOURCE="#THIS.dsn#" CACHEDWITHIN="#CreateTimeSpan(0,0,2,0)#">
Select distinct(blade)
from resnet_portinfo
where switchid= #switchid#
order by blade
</CFQUERY>
<!— Convert results to array —>
<cfloop index="i" from="1" to="#data.RecordCount#">
<cfif i IS "1">
<!— Set the display of the first value so the select displays empty set —>
<cfset result[1][1]= "9999">
<cfset result[1][2]= "">
<cfelse>
<cfset result[i][1]=data.blade[i]>
<cfset result[i][2]=data.blade[i]>
</cfif>
</cfloop>
<!— And return it —>
<cfreturn result>
</cffunction>
<!— Get art by media type —>
<cffunction name="getPort" access="remote" returnType="array">
<cfargument name="switchid" type="numeric" required="true">
<cfargument name="blade" type="numeric" required="true">
<!— Define variables —>
<cfset var data="">
<cfset var result=ArrayNew(2)>
<cfset var i=0>
<!— Get data —>
<cfquery name="data" datasource="#THIS.dsn#" CACHEDWITHIN="#CreateTimeSpan(0,0,2,0)#">
Select distinct port
from resnet_portinfo
where
switchid= #switchid#
and
blade= #blade#
order by port
</cfquery>
<!— Convert results to array —>
<cfloop index="i" from="1" to="#data.RecordCount#">
<cfset result[i][1]=data.port[i]>
<cfset result[i][2]=data.port[i]>
</cfloop>
<!— And return it —>
<cfreturn result>
</cffunction>
</cfcomponent>
I’m at my wits end, any help in debugging the error would be greatly appreciated.
I had the same problem what i did was wrap the and statement of the where clause in a conditional statement that wouldn’t call this unless the parameter met the proper requirements.
In this case my bind was based on two previously bound selects as well so it appeared it was attempting to bind the 3rd one before the other two had obtained their values…with the conditionals it kept it from throwing the error yet still bound correctly because if for some reason it bound the third two early it will originally get all then once the values are available it will bind with the new arguments.
I was going crazy too wondering how it was passing ""(blank) to the cfc when it should have not bound the 3rd select until the other 2 were bound. This is some code to try in your cfc. Modify as needed.
<cfif isDefined("ARGUMENTS.switch") AND ARGUMENTS.switch NEQ 0 AND ARGUMENTS.switch NEQ "">
AND switch=<cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.switch#" />
</cfif>
<cfif isDefined("ARGUMENTS.blade") AND ARGUMENTS.blade NEQ 0 AND ARGUMENTS.blade NEQ "">
AND blade=<cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.blade#" />
</cfif>
obviously change the cf_sql_integer to match your datatype. Also in firebug does anyone else get extra responses where the third select returns a 401 Unauthorized message twice before successfully binding the information to the element.
Hope this works for you and maybe someone has insight into this 401 response.
Thanks
For the second drop down, try setting bindOnLoad=false. This should prevent it from trying to load data before something is picked in the first drop down.
Got this to work with a URL for my first dropdown, adjusting it to fit the preexisting XML values I’m dealt.
But how would the code or logic be adjusted if the XML data source was separate XML lists from URLs. Example follows.
My problem is figuring out how to take the first selection and use it in the next URL for the next dropdown. This is a twist from having an all-in-one XML list. And the depth of dropdowns goes to about five, but not all options will produce values that deep.
The Example XML:
—– http://fakeserver/sitemap.do?id=1 —– renders this:
<?xml version="1.0" encoding="ISO-8859-1" ?>
<categoryList id="1">
<category id="500">Home</category>
<category id="9">Employment</category>
<category id="61">Education</category>
<category id="113">Housing</category>
<category id="126">Transportation</category>
<category id="137">Health</category>
<category id="164">Benefits</category>
<category id="185">Technology</category>
<category id="192">Community Life</category>
<category id="217">Civil Rights</category>
<category id="6006">National Resource Directory</category>
</categoryList>
—– http://fakeserver/sitemap.do?id=185 —– renders this:
<?xml version="1.0" encoding="ISO-8859-1" ?>
<categoryList id="185">
<category id="5244">State & Local Resources</category>
<category id="186">Accessibility</category>
<category id="187">Assistive & Information Technology</category>
<category id="5048">Federal Accommodations Programs</category>
<category id="5044">Resources & Organizations</category>
<category id="5116">News Archives</category>
<category id="5015">News & Events</category>
<category id="5106">Grants & Funding</category>
<category id="189">Laws & Regulations</category>
</categoryList>
—– http://fakeserver/sitemap.do?id=5244 —– renders this:
<?xml version="1.0" encoding="ISO-8859-1" ?>
<categoryList id="5244">
<category id="5559">Alabama</category>
<category id="5560">Alaska</category>
<category id="5562">Arizona</category>
<category id="5561">Arkansas</category>
<category id="5563">California</category>
</categoryList>
I’ve already requested a change in format to the lists are combined into one, but that’s "in the hopper" for a while.
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept All”, you consent to the use of ALL the cookies. However, you may visit "Cookie Settings" to provide a controlled consent.
This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
Cookie
Duration
Description
cookielawinfo-checkbox-analytics
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional
11 months
The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy
11 months
The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Leave a Reply