0

I am trying to populate a DIV with coldfusion query data based on a selection from a drop-down lsit. I am currently doing it by binding a CFDIV to a CFM action page but I am trying to do it via a pure JAVASCRIP solution instead.

So via an AJAX call to a CFC I am recieving the following error: 500 (Error Executing Database Query.)

The details of the error are:

Error Executing Database Query. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE customer_id = 211 AND alert_status = 'on'' at line 4

It doesn't like my last query in my CFC for some reason yet that query work in my other code where I bind to a CFDIV.

Here is my AJAX:

<script>
function PopulateCustomerAlertDIV(){
    // Populate the customer alert DIV based on the customer selection

    $.ajax({
        url:'cfcs/customerAlertDIV.cfc?method=getAlert&returnformat=json',
        data: { company_name: $("##company_name>option:selected").attr("Value") },
        success: function(response) {
            $("##CustomerAlertDIV").val( response );
            }
      });
}
</script>

Here is my DIV that I want to dynamically populate:

<div id="CustomerAlertDIV" style="display: block;"></div>

Here is my SelectBox:

<cfselect queryPosition="below" name="company_name" id="company_name" value="company_name" bind="cfc:cfcs.taxdata.getData()" bindonload="true"   tabindex="0" onchange="PopulateCustomerAlertDIV();" >
            <option>---Make A Selection---</option>
</cfselect>

Here is my CFC:

<cffunction name="getAlert" access="remote" returnType="string">
<cfargument name="company_name" type="any" required="true">

<!--- localize function variables --->
<cfset var dataDetail = "">

<cfquery name="getID" datasource="#datasource#" >
select customer_id
from customer_table
where company_name = <cfqueryparam value="#ARGUMENTS.company_name#" cfsqltype="cf_sql_varchar">
</cfquery> 


<cfquery name="dataDetail" datasource="#datasource#">
    SELECT ID, alert, alert_priority
    FROM   customer_alerts
    <!--- adjust cfsqltype if needed --->
    WHERE WHERE customer_id = #getID.customer_id# AND alert_status = 'on'
</cfquery>

<cfreturn dataDetail.alert>
</cffunction></cfcomponent>
Brian Fleishman
  • 1,237
  • 3
  • 21
  • 43
  • 1
    And, stop using `cfdiv`, this is very easy to accomplish using jQuery or any other proper JavaScript library. The ColdFusion UI elements are more trouble than they are worth. They are out dated, poorly implemented and severely limited in functionality. – Scott Stroz Mar 05 '15 at 15:31
  • 1
    I think Scott was referring to `cfselect`, as you already trying to eliminate the `cfdiv.` As far as your original question, you just need construct a string. Since "company" is a plain javascript variable, get rid of the braces and move it outside the quotes so it is evaluated, ie `load( 'Data.cfm?company='+ company )`. A few other notes a) the first query [may be vulnerable to sql injection](http://stackoverflow.com/a/18797042/104223) b) It is simpler to pass a numeric id, rather than a name, which must be url encoded. c) No need for two queries. A single JOIN would do it. – Leigh Mar 05 '15 at 15:57
  • Thanks. I typically paramaterize my queries after I have a working example. It is just easier to visualize in mind for some reason. So I think I have a better and simpler way of doing it using a CFC as @DanBracuk suggested. Should I edit my original question with my new code? – Brian Fleishman Mar 05 '15 at 19:57
  • Edit my question with a completely revamped way of doing this. A lot less code but getting a SQL error now for unknown reasons. – Brian Fleishman Mar 05 '15 at 20:15
  • @BrianFleishman you have two `WHERE` clauses in your SQL query – Matt Busche Mar 05 '15 at 20:20
  • Oh boy, thats embarrasing. Guess Ive been staring at this too. Long. So needless to say Im not recieivng the SQL error anymore. In fact no more errors in my console log. But my DIV is not being populated with my response. Am I not returning the data correctly? – Brian Fleishman Mar 05 '15 at 20:24
  • The error was in my AJAX success. Changed it to: $("##CustomerAlertDIV").append( response ); All is working now. – Brian Fleishman Mar 05 '15 at 20:42
  • *I typically paramaterize my queries after* IMHO, that is part of getting things "working" :) However, you may want to include a disclaimer in questions ie "My real code has cfqueryparam!" If only to stave off the inevitable slew of cfqueryparam comments like mine ;-) – Leigh Mar 05 '15 at 22:35
  • I know, after I submitted that post I realized I was gonna get hammered with the parameterized comments. Happens everytime! – Brian Fleishman Mar 05 '15 at 23:21

0 Answers0