Coldfusion and Related Selects

Question

For several years, I have used dropdowns to determine the requirements for a SQL query and have never needed to use dropdowns with ‘related selects’ until now. I found a sample on how to do this and I can create related selects (with static data) which works well, see below using my data relating to vehicles and makes.

However, when I come to apply this to my environment, and use dynamic SQL query data, I cannot get it to work. I am confident that all the pieces of code are working together (as it works perfectly for the static data), but if I try and build the data automatically and then create the string to pass to the queryNew function, it fails.

This is the working Content.cfc file with STATIC query strings/data array

<cfcomponent>

    <cfset tblMake = queryNew("name,id", "varchar,varchar", [{name:'RENAULT',id:'RENAULT'},{name:'RENAULT',id:'RENAULT'},{name:'RENAULT',id:'RENAULT'}])>
    <cfset tblModel = queryNew("name,code,continent_id", "varchar,varchar,varchar", [{name:"TSERIES",code:"TSERIES",continent_id:"RENAULT"},{name:"MASTER",code:"MASTER",continent_id:"RENAULT"}])>
    <cfset tblVoltage = queryNew("name,code", "varchar,varchar", [{name:"24 volt",code:"MASTER"},{name:"12 volt",code:"TSERIES"}])>

   <cffunction name="getContent" access="remote" returntype="query" output="true">
      <cfargument name="strTableName" type="string" required="true">
      <cfargument name="strID" type="string" required="true">
      <cfargument name="strName" type="string" required="true">
      <cfargument name="intDistinct" type="numeric" required="false" default="0">
      <cfargument name="selectedCol" type="string" required="false" default="0">
      <cfargument name="selectedID" type="string" required="false" default="0">
      <cfquery name="qryContent" dbtype="query">
         select
         <cfif arguments.intDistinct eq 1>distinct</cfif>
         #arguments.strID# as theID,
         #arguments.strName# as theValue
         from #arguments.strTableName#
         <cfif arguments.selectedID neq 0>
            where #arguments.selectedCol# = '#arguments.selectedID#'
         </cfif>
         order by #arguments.strName#
      </cfquery>
      <cfreturn qryContent />
   </cffunction>
</cfcomponent>

When I try and create the query using this method, nothing appears to work. I am looping through the database (from a query) and then creating a string using the required structure and the passing it to the function.

<cfcomponent>

    <cfset XSTATIC = ""/>
    <cfquery name="NOXVehicleModels" datasource="EBSNOX" >
        SELECT DISTINCT VehicleMake  
        FROM [dbo].[NOX-Master]
        WHERE VehicleMake IS NOT NULL
    </cfquery>
    <cfloop query="NOXVehicleModels">
      <cfset XSTATIC = XSTATIC & "{name:'" & #Trim(NOXVehicleModels.VehicleMake)# & "',id:'"& #Trim(NOXVehicleModels.VehicleMake)# & "'},"/>
    </cfloop>
    <cfset XLEN=LEN(#XSTATIC#)/>
    <cfset XSTATIC = MID(XSTATIC,1,XLEN-1)/>
    <cfoutput>#XSTATIC#</cfoutput> 

    <cfset tblMake = queryNew("name,id", "varchar,varchar", [#XSTATIC#])>

I have created a separate cfoutput to test the string for structure etc and it appears to be correct, but it is just not passing to the querynew function. This is what the output looks like:-

{name:'CUMMINS',id:'CUMMINS'},{name:'DAF',id:'DAF'},{name:'IVECO',id:'IVECO'},{name:'MAN',id:'MAN'},{name:'MERCEDES',id:'MERCEDES'},{name:'RENAULT',id:'RENAULT'},{name:'SCANIA',id:'SCANIA'},{name:'VOLVO',id:'VOLVO'} {name:'RENAULT',id:'RENAULT'},{name:'RENAULT',id:'RENAULT'},{name:'RENAULT',id:'RENAULT'}

What I have done so far:-

1. I have checked that the quotes are not important i.e. single/double.
2. I have tried to build the query outside of the tag.

Again, all appears (?) to be in order and I cannot understand that could be the problem. Possibly the structure is missing something before parsed to the function?

Any help would be greatly appreciated.

Thanks, Jack