select/bind help

[BreakawayPaul]

I'm helping a colleague with a web app that is basically a database of links to documents that are scattered around our website.  It's meant to be a way to search for documents using various criteria without having to browse the entire site.

The data is organized into categories (known as disciplines) and subcategories (known as subdisciplines) as well as a few other parameters.  I'm helping to get the database normalized, but one of the things I want to do is make the search form reflect the current database contents.

Currently, a web form with a few <select> items is used, and some Javascript makes the subdisciplines <select> dependent on what's selected in the disciplines <select> box. Thus, anytime a new one of either of those two items is added, the JS needs to be edited.  I'd like to make this automatic, so I'm trying to use bind to do it.

Right now I have the following form:

<cfform name="fectchdocs" action="#CGI.SCRIPT_NAME#" method="post">

<p><label for="disc">Discipline:</label><br />

<select name="disc" id="disc">

<option value="">All</option>

<cfoutput query="GetDisc">

<option value="#disc_id#"<cfif FORM.disc eq disc_id> selected="selected"</cfif>>#adiscipline#</option></cfoutput>

</select></p>

<p><label for="subdisc">Subdiscipline:</label><br />

<cfselect name="subdisc" id="subdisc" bindonload="Yes" bind="cfc:searchdata.getSubs({discs})" /></p>

<p><input type="select" name="search" value="Search" /></p>

</cfform>

And the following CFC (searchform.cfc):

<cfcomponent output="false">

    <cffunction name="getDiscs" access="remote" returntype="array">

        <cfquery name="discs" datasource="guidance">

            SELECT disc_id, adiscipline

            FROM search_fhwa_disc

            ORDER BY adiscipline

        </cfquery>

        <cfset count="1" />

        <cfset results = arraynew(2)>

        <cfloop query="discs">

            <cfset results[count][1]="#disc_id#" />

            <cfset results[count][2]="#adiscipline#" />

            <cfset count= count+1>

        </cfloop>

        <cfreturn results />

    </cffunction>

    <cffunction name="getSubs" access="remote" returntype="array">

        <cfargument name="disc_id" type="string" required="true">

        <cfquery name="subs" datasource="guidance">

            SELECT sub_id, bsubdiscipline

            FROM search_fhwa_sub

            WHERE parent_id = <cfqueryparam value="#arguments.disc_id#" cfsqltype="cf_sql_numeric">

            ORDER BY bsubdiscipline

        </cfquery>

        <cfset count="1" />

        <cfset results = arraynew(2)>

        <cfloop query="subs">

            <cfset results[count][1]="#sub_id#" />

            <cfset results[count][2]="#bsubdiscipline#" />

            <cfset count= count+1>

        </cfloop>

        <cfreturn results />

    </cffunction>

</cfcomponent>

It of course fails miserable with three popup errors.  A bind error telling me 'discs' is not found.  A second 'discs' not found error, and one about passing a null value to my query.

It's obvious that I'm using the wrong function/variable name somewhere, but I'm not sure where.

I know that the CFC works, because if I invoke either method, I get the array that I expect when I dump the returnvariable.

[Dan_Bracuk]

When you first load your form, the value of ({discs}) will be an empty string.  You then pass this to your getSubs() method, and your query will crash.

To see why, invoke the method using coldfusion, but pass an empty string as your argument.

I rarely if ever do this sort of thing, but changing bindonload to false on your second select might solve the problem.

[BreakawayPaul]

Well that should have been pretty obvious, shouldn't it?

Changing bindonload to false eliminated two of the errors.  Now I just get a "bind failed, element not found: discs" error when I load the page.

[Ashish_Todon]

Insted of creating 2-d array it would be better to use your query directly for binding cfselect please go through the code

This my cfm

<cfform name="test" >

          <cfselect name="desc" id="desc">

                    <cfoutput>

                  <cfloop index="i" from="1" to="10" >

                                        <option value="#i#">#i#</option>

                              </cfloop>

        </cfoutput>

          </cfselect>

          <cfselect name="company" id="company"

                                bind="cfc:Utility.test.getname({desc})" bindonload="false"

                                value="COMPANYID" display="COMPANYNAME"></cfselect>  // here value and display should match column name of your query

  </cfform>

