Copy link to clipboard
Copied
I read Raymond Camden's blog post about triple related selects and I'm trying to implement a solution of my own. I can only get the first dropdown to populate and am getting three bind errors "Bind Failed, element not found: SelState ..." for the first two and then "Bind Failed, element not found: SelDestination ... " for the last.
My code looks accurate (to me anyway) but clearly its not working.
Can someone please help me debug this?
<cfform name="Localiza">
<table>
<tr>
<td width="100">State:</td>
<td width="150">
<cfselect name="SelState" bind="cfc:_fc_cfc_triple.GetState()"
display="misc_state" value="misc_state" BindOnLoad="true"/></td></tr>
<tr>
<td width="100">County:</td>
<td width="150">
<cfselect name="SelCounty" bind="cfc:_fc_cfc_triple.GetCounty({SelState})"
display="destination_county" value="destination_county" BindOnLoad="false"/></td></tr>
<tr>
<td width="100">Destination:</td>
<td width="150">
<cfselect name="SelDestination" bind="cfc:_fc_cfc_triple.GetDestination({SelState},{SelDestination})"
display="destination_name" value="destination_id" BindOnLoad="false"/></td></tr>
</table>
</cfform>
The form is above, the cfc (_fc_cfc_triple.cfc) is below
<cfcomponent>
<cffunction name="GetState" access="remote" returnType="query">
<cfquery name="LstState" datasource="mydsn">
SELECT misc_state
FROM states_misc
ORDER BY misc_state ASC
</cfquery>
<cfreturn LstState>
</cffunction>
<cffunction name="GetCounty" access="remote" returnType="query">
<cfargument name="misc_state" type="any" required="true">
<cfif #ARGUMENTS.misc_state# EQ "">
<cfset LstCounty="">
<cfelse>
<cfquery name="LstCounty" datasource="mydsn">
SELECT DISTINCT destination_county
FROM destinations
WHERE destination_state = #ARGUMENTS.misc_state#
ORDER BY destination_county ASC
</cfquery>
</cfif>
<cfreturn LstCounty>
</cffunction>
<cffunction name="GetDestination" access="remote" returnType="query">
<cfargument name="misc_state" type="any" required="true">
<cfargument name="destination_county" type="any" required="true">
<cfif #ARGUMENTS.misc_state# EQ "" OR #ARGUMENTS.destination_county# EQ "">
<cfset LstDestination="">
<cfelse>
<cfquery name="LstDestination" datasource="mydsn">
SELECT destination_id,destination_name
FROM destinations
WHERE destination_state = #ARGUMENTS.misc_state#
AND destination_county = #ARGUMENTS.destination_county#
ORDER BY destination_name ASC
</cfquery>
</cfif>
<cfreturn LstDestination>
</cffunction>
</cfcomponent>
Copy link to clipboard
Copied
All that I initially see incorrect is the last cfselect bind, it should be:
<cfselect name="SelDestination" bind="cfc:_fc_cfc_triple.GetDestination({SelState},{SelCounty})"
display="destination_name" value="destination_id" BindOnLoad="false"/>
Copy link to clipboard
Copied
Changed the SelDestination to SelCounty in the last cfselect... Still getting the same three bind failed errors.
Copy link to clipboard
Copied
For the last two selects, if the arguments are empty it will probably fail as the return type is expecting a query object and not a string. You can either change the functions as follows to make them return query objects when the arguments are empty, or make sure that there is no empty value in your State drop-down upon first load.
The code works for me otherwise.
<cffunction name="GetCounty" access="remote" returnType="query">
<cfargument name="misc_state" type="any" required="true">
<cfif #ARGUMENTS.misc_state# EQ "">
<cfset LstCounty = queryNew("destination_county","varchar")>
<cfelse>
<cfquery name="LstCounty" datasource="mydsn">
SELECT DISTINCT destination_county
FROM destinations
WHERE destination_state = #ARGUMENTS.misc_state#
ORDER BY destination_county ASC
</cfquery>
</cfif>
<cfreturn LstCounty>
</cffunction>
<cffunction name="GetDestination" access="remote" returnType="query">
<cfargument name="misc_state" type="any" required="true">
<cfargument name="destination_county" type="any" required="true">
<cfif #ARGUMENTS.misc_state# EQ "" OR #ARGUMENTS.destination_county# EQ "">
<cfset LstDestination = queryNew("destination_id,destination_name","integer,varchar")>
<cfelse>
<cfquery name="LstDestination" datasource="mydsn">
SELECT destination_id,destination_name
FROM destinations
WHERE destination_state = #ARGUMENTS.misc_state#
AND destination_county = #ARGUMENTS.destination_county#
ORDER BY destination_name ASC
</cfquery>
</cfif>
<cfreturn LstDestination>
</cffunction>
Copy link to clipboard
Copied
Lots of cleanup to do... there is blood everywhere from me banging my head on the desk.
Kidding of course. This new change did nothing... still the same three bind errors as my original post.
Copy link to clipboard
Copied
I continue to tinker with this but I cannot understand why all three binds fail. I changed my initial query so that a result has to be displayed and that didn't work.
I'm beginning to wonder if I should abandon my approach and just use the downloaded code from the Jedi's site and fill that .cfm include with real data??
I'd rather not do that. Can anyone see what is wrong with my approach?
Copy link to clipboard
Copied
I'd rather not do that. Can anyone see what is wrong with my approach?
Probably trying to keep all the balls in the air before you've learned how to juggle could be the issue here.
Do this:
* Abandon the code you currently have;
* start again. Write some code that gets a single bind working to spec. Do not worry about your ultimate requirement, just hard-code mark-up & sample data and everything you need to make the code as simple as possible, and as self-contained as possible;
* copy that code, putting the original aside so you can return to it if you need to;
* on the copy of the code, augment it to get the second bind working. Do not think about the third bind until you have the second one working;
* copy that code and save a back-up of it;
* augment the copy to get the third bind working;
* put that code aside;
* apply the techniques that you should now be comfortable with to your actual requirement, implementing one enhancement at a time until it works 100% before moving onto the next bit.
At no point move to the next step before the code in the current step is working 100% and you understand it 100%. At any step you cannot progress, post the code here, post the results of running the code here, post any CF or JS error messages here. The code you post here should be able to be copy and pasted from your post, saved to file(s) and run. So no DB requirements, no calls to methods you don't provide the code for, etc.
--
Adam
Copy link to clipboard
Copied
That's actually not the issue in this case. I already have several related selects on my site.
I wanted to understand why the code I pasted in this thread is not accurate.
Copy link to clipboard
Copied
I have been tinkering with the data that's in the included .cfm file that contains the <cfscript>.
Things are working well for me... However, my last dropdown is the problem child. The reason I wanted a triple select in the first place is that my destinations table (the third dropdown) has over 300k rows. So I can't load it up front in the <cfscript> because it takes way too long.
What can I do? Is there some way I can wait to query the table until the first two dropdowns are populated?
Copy link to clipboard
Copied
Answered my own q. Man... that was fun. Ok first of all, thank you Raymond for posting the triple select blog post on your Jedi blog. What I ended up doing was altering the script so that I had query data returned for the first two selects. Since my third select was the one that queried that large table, I didn't use the script but instead queried the database. That's it! Simple and working well.
Please let me know if you'd like me to post the code and I will.
Copy link to clipboard
Copied
Good work.
It'd probably be helpful for other people encountering the same problem if you posted your code.
--
Adam
Copy link to clipboard
Copied
Adam Cameron. wrote:
Good work.
It'd probably be helpful for other people encountering the same problem if you posted your code.
--
Adam
<cfquery name="get_states" datasource="mydsn">
SELECT state_id,state
FROM states
ORDER BY state
</cfquery>
<cfquery name="get_counties" datasource="mydsn">
SELECT county_id,state_id,county
FROM counties
ORDER BY state_id,county_id
</cfquery>
<cfquery name="get_destinations" datasource="mydsn" maxrows="1">
SELECT state_id,county_id,destination_id,destination_name
FROM destinations
WHERE destination_id = '0'
ORDER BY state_id,county_id
</cfquery>
<cfscript>
State = QueryNew("state_id,state","Integer,Varchar");
for(i=1; i LTE get_states.RecordCount; i=i+1){
newRow = QueryAddRow(State);
QuerySetCell(State, "state_id", #get_states.state_id#);
QuerySetCell(State, "state", "#get_states.state#");
}
County = QueryNew("state_id, county_id, county", "Integer, Integer, Varchar");
for(i=1; i LTE get_counties.RecordCount; i=i+1){
newRow = QueryAddRow(County);
QuerySetCell(County, "state_id", #get_counties.state_id#);
QuerySetCell(County, "county_id", #get_counties.county_id#);
QuerySetCell(County, "county", "#get_counties.county#");
}
Destination = QueryNew("state_id, county_id, destination_id, destination_name", "Integer, Integer, Integer, Varchar");
for(i=1; i LTE get_destinations.RecordCount; i=i+1){
newRow = QueryAddRow(Destination);
QuerySetCell(Destination, "state_id", #get_destinations.state_id#);
QuerySetCell(Destination, "county_id", #get_destinations.county_id#);
QuerySetCell(Destination, "destination_id", #get_destinations.destination_id#);
QuerySetCell(Destination, "destination_name", "#get_destinations.destination_name#");
}
</cfscript>
===================================================================
<cffunction name="GetDestination" access="remote" returnType="query">
<cfargument name="State" type="any" required="true">
<cfargument name="County" type="any" required="true">
<cfif ARGUMENTS.State EQ "" OR ARGUMENTS.County EQ "">
<cfset LstDestination = QueryNew("state_id, county_id, destination_id, destination_name", "Integer, Integer, Integer, Varchar")>
<cfelse>
<cfquery name="LstDestination" datasource="mydsn">
SELECT state_id, county_id, destination_id, destination_name
FROM destinations
WHERE state_id = #ARGUMENTS.State# AND
county_id = #ARGUMENTS.County#
ORDER BY destination_name
</cfquery>
</cfif>
<cfreturn LstDestination>
</cffunction>
Copy link to clipboard
Copied
Hi Idesdema,
I'm working on a year/make/model/option/option2 related search and came across your post. I know its been a while but do you have the form that goes with the post so i can take a look at how to reference the cfc properly?
I have tried so many tutorisals and none have worked. At this point i built a database to match your code so i can test to make sute i have a good idea before i convert it to my automotive app. Thanks!
Cheers,
Aaron
Copy link to clipboard
Copied
Nevermind, found it!
Copy link to clipboard
Copied
Ok cool. I have it if you need it.
Copy link to clipboard
Copied
Hi,
Did you ever get a error on the returnType="query"? It says "The value of the attribute QUERY is invalid. The 'Query' attribute must be defined if the 'Value', 'Display', or 'Group' attributes are defined."
<cffunction name="GetDestination" access="remote" returnType="query">
<cfargument name="State" type="any" required="true">
<cfargument name="County" type="any" required="true">
<cfif ARGUMENTS.State EQ "" OR ARGUMENTS.County EQ "">
<cfset LstDestination = QueryNew("state_id, county_id, destination_id, destination_name", "Integer, Integer, Integer, Varchar")>
<cfelse>
<cfquery name="LstDestination" datasource="" username="" password="">
SELECT state_id, county_id, destination_id, destination_name
FROM destinations
WHERE state_id = #ARGUMENTS.State# AND
county_id = #ARGUMENTS.County#
ORDER BY destination_name
</cfquery>
</cfif>
<cfreturn LstDestination>
</cffunction>
Copy link to clipboard
Copied
Are you including the .cfm file in the cfc that initially declares that LstDestination? Something like...
(in the included file before your functions)
LstDestination = QueryNew("state_id, county_id, destination_id, destination_name", "Integer, Integer, Integer, Varchar");
for(i=1; i LTE get_destinations.recordcount; i=i+1){
newRow = QueryAddRow(LstDestination);
QuerySetCell(LstDestination,"state_id", #get_destinations.state_id#);
QuerySetCell
and so on for the other columns...
Copy link to clipboard
Copied
I have it in my cfc named "_fc_cfc_triple.cfc" saved in the same directory as the form. I haven't really used CFC's until this week. How do i include it properly? I wonder if its even loading it.
I thought maybe the form was calling it in the select tag:
<cfform>
<table>
<tr>
<td width="100">State:</td>
<td width="150">
<cfselect name="SelState" bind="cfc:_fc_cfc_triple.GetState()" display="misc_state" value="misc_state" BindOnLoad="true"/></td></tr>
<tr>
<td width="100">County:</td>
<td width="150">
<cfselect name="SelCounty" bind="cfc:_fc_cfc_triple.GetCounty({SelState})" display="destination_county" value="destination_county" BindOnLoad="false"/></td></tr>
<tr>
<td width="100">Destination:</td>
<td width="150">
<cfselect name="SelDestination" bind="cfc:_fc_cfc_triple.GetDestination({SelState},{SelDestination})" display="destination_name" value="destination_id" BindOnLoad="false"/></td></tr>
</table>
</cfform>
Copy link to clipboard
Copied
CFC:
<cffunction name="GetDestination" access="remote" returnType="query">
<cfargument name="State" type="any" required="true">
<cfargument name="County" type="any" required="true">
<cfif ARGUMENTS.State EQ "" OR ARGUMENTS.County EQ "">
<cfset LstDestination = QueryNew("state_id, county_id, destination_id, destination_name", "Integer, Integer, Integer, Varchar")>
<cfelse>
<cfquery name="LstDestination" datasource="" username="" password="">
SELECT state_id, county_id, destination_id, destination_name
FROM destinations
WHERE state_id = #ARGUMENTS.State# AND
county_id = #ARGUMENTS.County#
ORDER BY destination_name
</cfquery>
</cfif>
<cfreturn LstDestination>
</cffunction>
Copy link to clipboard
Copied
The code I roughed out in #16 is what should in a seperate .cfm file that is included within your .cfc.
So, my code is very similar to yours, but I have a <cfinclude template="..."> in the .cfc file that first establishes the three queries for when the select boxes have yet to be changed. You should have something similar.
Copy link to clipboard
Copied
idesdema wrote:
The code I roughed out in #16 is what should in a seperate .cfm file that is included within your .cfc.
So, my code is very similar to yours, but I have a <cfinclude template="..."> in the .cfc file that first establishes the three queries for when the select boxes have yet to be changed. You should have something similar.
Thank you. Do i wrap that with a javascript tag?
Copy link to clipboard
Copied
Run your cfqueries, then wrap the QueryNew statements in tags.
Copy link to clipboard
Copied
I think you are better off with 3 functions, one for each select. It would be a lot simpler.
Also, if you read the entire thread, you will notice some excellent advice from Adam Cameron. It's the one that mentions juggling. The approach he suggests is the one I usually take.
Copy link to clipboard
Copied
That is how mine is setup... one function for each dropdown. I'm assuming Aaron's is that way as well. And for the record I did take Adam's advice (long long ago when I was working on this) and that's how I arrived at my triple select. It works well and is efficient.
Copy link to clipboard
Copied
idesdema wrote:
That is how mine is setup... one function for each dropdown. I'm assuming Aaron's is that way as well. And for the record I did take Adam's advice (long long ago when I was working on this) and that's how I arrived at my triple select. It works well and is efficient.
Thanks, mine is set up that way as well. I'm going to start over and go peice by piece and see if that makes more sense.