Skip navigation
Currently Being Moderated

Triple related select

Aug 4, 2011 11:32 AM

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>
 
Replies
  • Currently Being Moderated
    Aug 4, 2011 12:51 PM   in reply to idesdema

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

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 4, 2011 1:21 PM   in reply to idesdema

    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>

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 5, 2011 12:36 AM   in reply to idesdema

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Aug 5, 2011 1:09 PM   in reply to idesdema

    Good work.

     

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

     

    --

    Adam

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 27, 2012 8:13 AM   in reply to idesdema

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 27, 2012 8:16 AM   in reply to AaronMRenfroe

    Nevermind, found it!

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 27, 2012 9:45 AM   in reply to idesdema

    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>

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 27, 2012 9:59 AM   in reply to idesdema

    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>

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 27, 2012 10:00 AM   in reply to AaronMRenfroe

    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>

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 27, 2012 10:19 AM   in reply to idesdema

    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?

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 27, 2012 10:55 AM   in reply to AaronMRenfroe

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 28, 2012 5:01 AM   in reply to Dan Bracuk

    Dan Bracuk wrote:

     

    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.

    Thank you, i will start with a single select this morning and try and get it working properly.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 28, 2012 5:04 AM   in reply to idesdema

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 28, 2012 5:58 AM   in reply to idesdema

    idesdema wrote:

     

    Let me know if u want my code if u get stuck. I'll zip and send.

     

    Yes Please...

     

    I have broken it down to the following, but i'm still confused. I don't get errors anymore, but i don't get any select options either.

     

    index.cfm:

    <cfform name="RabbitFoot">

    <table>

        <tr>

            <td width="100">Select Year:</td>

            <td width="150">

            <cfselect name="SelYears" bind="cfc:vehicles.GetYear()" BindOnLoad="true"/>

            </td>

        </tr>

    </table>

    </cfform>

     

    include QryYears.cfm:

    <cfquery name="GetYearRange" datasource="" username="" password="">

        SELECT DISTINCT YearRange

        FROM ExactFit2012

        ORDER BY YearRange

    </cfquery>

       

    <cfscript>

    YearRange = QueryNew("YearRange","Varchar");

          for(i=1; i LTE getYearRange.RecordCount; i=i+1){

      newRow = QueryAddRow(YearRange);

      QuerySetCell(YearRange, "YearRange", "#GetYearRange.YearRange[i]#");

      }

    </cfscript>

     

    CFC:

    <cfinclude template="QryYears.cfm">

     

     

    <cffunction name="GetYear" access="remote" returnType="query">

    <cfargument name="GetYearRange" type="any" required="true">

    <cfif ARGUMENTS.GetYearRange EQ "">

        <cfset LstYears = QueryNew("YearRange", "Varchar")>

    <cfelse>

            <cfquery name="GetYearRange" dbtype="query" datasource="GriffinDSN" username="aaron_Griffin2009" password="Griffin1">

                SELECT DISTINCT YearRange

                FROM ExactFit2012

                ORDER BY YearRange

            </cfquery>

    </cfif>

    <cfreturn LstYears>

    </cffunction>

     

    I don't know if i'm supposed to have two queries that are the same, or if the second query was supposed to be for my next set of select options.


     
    |
    Mark as:
  • Currently Being Moderated
    Nov 28, 2012 6:14 AM   in reply to AaronMRenfroe

    Nevermind on the last sentance, i see its just a if statment on the argument. Going codeblind!

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 28, 2012 6:40 AM   in reply to idesdema

    Thank you very much!

     

    Can I ask if you ever got the following error on the query using the " display="state" value="state_id" " within the select tags?

     

    On my last couple tries I got the "The value of the attribute QUERY is invalid. The 'Query' attribute must be defined if the 'Value', 'Display', or 'Group' attributes are defined." error so I went to Ray's blog and another developer had taken them out completely to fix the error, even though Ray never got that error on his local servers. I'm curious as to why we got them but ray and others didn't.

     

    We are using CF8 but we are shared hosting so I’m thinking it might be some preference in the CF settings.

     

    Thanks!

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 28, 2012 6:46 AM   in reply to AaronMRenfroe

    Nevermind, i think i see why now, they are database fields within the display and value attributes

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 28, 2012 8:27 AM   in reply to idesdema

    Hi Again,

     

    All of my data is in one table with one ID so i needed to modify the included page and the CFC queries and the script below to refelct that. I don't get errors but still no data being inserted into the dropdowns.

     

    <cfquery name="get_years" datasource="DSN">

    SELECT YearRange

    FROM ExactFit2012

    ORDER BY YearRange

    </cfquery>

    <cfquery name="get_makes" datasource="DSN">

    SELECT make

    FROM ExactFit2012

    ORDER BY Make

    </cfquery>

    <cfquery name="get_models" datasource="DSN">

    SELECT model

    FROM ExactFit2012

    ORDER BY model

    </cfquery>

     

    Origional Code:

    <cfquery name="get_states" datasource="mydsn">

    SELECT state_id,state

    FROM tbl_states

    ORDER BY state

    </cfquery>

    <cfquery name="get_counties" datasource="mydsn">

    SELECT county_id,state_id,county

    FROM tbl_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 tbl_destinations

    WHERE destination_id = '0'

    ORDER BY state_id,county_id

    </cfquery>

     

     

     

    <cfscript>

    Year = QueryNew("YearRange","Varchar");

        for(i=1; i LTE get_years.RecordCount; i=i+1){

            newRow = QueryAddRow(Year);

            QuerySetCell(Year, "YearRange", "#get_years.YearRange[i]#");

        }

    make = QueryNew("YearRange, make", "Varchar, Varchar");

        for(i=1; i LTE get_makes.RecordCount; i=i+1){

            newRow = QueryAddRow(make);

            QuerySetCell(make, "YearRange", #get_makes.YearRange[i]#);

            QuerySetCell(make, "Make", #get_makes.make[i]#);

        }

    Model = QueryNew("YearRange, make, model", "Varchar, Varchar, Varchar");

        for(i=1; i LTE get_models.RecordCount; i=i+1){

            newRow = QueryAddRow(model);

            QuerySetCell(model, "YearRange", #get_models.YearRange[i]#);

            QuerySetCell(model, "make", #get_models.make[i]#);

            QuerySetCell(model, "model", #get_models.model[i]#);

        }

    </cfscript>

     

    Origional Code:

    <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[i]#);

            QuerySetCell(State, "state", "#get_states.state[i]#");

        }

    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[i]#);

            QuerySetCell(County, "county_id", #get_counties.county_id[i]#);

            QuerySetCell(County, "county", "#get_counties.county[i]#");

        }

    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[i]#);

            QuerySetCell(Destination, "county_id", #get_destinations.county_id[i]#);

            QuerySetCell(Destination, "destination_id", #get_destinations.destination_id[i]#);

            QuerySetCell(Destination, "destination_name", "#get_destinations.destination_name[i]#");

        }

    </cfscript>

     

     

    I deleted all the references to the _id fields from the code so that it might work with my data but i think i may have needed that.


     
    |
    Mark as:
  • Currently Being Moderated
    Nov 28, 2012 10:28 AM   in reply to Dan Bracuk

    Hi Idesdema & Dan,

     

    Back Again, still not working for me.

     

    I tried breaking it down to a single select for learning purposes. I keep getting the error "The value of the attribute QUERY is invalid. The 'Query' attribute must be defined if the 'Value', 'Display', or 'Group' attributes are defined." unless i remove the "display" and  "value" attributes inside of the select tag. When i do it stops the error but i get no results. For some reason i think those need to be there as a reference to the data and could be the reason i'm not seeing results, but i'm not certain. What do you think?

     

    Form:

    <cfform name="_jump" method="post" action="">

    <table cellpadding="4" cellspacing="0" border="0">

    <tr>

        <tr>

            <td align="right" valign="top">

            Year Range:

            </td>

            <td align="left">

            <cfselect class="dropdown" name="jump_state" bind="cfc:_cfc_triple.get_states()" display="state" value="state_id" bindonload="true"/>

            </td>

        </tr>

    </table>

    </cfform>

    CFC:

    <cfcomponent>

        <cfinclude template="_cfc_triple.cfm">

        <cffunction name="get_states" access="remote" returnType="query">

            <cfquery name="LstState" dbtype="query" datasource="GriffinDSN">

            SELECT ID, YearRange

            FROM ExactFit2012

            ORDER BY YearRange

            </cfquery>

            <cfreturn LstState>

        </cffunction>

    </cfcomponent>

     

    Include:

    <cfquery name="get_states" datasource="GriffinDSN">

    SELECT ID, YearRange

    FROM ExactFit2012

    ORDER BY YearRange

    </cfquery>

     

     

    <cfscript>

    State = QueryNew("ID,YearRange","Integer,Varchar");

        for(i=1; i LTE get_states.RecordCount; i=i+1){

            newRow = QueryAddRow(State);

            QuerySetCell(State, "id", #get_states.id[i]#);

            QuerySetCell(State, "YearRange", "#get_states.YearRange[i]#");

        }

    </cfscript>

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 28, 2012 10:53 AM   in reply to AaronMRenfroe

    Your error message is being generated because you are running a query of queries against a database table.

     

    It appears that you are running way too much code in order to get what you want.  For example, you are running a database query called get_states and then looping though it to produce a CF query called state.  I'm not sure what that's all about.

     

    I also don't know why there is an included file for the query.  If you need a query object that's available to more than one function, just run the code in the cfc file, outside of any function.

     

    Going back to the juggling analogy, if it were me, I'd test my cfc functions with either cfinvoke or createobject syntax before attempting to use them with bind syntax.

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 28, 2012 11:03 AM   in reply to Dan Bracuk

    Dan Bracuk wrote:

     

    Your error message is being generated because you are running a query of queries against a database table.

     

    It appears that you are running way too much code in order to get what you want.  For example, you are running a database query called get_states and then looping though it to produce a CF query called state.  I'm not sure what that's all about.

     

    I also don't know why there is an included file for the query.  If you need a query object that's available to more than one function, just run the code in the cfc file, outside of any function.

     

    Going back to the juggling analogy, if it were me, I'd test my cfc functions with either cfinvoke or createobject syntax before attempting to use them with bind syntax.

    Thanks! I will keep trying

     
    |
    Mark as:
  • Currently Being Moderated
    Nov 28, 2012 11:55 AM   in reply to Dan Bracuk

    Hi Dan i tried using the CFInvoke Below. I haven't tried using these types of tags before and am googling examples to modify and use.

     

    Here is mine:

     

    CFC: Named Components

    <cfcomponent displayname="Years" hint="ColdFusion Component for Getting Vehicle Years">

    <cffunction name="retrieveYears" hint="Gets all years from the database" returntype="query">

       <cfquery name="getYears" datasource="GriffinDSN">

            SELECT ID, YearRange

            FROM ExactFit2012

            ORDER BY YearRange

       </cfquery>

       <cfreturn getYears>

    </cffunction>

    </cfcomponent>

     

    Invoke:

    <cfinvoke component="components.years" method="retrieveYears" returnvariable="allYears"></cfinvoke>

     

     

    <cfoutput query="allYears">

    #YearRange#

    </cfoutput>

     

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points