This is CFC

<cfcomponent>

          <cffunction name="getname" access="remote" >

                    <cfargument name="id" required="true" />

                    <cfquery name="qname" datasource="" >

                              select companyId, companyname from company where companyId = #ARGUMENTS.id#

                    </cfquery>

                    <cfreturn serializeJSON(qname)>

          </cffunction>

</cfcomponent>

[BreakawayPaul]

Thanks Ashish.  I edited your code to reflect my column names.  I also used a query to populate the first <select> list.  It got rid of all the errors, but the second <select> list remains empty, despite what's selected in the first.

Here's what I have:

form:

<cfform name="test" >

<cfselect name="desc" id="desc" query="getdiscs" value="disc_id" display="adiscipline"></cfselect>

<cfselect name="subdesc" id="subdesc" bind="cfc:searchdata.getname({desc})" bindonload="false" value="sub_id" display="bsubdiscipline"></cfselect>

</cfform>

CFC:

<cfcomponent>

    <cffunction name="getname" access="remote" >

    <cfargument name="disc_id" required="true" />

    <cfquery name="subs" datasource="guidance">

      SELECT sub_id, bsubdiscipline

      FROM search_fhwa_sub

      WHERE parent_id = #arguments.disc_id#

      ORDER BY bsubdiscipline

    </cfquery>

    <cfreturn serializeJSON(subs)>

    </cffunction>

</cfcomponent>

[Ashish_Todon]

My above code is working fine at my side please check your bind path  cfc:searchdata.getname({desc})

In my case by both cfm and cfc are located in such way

wwwroot/utility/mycfm.cfm

wwwroot/utility/test.cfc

so i gave bind path bind="cfc:Utility.test.getname({desc})"

and check your ajax request in firebud/chrome you are very much close to the solution

[BreakawayPaul]

My path is wwwroot/staffnet_test/pgcadmin, so my bind path is:

cfc:staffnet_test.pgcadmin.searchdata.getname({desc})

File name: searchdata.cfc

Method: getname

I know it's correct, because if I typo it, I get a missing component error.

I ediited the bind path and copied the files to the test server (I had been doing everything locally on the cf dev version), and I got the error that the bind failed for bos subdesc (my second <select>) because it's not a 2D array or valid serialized query.

[Ashish_Todon]

Hi Paul,

I edited your code to reflect my column names.  I also used a query to populate the first <select> list.  It got rid of all the errors, but the second <select> list remains empty, despite what's selected in the first.

Please follow these steps:-

1) In my code bind will work on onchange only if you want to to work even onload also please make bindonload="true"

2) As you said you rid off all the errors it means your bind is working fine.

3) on Chance of "desc" please check ajax request in firebug/chrome.

4) If, it is "cfc not found error" then see in to the path of cfc in the request URL if it is correct or not.

    If not then you have given wrong path in bind attribute of cfselect.

    example in my case URL path is(cfc:Utility.test.getname({desc}))

localhost/Utility/test.cfc?method=getname&_cf_ajaxproxytoken=&returnFormat=json&argumentCollection=%7B%22id%22%3A%221%22%7D&_cf_nodebug=true&_cf_nocache=true&_cf_rc=5

localhost/Utility/test.cfc //This is correct path of my cfc

5) Else we need to check our cfc function.

Please follow the setp and let me know where you find the issue .

BreakawayPaul wrote:

I ediited the bind path and copied the files to the test server (I had been doing everything locally on the cf dev version), and I got the error that the bind failed for bos subdesc (my second <select>) because it's not a 2D array or valid serialized query.

I think first we need to find out what format we are getting. Bind cfselect like we did then check what type of format we are getting

if it is a query format then value,display will be any two column name of query(rest columns will be neglected by cf). Else we need 2-d array for proper bind. If we are not getting either of the both formats and we can not make changes to our cfcs.Then we need to do little hack here instead of direct binding your cfselect here you can bind your javascript function. From that javascript function we can make ajax call and parse out data accordingly.

Please take a look: (This my cfm file)

