• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Code is not working for a 2nd select box populating based on selection in the first select box

Participant ,
Jan 27, 2019 Jan 27, 2019

Copy link to clipboard

Copied

I want to have the first select box display vehicle makes (this part is working) and then have the second select box display related vehicle models based on the model selected in the first select box.

I've researched this and based my code on answers I have found in the forum, however the second select box is not working.

Following is my code:

The CFC:

<cfcomponent output="false">

  

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

        <cfset var data="" />

           <cfquery datasource="xxxxxxx" name="data">

SELECT  makeid, make

FROM makesfinal

ORDER by make

</cfquery>

        <cfreturn data>

       

       

         <!--- Convert results to array --->

        <cfloop index="i" from="1" to="#data.RecordCount#">

            <cfset result[1]=data.makeid>

            <cfset result[2]=data.make>

        </cfloop>

        <!--- And return it --->

        <cfreturn data>

       

       

    </cffunction>

  

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

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

        <cfset var data="" />

       <cfquery datasource="xxxxxxxxxx" name="data">

        SELECT modelid, model,makeid

        FROM modelsfinal

        WHERE makeid = '#ARGUMENTS.makeid#'

        ORDER BY model

        </cfquery>

       

       

         <cfloop index="i" from="1" to="#data.RecordCount#">

            <cfset result[1]=data.modelid>

            <cfset result[2]=data.model>

        </cfloop>

       

        <cfreturn data>

    </cffunction>

  

</cfcomponent>

The Select Code:

<cfselect

    name="Product"

    bind="cfc:makemodel2.getProduct()"

    style="width:387px;"

    size="1"

    multiple="No"

    required="No"

    display="make"

    value="makeid"

    bindonLoad="True">

</cfselect>

<cfselect

    name="Sub Product"

    bind="cfc:makemodel2.getSubProduct({makeid})"

    style="width:387px;"

    size="1"

    multiple="No"

    required="No"

    display="model"

    value="Modelid">

</cfselect>

Thanks for your help.

Views

609

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Community Expert , Jan 28, 2019 Jan 28, 2019

I didn't say you should change the name of the function. Leave the functions getProduct and getSubProduct intact. These are the names of functions in Makemodel2.cfc.

There are 3 things.

1) Make sure that the name of the first select field, for example makeid or product, matches the value that you pass in the bind of the second select field.

2) Display and value are not necessary as required attributes. What you may need are default values for the select fields. To get them, do this in the CFC:

!---

...

Votes

Translate

Translate
Community Expert ,
Jan 27, 2019 Jan 27, 2019

Copy link to clipboard

Copied

I'm not all that knowledgeable about using the BIND attribute of CFSELECT. But I do see a potential problem here. What exactly is supposed to cause the second CFSELECT to trigger the CFC call? Your first CFSELECT has a name of Product; changing that isn't going to cause the second one to fire.

Dave Watts, Eidolon LLC

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 28, 2019 Jan 28, 2019

Copy link to clipboard

Copied

I would remove the value and display and change the name of the first select to makeid:

<cfselect

    name="makeid"

    bind="cfc:makemodel2.getProduct()"

    style="width:387px;"

    size="1"

    multiple="No"

    required="No"

    bindonLoad="True">

</cfselect>

<cfselect

    name="SubProduct"

    bind="cfc:makemodel2.getSubProduct({makeid})"

    style="width:387px;"

    size="1"

    multiple="No"

    required="No">

</cfselect>

Alternatively,

<cfselect

    name="product"

    bind="cfc:makemodel2.getProduct()"

    style="width:387px;"

    size="1"

    multiple="No"

    required="No"

    bindonLoad="True">

</cfselect>

<cfselect

    name="SubProduct"

    bind="cfc:makemodel2.getSubProduct({product})"

    style="width:387px;"

    size="1"

    multiple="No"

    required="No">

</cfselect>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jan 28, 2019 Jan 28, 2019

Copy link to clipboard

Copied

BKBK,

I removed the display and value attributes but there was an error that those are necessary, which makes sense.

I also changed getproduct to makeid and that caused an error as well.

Any other thoughts?

Thanks.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 28, 2019 Jan 28, 2019

Copy link to clipboard

Copied

I didn't say you should change the name of the function. Leave the functions getProduct and getSubProduct intact. These are the names of functions in Makemodel2.cfc.

There are 3 things.

1) Make sure that the name of the first select field, for example makeid or product, matches the value that you pass in the bind of the second select field.

2) Display and value are not necessary as required attributes. What you may need are default values for the select fields. To get them, do this in the CFC:

!--- Values for the first select option --->

var result=arrayNew(2)>

<cfset result[1][1]="">

<cfset result[1][2]="Select make">

<cfloop query="data">

<cfset result[currentrow+1][1]=data.makeId>

<cfset result[currentrow+1][2]=data.make>

</cfloop>

<!--- Values for the first select option --->

var result2=arrayNew(2)>

<cfset result2[1][1]="">

<cfset result2[1][2]="Select model">

<cfloop query="data">

