Using AJAX to get data from a ColdFusion component page, and fill another dropdown with results

Question

I know similar questions have been asked before and answered, but I'm having specific issues with my code. When the dropdown with id="SITE" is changed, I want the dropdown with id="YARD" to fill with yards from that site. Here is my component CFC code (in a page called AjaxFunctions.cfc):

<cfcomponent output="false">
<!---  Get YARDS dataset based on SITE  --->
    <cffunction name="getYards" access="remote" returntype="query" />
    <cfargument name="Site" type="string" required="true" /> 
    <cfquery name="rs_Yards" datasource="abc" username="notReal1" password="notReal2" >
        <!--- SQL code here --->
    </cfquery>
    <cfreturn rs_Yards />
    </cffunction>
 <cfcomponent>

And here is my receiving code in the head section of my calling page:

<script>
    $(document).ready(function() {
        $("#SITE").change(function() {
            alert( "SITE has changed." );// testing to see if jQuery works
            // empty YARD dropdown
            $("#YARD").empty();
            // perform ajax
            $.ajax({
                type: "GET",
                url: "AjaxFunctions.cfc",
                data: {
                    method: "getYards",
                    Site: $("#SITE").val()
                },
                datatype: "json",
                success: function(data) {
                    alert("We received the data."+data);
                    $.each(data, function () {
                        $("#YARD").append($("<option></option>").val(this['ITEMID']).html(this['ITEMDESC']));
                    }                   
                }
            });
        });
    });
</script>

Whey I try the code as is, absolutely nothing happens. When I comment out just these lines

$.each(data, function () {
    $("#YARD").append($("<option></option>").val(this['ITEMID']).html(this['ITEMDESC']));
} 

Then I get the notification "the SITE has changed" and the YARD dropdown empties, but the "We received the data..." alert looks like HTML code from an error page. I guess I can worry about updating the YARD dropdown later, right now I'm worried about just receiving the data from the query.

Answer 1

Are you able to change the cffunction? ColdFusion's JSON format for queries is goofy. IMO, it's simpler to change the function. Return an array of structures with the whatever key names you want, like "yard". (Don't forget to scope the function variables and change the returntype to "array")

<cfcomponent output="false">
   <cffunction name="getYards" access="remote" returntype="array" >
       <cfargument name="Site" type="string" required="true" /> 

       <cfset var rs_Yards = "">
       <cfset var response = []>

       <!--- demo data. replace this with your cfquery --->
       <cfset rs_Yards = QueryNew("")>
       <cfset queryAddColumn(rs_Yards, "Yard", ["X-745-C ","X-745-C5","X-745-E ","X-745-G "])>

       <cfloop query="rs_Yards">
           <cfset arrayAppend(response, { "yard": rs_Yards.yard })>
       </cfloop>

       <cfreturn response />
   </cffunction>
</cfcomponent>

Incorporate the other changes I mentioned in the comments:

• Javascript is case sensitive. Change datatype to dataType (capital "T")
• Change the URL to request JSON from the component: AjaxFunctions.cfc?method=getYards&returnformat=json

Finally, use the new key name, "yard", in the $.each() loop.

JQuery:

$(document).ready(function() {
    $("#SITE").change(function() {
        $("#YARD").empty();
        $.ajax({
            type: "GET",
            url: "AjaxFunctions.cfc?method=getYards&returnformat=JSON",
            data: {Site: $("#SITE").val()},
            dataType: "json",
            success: function(data) {
                $.each(data, function() {
                     $("#YARD").append($("<option></option>").val(this.yard).text(this.yard));
                }); 
            }
        });
    });
});

HTML:

<form>
   <select id="SITE" name="SITE">
      <option value="123">One</option>
      <option value="456">Two</option>
   </select>
   <select id="YARD">
       <option value="123">select somthing</option>
   </select>
</form>

Answer 2

Big thanks to @Ageax for all his help. I wound up going a much simpler route. Here is my function:

<cfcomponent output="false">
<!---  Get YARDS dataset based on SITE  --->
    <cffunction name="getYards" access="remote" returntype="string" returnformat="plain" >
        <cfargument name="Site" type="string" required="true" /> 
        <cfquery name="rs_Yards" datasource="MyDatasource" >
            <!--- SQL code here --->
        </cfquery>
        <cfset myList = ValueList(rs_Yards.Yard)>   <!--- convert query results to list --->
        <cfreturn MyList />
    </cffunction>
</cfcomponent>

And here is my jQuery on the calling page:

<!---  jQuery/AJAX to autofill dropdowns  --->
    <script>
    $(document).ready(function() {
        $("#SITE").change(function() {   // when the SITE dropdown changes 
            $("#YARD").empty();          // empty the YARD dropdown 
            $.ajax({
                type: "GET",
                url: "AjaxFunctions.cfc",
                data: {
                    Site: $("#SITE").val(),
                    Method: "getYards"
                    },
                dataType: "text",
                success: function(data) {
                    var options = data.split(",");              // convert returned list to array 
                    var select = document.getElementById('YARD');
                    for(var i=0;i<options.length; i++)
                    {
                        var options2 = $.trim(options[i]);      // clean up label portion 
                        $("#YARD").append($("<option></option>").val(options[i]).text(options2));   // append options to YARD dropdown 
                    }
                }
            });
        });
    });
    </script>

Hope this helps someone else.