<cfform name="test" >

               <cfselect name="desc" id="desc">

                              <cfoutput>

                            <cfloop index="i" from="1" to="10" >

                                                            <option value="#i#">#i#</option>

                                             </cfloop>

             </cfoutput>

               </cfselect>

               <cfselect name="company" id="company"

                                                    bind="javascript:myjsfunc({desc@change})" bindonload="false"></cfselect>

</cfform>

<script type="text/javascript">

               var myjsfunc = function(data){

         /*1) Here you can make your ajax call and

                 parse your data according to your use

             2) I created one js array and bind that

                array to the cfselect*/

         var arr = [[1,'a'],[2,'b']];

          return(arr);

          }

</script>

[BreakawayPaul]

Ok, when I change bindonload to true, I get the same 2D error, but this time it comes up when I load the page.

I opened the Firebug console and made a selection in the first <select>.  What I'm getting looks right:

Response:

"{\"COLUMNS\":[\"SUB_ID\",\"BSUBDISCIPLINE\"],\"DATA\":[[1506,\"Grant Programs\"],[1559,\"Program and Project Development\"],[1507,\"Truck Size and Weight\"]]}"

No errors.  No warnings.  So it's working.  It's just not making it into the <select> dropdown.

I thought about just using JavaScript and having a query build the JS, but I'm pretty hopeless with JS, so I'd have to find a working example to edit.

In the CFC, where we have this line: <cfreturn serializeJSON(subs)>

The value in the parens should be the name of the query in the CFC, correct?

[Ashish_Todon]

BreakawayPaul wrote:

Response:

"{\"COLUMNS\":[\"SUB_ID\",\"BSUBDISCIPLINE\"],\"DATA\":[[1506,\"Grant Programs\"],[1559,\"Program and Project Development\"],[1507,\"Truck Size and Weight\"]]}"

We are very much on right track you get result it means bind is OK, CFC query is OK

Steps to follow

1) Got to your Cf admin.

2) server settings > settings

3) Prefix serialized JSON with[//] option will be checked in your case please uncheck this and save. 

4) and check your page again.

BreakawayPaul wrote:

In the CFC, where we have this line: <cfreturn serializeJSON(subs)>

The value in the parens should be the name of the query in the CFC, correct?

yes

BreakawayPaul wrote:

I thought about just using JavaScript and having a query build the JS, but I'm pretty hopeless with JS, so I'd have to find a working example to edit.

Above step might have solve your problem. but we can discuss this(JS) way of doing our job. I will surly add code here. so we can discuss more

[Ashish_Todon · Accepted solution]

Hi

Above solution is by changing your server setting you can try that but we can do one more thing

Please see some changes in cfc level:-

Change 1 : I have added attribute returnformat="json" here.

Change 2 : I removed serializejson() function and return my result query as it is.

<cfcomponent>

          <cffunction name="getname" access="remote" securejson="false" returnformat="json"> //Change 1

                    <cfargument name="id" required="true" />

                    <cfquery name="qname" datasource="formexpress" >

                              select companyId as SUB_ID,

                                     companyname as BSUBDISCIPLINE

                                from company

                               where companyId = #ARGUMENTS.id#

                    </cfquery>

                    <cfreturn qname> // Change 2 

          </cffunction>

</cfcomponent>

By this you don't need to change your server settings

[BreakawayPaul]

Prefix serialized JSON with // is currently UNchecked.  This is on my local machine, of course.  I unfortunately don't have access to CFAdmin on our server.  But if this is something that's a problem, I can probably get the server people to change the setting.

Now, I have no idea why I didn't see this before (perhaps something was cached) but now on my localhost, I have a console error in Firebug:

'SyntaxError: parseJSON' when calling method: [nsIDOMEventListener::handleEvent]

[Break On This Error]    

Filtered chrome url chrome://firebug/content/net/spy.js

[Ashish_Todon]

Now can you show your response of AJAX to me. If it is coming. If not, check your cfc.  

[BreakawayPaul]

The response basically consists of my header include (from doctype all the way down to my H1) and my footer include, with this in between:

    "{\"COLUMNS\":[\"SUB_ID\",\"BSUBDISCIPLINE\"],\"DATA\":[[1233,\"Air Quality Planning\"],[1237,\"Air Toxics\"],[1232,\"Climate Change\"],[1231,\"Conformity\"],[1234,\"Congestion Mitigation and Air Quality Improvement\"]]}" 