<cfset result2[currentrow+1][1]=data.modelId>

<cfset result2[currentrow+1][2]=data.model>

</cfloop>

3) In the functions use <cfreturn result>, <cfreturn result2>, respectively.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 29, 2019 Jan 29, 2019

Copy link to clipboard

Copied

(1) is the part that immediately struck me. For binds to work, you have to change a field named in the form. After I found that, I didn't bother looking at the rest.

Dave Watts, Eidolon LLC

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 29, 2019 Jan 29, 2019

Copy link to clipboard

Copied

Correction:

<cfset var result=arrayNew(2)>

...

...

<cfset var result2=arrayNew(2)>

You should also initialize the query name at the beginning of each function. That is, using

<cfset var data="">

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jan 29, 2019 Jan 29, 2019

Copy link to clipboard

Copied

BKBK

Thank you, it is working! However, it is first popping up a message:

Error invoking CFC /makemodel2.cfc : Error Executing Database Query. [Enable debugging by adding 'cfdebug' to your URL parameters to see more information]

I'm not sure why since it is obviously executing the database query because I'm getting the correct results in each Select.

Here's my code, maybe you can figure out why that message is popping up?

CFC:

<cfcomponent output="false">

  

    <cffunction name="getProduct" access="remote" returnType="array">

        <cfset var data="" />

<cfquery datasource="xxxxxxx"name="data">

SELECT  makeid, make

FROM makesfinal

ORDER by make

</cfquery>

      

        <!--- Convert results to array --->

<cfset var result=arrayNew(2)>

<cfset result[1][1]="">

<cfset result[1][2]="Select make">

<cfloop query="data">

<cfset result[currentrow+1][1]=data.makeId>

<cfset result[currentrow+1][2]=data.make>

</cfloop>

      <!--- And return it --->

      <cfreturn result>

    </cffunction>

  

    <cffunction name="getSubProduct" access="remote" returnType="array">

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

        <cfset var data="" />

       <cfquery datasource="xxxxxxxx"name="data">

        SELECT modelid, model,makeid

        FROM modelsfinal

        WHERE makeid = '#ARGUMENTS.makeid2#'

        ORDER BY model

        </cfquery>

       

<cfset var result2=arrayNew(2)>

<cfset result2[1][1]="">

<cfset result2[1][2]="Select model">

<cfloop query="data">

<cfset result2[currentrow+1][1]=data.modelId>

<cfset result2[currentrow+1][2]=data.model>

</cfloop>

      <!--- And return it --->

      <cfreturn result2>

    </cffunction>

  

</cfcomponent>

Select Code:

<cfselect

    name="makeid"

    bind="cfc:makemodel2.getProduct()"

    style="width:387px;"

    size="1"

    multiple="No"

    required="No"

    bindonLoad="True">

</cfselect>

<cfselect

    name="modelid"

    bind="cfc:makemodel2.getSubProduct({makeid})"

    style="width:387px;"

    size="1"

    multiple="No"

    required="No">

</cfselect>

Thanks!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 30, 2019 Jan 30, 2019

Copy link to clipboard

Copied

I am glad to hear that it works. About the error

Error invoking CFC /makemodel2.cfc : Error Executing Database Query. [Enable debugging by adding 'cfdebug' to your URL parameters to see more information]

I wonder whether this has to do with the second query. What happens when you test with

<!--- Assuming makeID is a string --->

<cfquery datasource="xxxxxxxx" name="data">

SELECT modelid, model

FROM modelsfinal

WHERE makeid = <cfqueryparam cfsqltype="cf_sql_varchar" value="#ARGUMENTS.makeid2#">

ORDER BY model

</cfquery>

or

<!--- assuming makeID is an integer --->

<cfquery datasource="xxxxxxxx" name="data">

SELECT modelid, model

FROM modelsfinal

WHERE makeid = <cfqueryparam cfsqltype="cf_sql_integer" value="#ARGUMENTS.makeid2#">

ORDER BY model

</cfquery>

In any case, this version is safer and more efficient.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jan 30, 2019 Jan 30, 2019

Copy link to clipboard

Copied

BKBK

I'm sorry to say that did not resolve the issue.

Through the debugging console I was able to find that the error is:

Conversion failed when converting from a character string to uniqueidentifier.

I don't exactly understand that though  as the value is a uniqueidentifier.  I tried convert(uniqueidentifier, 'arguments.makeid2') but that did not solve the issue.

UPDATE:

I individually entered each makeid into the query as below, and none of them threw the error.  It has something to do with #argument.maikeid2#. I'm perplexed.:

<cfquery datasource="lrauction" dbname="auction" password="Grandeur2" name="data">

        SELECT modelid, model,makeid

        FROM modelsfinal

        WHERE makeid = '4CC466E7-EA7E-41BB-9BE0-027A4D828A99'

        ORDER BY model

        </cfquery>

Thanks again for your help.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 30, 2019 Jan 30, 2019

Copy link to clipboard

Copied

