Clever Geek Handbook

Pull down with anchor and AJAX

I'm new to ColdFusion and just started learning Ajax. The question I have is that I found some cool coding on the internet to implement two dropdown menus, where the second one depends on what is selected from the first. The goal is to pull all the values ​​in the dropdown from the selected query.

I am using the binding function I just learned about. Everything in the first pull twitches correctly. The problem, albeit the second. The error I am getting from the AJAX logger is "Error invoking: Error Executing Database Query"

. Also thanks in advance for your help.

select.cfc

<cfcomponent output="false">

    <!--- Get array of Catagory Description types --->
    <cffunction name="cat_description" access="remote" returnType="array">

        <!--- Define variables --->
        <cfset var data="">
        <cfset var result=ArrayNew(2)>
        <cfset var i=0>

        <!--- Get data --->
        <cfquery name="getServiceCat" datasource="SOME_DATABASE">
            SELECT DISTINCT CAT_DESC
            FROM   service_table
            ORDER BY CAT_DESC
        </cfquery>

        <!--- Convert results to array---> 
        <cfloop index="i" from="1" to="#getServiceCat.recordcount#">
            <cfset result[i][1]=getServiceCat.CAT_DESC[i]>
            <cfset result[i][2]=getServiceCat.CAT_DESC[i]>
        </cfloop>

        <!--- And return it --->
        <cfreturn result>
    </cffunction>

    <!--- Get Service Type by Cat description type --->
    <cffunction name="getServiceType2" access="remote" returnType="array">
        <cfargument name="CAT_DESC" type="string" required="true">

        <!--- Define variables --->
        <cfset var data="">
        <cfset var result=ArrayNew(2)>
        <cfset var i=0>

        <!--- Get data --->
        <cfquery name="getServiceType2" datasource="SOME_DATABASE" dbtype="query">
            SELECT DISTINCT com_service_code, report_desc
            FROM   service_table
            WHERE  CAT_DESC = #ARGUMENTS.CAT_DESC# 
            ORDER BY report_desc
        </cfquery>
        </cfif>

        <!--- Convert results to array --->
        <cfloop index="i" from="1" to="#getServiceType2.recordcount#">
            <cfset result[i][1]=getServiceType2.com_service_code[i]>
            <cfset result[i][2]=getServiceType2.report_desc[i]>
        </cfloop>

        <!--- And return it --->
        <cfreturn result>
    </cffunction>

 </cfcomponent>

The above cfc handles all requests. One of the main reasons for this decision is the simplicity of the code required in the form.

 ...
 <td>Select Category: <cfselect name="catdesc"
                                 bind="cfc:select.cat_description()"
                                 bindonload="true"/><br />
 </td>
 ...
 <td>Select Service:  <cfselect name="service"
                                bind="cfc:select.getServiceType2({catdesc})"
                                bindonload="false"/>
 </td>

I've searched for almost 2 days trying to find a solution to the request error. I know the query works in SQL, so I believe the issue is AJAX related and does not correctly implement the WHERE clause for the second output.

Again any advice or suggestions would be great. Also, if you do this solution differently, I am more than happy to try this. As I said, I am very new to this language. In just 2 weeks.

+3


source to share


2 answers


CFC testing

Always check the cfc in CF before connecting them to ajax. There is no point in fiddling with bindings until you have verified that cfc works without errors, because if it doesn't work in CF, it won't work with ajax. The only difference is that errors will be harder to find.

Like others, start small. Check the request yourself. Then go to testing the CFC either with <cfinvoke>

or just call it from your browser with test values, like:

http://yourserver/path/to/select.cfc?method=getServiceType2&cat_desc= 
http://yourserver/path/to/select.cfc?method=getServiceType2&cat_desc=someValue

Mistake

As for the error, we need to see the full error message in order to provide more specific guidance. However, looking at the request / code, some possible reasons are:

  • CAT_DESC

    is a varchar column, in which case your argument must be enclosed in single quotes. Otherwise, the database will read the argument value - this is the name of the object (table or column).

    WHERE CAT_DESC = '#ARGUMENTS.CAT_DESC#'

  • .. OR CAT_DESC

    is a numeric column, but your argument is empty. This will result in an invalid sql statement. You need to make sure that a valid number is passed to the query (or you can omit the WHERE clause when the argument is empty, depending on the results you want). One common approach is to use a function val()

    to convert empty strings and other non-numeric values ​​to zero, that is:

    WHERE CAT_DESC = #val(ARGUMENTS.CAT_DESC)#

  • It also looks like after the second request you have a roaming one </cfif>

    . (Assuming it's not a copy / paste error)

  • One more thing, your second query indicates both datasource

    and dbtype

    . These two attributes are mutually exclusive. If both can cause a syntax error (I haven't tried it). In any case, you should only use one of them (most likely datasource

    ).

SQL Injection



However, the query above is vulnerable to SQL injection. You should always use <cfqueryparam>

for all variable query parameters to protect against SQL injection. It also has other benefits (performance, data type checking, etc.). But sql intrusion protection is most important in a web application.

<!--- substitute the correct type for your db column --->
WHERE CAT_DESC = <cfqueryparam value="#ARGUMENTS.CAT_DESC#" 
                         cfsqltype="cf_sql_varchar">

<strong> Improvements

As for CF8.0.1 +, it cfselect

can bind to a request object (not just an array). So instead of building an array, we just return the raw request from the function.

    <cffunction name="getServiceType2" access="remote" returnType="query">
        <cfargument name="CAT_DESC" ....>

        <cfset var data="">

        <cfquery name="data" ..> 
           SELECT TheQueryColumnToDisplay, TheQueryColumnUsedForListValue
           FROM  ... 
           WHERE ... 
        </cfquery>

       <cfreturn data>
    </cffunction>

Then specify which columns are used for the attributes display

and value

:

    <cfselect name="service" 
              bind="cfc:select.getServiceType2({catdesc})"
              display="TheQueryColumnToDisplay"
              value="TheQueryColumnUsedForListValue" ...>
+5


source


My suggestion is to do one at a time. For your specific situation

First, ask your query to work with the cfquery tag.

Second, make it work inside the function where you are passing the argument to the function.

Then put the function inside cfc and call it from the cfc page using either or creating an object and calling the function.



Finally, do the binding.

This approach will make mistakes more visible so you can do something about them.

Other observations

  • use query parameters.
  • try to pass id field into your request instead of text description
+1


source






More articles:


All Articles