it may be worth noting that I use OnRequestStart() and OnRequestEnd() in Application.cfc to bring in my headers and footers.  When I comment those methods out, I get the 2D array error again.

[Ashish_Todon]

BreakawayPaul wrote:

The response basically consists of my header include (from doctype all the way down to my H1) and my footer include, with this in between:

    "{\"COLUMNS\":[\"SUB_ID\",\"BSUBDISCIPLINE\"],\"DATA\":[[1233,\"Air Quality Planning\"],[1237,\"Air Toxics\"],[1232,\"Climate Change\"],[1231,\"Conformity\"],[1234,\"Congestion Mitigation and Air Quality Improvement\"]]}" 

"my header include (from doctype all the way down to my H1) and my footer include"

Got the error actully when we bind in coldfusion or in any javascript function it require a particular format like here in response cfselect want only query or array in json format. But you are sending header and footer both in your reponse. Here coldfuiosn's internal javascript library accept your response and tries to parse it but your header and footer is not in JSON format and you got the error. And here it wants query in json format only in response.

Now we can do 2 things:

1) Remove header and footer form your response. (I know ,little problem if you added them in onrequeststart and onrequstend)

2) or, Give me some time i will help you to bind javascript function. That will filter out header and footer from your response.

You don't need to change setting in cfadmin please follow my comment number 10

[BreakawayPaul]

Ashish Todon wrote:

You don't need to change setting in cfadmin please follow my comment number 10

Sorry, I totally missed post #10!  And now it WORKS!!  Bot locally and on the test server.

When I add the header/footer back in, it breaks again, so I'll have to figure out another way to do the includes.

EDIT: Solved the includes problem by putting the searchdata.cfc in its own folder, with its own empty Application.cfc.

Thanks Ashish!!!

[Ashish_Todon]

Pleasure is all mine learned a lot about coldfusion binding

[BreakawayPaul]

Sorry for the thread resurection, but I noticed two errr, features of the bind functionality.

1) The selected item on the dependent <select> doesn't remain selected after the form has been submitted, and

2) There's no way to have an "ALL" option.

I tried this for #2 to no avail:

<cfselect

     name="subdisc"

     id="subdisc"

     bind="cfc:infrastructure.pgcadmin.components.searchdata.getname({disc})"

     bindonload="True"

     display="bsubdiscipline"

     value="sub_id"

     selected="#FORM.subdisc#"

     queryPosition="below">

<option value="0"<cfif FORM.subdisc eq 0> selected="selected"</cfif>>All</option>

</cfselect>

Are there any work-arounds to either of these issues?

[Ashish_Todon]

BreakawayPaul wrote:

1) The selected item on the dependent <select> doesn't remain selected after the form has been submitted, and

Here we can do like

//this is conditional statment check if desc in defined in form or not

  <Cfset VARIABLE.desc = isDefined("FORM.desc")?FORM.desc:"" />

        <cfform name="test" >

            <cfselect name="desc" id="desc" query="qname" value="disc_id" display="adiscipline"

                                        selected="#VARIABLE.desc#">

                              <option value="0" <cfif not isDefined("FORM.desc")>selected="selected"</cfif>>All</option> //Here i am taking 'All' id as '0'  

            </cfselect>

       <cfselect name="company" id="company" bindattribute="option,selected"

                            bind="cfc:Utility.test.getname({desc})" bindonload="true"

                            value="SUB_ID" display="BSUBDISCIPLINE"></cfselect>

      <cfinput name="submit" value="Submit" type="submit" />                       

      </cfform>

BreakawayPaul wrote:

2) There's no way to have an "ALL" option.

   We can write query like:-(this is my cfc i am taking All id 0)

<cfcomponent>

          <cffunction name="getname" access="remote" returnformat="JSON" >

                    <cfargument name="id" required="true" />

                    <cfquery name="qname" datasource="mydb" >

                              select companyId as SUB_ID,

                                     companyname as BSUBDISCIPLINE

                                from company

                               <cfif ARGUMENTS.id neq 0>

                                         where companyId = #ARGUMENTS.id#

                               </cfif>

                    </cfquery>

                    <cfreturn qname>

          </cffunction>