As makeId's datatype is unique identifier, the error makes sense. Verify whether the type 4CC466E7-EA7E-41BB-9BE0-027A4D828A99 is a valid unique identifier for the database brand. (It is, for SQL Server). Also test whether any args are empty. Lastly, use

convert(uniqueidentifier, <cfqueryparam cfsqltype="cf_sql_varchar" value="#trim(ARGUMENTS.makeid2)#">)

Something like:

<cfset var makeId = trim(ARGUMENTS.makeid2)>

<cfif makeId is not "" and len(makeId) eq 36>

    <cfquery datasource="lrauction" dbname="auction" password="Grandeur2" name="data">

    SELECT modelid, model

    FROM modelsfinal

    WHERE makeid = convert(uniqueidentifier, <cfqueryparam cfsqltype="cf_sql_varchar" value="#makeid#">)

    ORDER BY model

    </cfquery>

<cfelse>

<!--- Inform: input is not a valid GUID --->

</cfif>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Jan 31, 2019 Jan 31, 2019

Copy link to clipboard

Copied

BKBK

I am using SQL server.

It is throwing the following error and I'm not sure why as it worked before:

Attribute validation error for tag cfloop. The value of the attribute query, which is currently data, is invalid.

Here is the code:

<cffunction name="getSubProduct" access="remote" returnType="array">

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

      

      

        <cfset var data="" />

    <cfset var makeId = trim(ARGUMENTS.makeid2)>

<cfif makeId is not "" and len(makeId) eq 36>

    <cfquery datasource="xxxxx" name="data">

    SELECT modelid, model

    FROM modelsfinal

    WHERE makeid = convert(uniqueidentifier, <cfqueryparam cfsqltype="cf_sql_varchar" value="#makeid#">)

    ORDER BY model

    </cfquery>

<cfelse>

<!--- Inform: input is not a valid GUID --->

</cfif>

  

  

     

<cfset var result2=arrayNew(2)>

<cfset result2[1][1]="">

<cfset result2[1][2]="Select model">

<cfloop query="data">

<cfset result2[currentrow+1][1]=data.modelId>

<cfset result2[currentrow+1][2]=data.model>

</cfloop>

      <!--- And return it --->

      <cfreturn result2>

    </cffunction>

</cfcomponent>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 31, 2019 Jan 31, 2019

Copy link to clipboard

Copied

Ah, good!

It means that one of the arguments.makeId values is not valid. It is either an empty string or contains more than 36 characters. That is in fact the reason I suggested you do validation.

The following example will dump validation.html in the current folder:

<cfif makeId is not "" and len(makeId) eq 36>

<!--- query --->

<cfelse>

<!--- Inform: input is not a valid GUID --->

<cfdump var="The arguments.makeId value of '#arguments.makeId#' is not valid" format="html" output="#expandPath('.')#\validation.html">

<cfabort>

</cfif>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Feb 04, 2019 Feb 04, 2019

Copy link to clipboard

Copied

BKBK

You were right!  I get this in the validation.html file:

The arguments.makeId2 value of '' is not valid

However, when I look at the tables in my Database I see no nulls:

Makes:

Models:

So I am quite perplexed. Or is it that one of the makeids from the makes table is not finding a match in the models table?

Thanks!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 04, 2019 Feb 04, 2019

Copy link to clipboard

Copied

It can only mean that the AJAX call is automatically calling the second function, passing it the default makeId,

<cfset result[1][1]="">

<cfset result[1][2]="Select make">

However, that shouldn't be happening. To correct for this, you could do something like

<cfset var result2=arrayNew(2)>

<cfif makeId is not "" and len(makeId) eq 36>

    <cfquery datasource="xxxxx" name="data">

    SELECT modelid, model

    FROM modelsfinal

    WHERE makeid = convert(uniqueidentifier, <cfqueryparam cfsqltype="cf_sql_varchar" value="#makeid#">)

    ORDER BY model

    </cfquery>

    <cfset result2[1][1]="">

    <cfset result2[1][2]="Select model">

    <cfloop query="data">

        <cfset result2[currentrow+1][1]=data.modelId>

        <cfset result2[currentrow+1][2]=data.model>

    </cfloop>

<cfelse>

    <cfset result2[1][1]="">

    <cfset result2[1][2]="Select model">

</cfif>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Feb 04, 2019 Feb 04, 2019

Copy link to clipboard

Copied

LATEST

BKBK

Thank you so much again for your help, that fixed it! 

Cheers!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 28, 2019 Jan 28, 2019

Copy link to clipboard

Copied

In your first function, you are returning the query before you put it into an array.  The act of creating and populating the array then returning the array isn't being triggered because the first cfreturn data stops all processing after it.

Also, in the second function you are declaring the returntype as query, but returning an array.  That, alone, should trigger error messages.

Besides that, and I hate to bring this up, but CFFORM and related elements should not be used.  Adobe is sticking to outdated Ext.js and other libraries.  You're better off using plain JavaScript or jQuery and a CSS library like Bootstrap, and coding manually for the bindings.

HTH,

^ _ ^

UPDATE: I just noticed that you are creating an array called "result" but returning "data".  How confusing.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation