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

[BACFL]

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.

[Dave Watts]

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

[BKBK]

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>

[BACFL]

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.

[BKBK · Accepted solution]

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.

[Dave Watts]

(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

[BKBK]

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

[BACFL]

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!

[BKBK]

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.

[BACFL]

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.

[BKBK]

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>

[BACFL]

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>

[BKBK]

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>

[BACFL]

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!

[BKBK]

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>

[BACFL]

BKBK​

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

Cheers!

[WolfShade]

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.