</cfcomponent>

[BreakawayPaul]

Ok, I think I probably wasn't very clear in my question for #2.  For #1, I have a feeling my problem is the fact that we're still using CF8.  I've read in a few places that the "selected" attribute on the bound <select> doesn't work in CF8.

As for #2, what I want to do is have an ALL option (value 0) for the second <select>.  If I pick an option in the first <select>,  it populates the second <select> with the applicable values, but there seems to be no way to add an "ALL" option (or any other additional option that's not in the database).

My only idea for a workaround is to have a checkbox near the second <select> to chose ALL subdisciplines for the selected discipline, but that's a little clunky.  But I'm not sure what else to do.

[Ashish_Todon]

Hi Paul

BreakawayPaul wrote:

As for #2, what I want to do is have an ALL option (value 0) for the second <select>.  If I pick an option in the first <select>,  it populates the second <select> with the applicable values, but there seems to be no way to add an "ALL" option (or any other additional option that's not in the database).

I think i have already given answer of this question in my post 18 :-

<cfform name="test" >

            <cfselect name="desc" id="desc" query="qname" value="disc_id" display="adiscipline"

                           selected="#VARIABLE.desc#">

               <option value="0" <cfif not isDefined("FORM.desc")>selected="selected"</cfif>>All</option>

                //Here i am creating on addtional option which is not in query object 'All'

            </cfselect>

       <cfselect name="company" id="company" bindattribute="option,selected"

                            bind="cfc:Utility.test.getname({desc})" bindonload="true"

                            value="SUB_ID" display="BSUBDISCIPLINE"></cfselect>

      <cfinput name="submit" value="Submit" type="submit" />                      

      </cfform>

I thing this will help you.

Ashish

[BreakawayPaul]

Thanks.

This doesn't seem to be working for me.  In this line:

<Cfset VARIABLE.desc = isDefined("FORM.desc")?FORM.desc:"" />

That looks like a PHP ternary expression.  Are you trying to do something like this?

#iif(isDefined("FORM.desc"),FORM.desc,"")#

I'm just trying to get my head around what your suggestion is supposed to do.

[Ashish_Todon]

yes yes,

this is supported in cf9 inside cfscript (just a progamming habit)

<Cfset VARIABLE.desc = isDefined("FORM.desc")?FORM.desc:"" />

this is just a conditional statment.

same as

#iif(isDefined("FORM.desc"),FORM.desc,"")#

Sorry if i confused you.

<cfselect name="desc" id="desc" query="qname" value="disc_id" display="adiscipline"

                           selected="#VARIABLE.desc#">

               <option value="0" <cfif not isDefined("FORM.desc")>selected="selected"</cfif>>All</option>

                //Here i am creating on addtional option which is not in query object 'All'

            </cfselect>

Here i added addtional option 'ALL' which will be selected if "FORM.desc" is not declared.

Ashish

[BreakawayPaul]

Ashish Todon wrote:

Here i added addtional option 'ALL' which will be selected if "FORM.desc" is not declared.

Ashish

Yep, I had that too, and added your method.  The problem in either case is that as soon as you make a selection in the first <select> box, the second one populates with the code from the query in the CFC and ignores anything that's in between the <cfselect..> and </cfselect>.  You can still see the zero value option in the source code, but it doesn't appear in the <select>.

EDIT: Looks like it's a common problem.  At least on CF8.

http://stackoverflow.com/questions/3926312/coldfusion-ajax-how-to-get-blank-row-in-binded-select-box

http://stackoverflow.com/questions/13141330/binding-a-cfc-to-cfselect-box

[BreakawayPaul]

Solution:

In the CFC....

SELECT sub_id

          , bsubdiscipline

          , 1 AS selectorder

FROM search_fhwa_sub

WHERE parent_id = <cfqueryparam value="#arguments.disc_id#" cfsqltype="cf_sql_numeric">

UNION

SELECT 0 AS sub_id

          , 'ALL' AS bsubdiscipline

          , 0 AS selectorder

FROM search_fhwa_sub

ORDER BY selectorder

The selectorder is fake column that makes the ALL the first choice.