Skip to main content
A
Anonymous
August 4, 2011
Question

Triple related select

  • August 4, 2011
  • 3 replies
  • 8686 views

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>
    This topic has been closed for replies.

    3 replies

    A
    Anonymous
    August 5, 2011

    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.

    A
    Adam Cameron.
    Inspiring
    August 5, 2011

    Good work.

    It'd probably be helpful for other people encountering the same problem if you posted your code.

    --

    Adam

    A
    Anonymous
    August 5, 2011

    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>

    A
    Anonymous
    August 5, 2011

    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?

    A
    Adam Cameron.
    Inspiring
    August 5, 2011

    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

    A
    Anonymous
    August 5, 2011

    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.

    A
    Avatar
    Participating Frequently
    August 4, 2011

    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"/>

    A
    Anonymous
    August 4, 2011

    Changed the SelDestination to SelCounty in the last cfselect... Still getting the same three bind failed errors.

     

    A
    Avatar
    Participating Frequently
    August 4, 2011

    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>

    Firefly Community Gallery
    Adobe Firefly

    Remix with Firefly Community Gallery

    Thousands of free creations to fall in love with and remix in Firefly.

    Explore now
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices