具有约束力和AJAX下拉 [英] Pull Down with binding and AJAX

问题描述

我是新来的ColdFusion和刚刚开始学习关于Ajax。我的问题是，我在网上找到一些很酷的编码上实现两个下拉菜单，在这里是第二个是依靠什么从第一选择。我们的目标虽然是拉在下拉列表中的所有值，从选择查询。

I am new to ColdFusion and just started learning about Ajax. The question I have is that I found on the web some cool coding to implement two pull down menus, where is the second one is dependent on what is selected from the first. The goal though is to pull all the values in the pull down list from a select query.

我现在用的是绑定功能，这是我刚刚得知。在第一个下拉一切都拉着正确。这个问题虽然是第二个。我从AJAX记录得到的误差错误调用：错误执行数据库查询任何意见将是极大的AP preciated。也谢谢你提前为您的帮助。

I am using the binding feature, which I just learned about. Everything in the first pull down is pulling correctly. The problem though is the second one. The error I am getting from AJAX logger is "Error invoking: Error Executing Database Query" Any advice would be greatly appreciated. Also thank you 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>

以上是处理所有查询的CFC。其中一个主要的原因是溶液的是形式中所需的code中的简单性。

The above is the cfc which handles all the queries. One of the main reason for this solution is the simplicity of the code that is required within 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>

我已经寻找了近2天尝试找到解决的查询错误。我知道，查询工作在SQL所以我相信这个问题已经做AJAX和不正确执行WHERE第二条往下拉。

I have searched for almost 2 days trying to find a solution to the query error. I know that the query works in SQL so I believe the issue has to do with AJAX and not correctly implementing the WHERE clause for the second pull down.

此外的任何意见或建议，将是巨大的。此外，如果你这样做解决方案的另一种方式，我更乐意去尝试这一点。正如我所说，我很新的这门语言。仅2周

Again any advice or suggestions would be great. Additionally if you would do this solution another way I am more than happy to try that as well. As I stated I am very new to this language. Only 2 weeks in.

推荐答案

测试CFC的

始终测试CFC在CF将它们插入阿贾克斯之前。没有一点用绑定搞乱，直到的在的你已经验证了CFC工作没有错误，因为如果它不CF的工作，它是不会与阿贾克斯擦出火花。唯一的区别是错误将很难找到。

Always test cfc's in CF before plugging them into ajax. There is no point messing with bindings until after you have verified the cfc works without error, because if it does not work in CF, it is not going work with ajax either. The only difference is the errors will be harder to find.

至于其他人则建议，从小事做起。由本身测试该查询。然后移动到要测试的CFC或者使用＆LT; CFINVOKE＆GT; 或只是从您的浏览器测试值调用它，如：

As others suggested, start small. Test the query by itself. Then move onto to testing the CFC either using <cfinvoke> or simply invoke 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

错误

在问候错误，我们需要看到完整的错误消息，以提供更具体的建议。然而，看着C语言带来的可能的原因查询/ $ C $是：

In regards to the error, we need to see the full error message to provide more specific advice. However, looking at the query/code some likely causes are:

1. CAT_DESC 是一个VARCHAR列，在这种情况下，你的论点必须用单引号。否则，数据库将认为该参数值是一个对象名称（表或列）。

1. CAT_DESC is a varchar column, in which case your argument must be enclosed in single quotes. Otherwise the database will think the argument value is an object name (table or column).

WHERE CAT_DESC = '#ARGUMENTS.CAT_DESC#' 

..或者 CAT_DESC 是一个数字列，但你的说法是空的。这将导致一个无效的SQL语句。您需要确保一个有效的数字传递到查询（或者你可以跳过WHERE子句时的说法是空的，这取决于所期望的结果）。一种常见的方法是使用<一个href="http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-6e64.html"相对=nofollow> VAL（） 功能为空字符串和其他非数字值转换到零，即：

.. OR CAT_DESC is a numeric column, but your argument is empty. That would result in an invalid sql statement. You need to ensure a valid number is passed into the query (Or you could skip the WHERE clause when the argument is empty, depending on the desired results). One common approach is using the val() function to convert empty strings and other non-numeric values to zero, ie:

，其中CAT_DESC = #val（ARGUMENTS.CAT_DESC）＃

它也像你有一个流浪＆LT; / CFIF＆gt;在第二个查询之后。 （假设它不是一个复制/粘贴错误）

It also looks like you have a stray </cfif> after the second query. (Assuming it is not a copy/paste error)

还有一件事，你的第二个查询同时指定数据源和 DBTYPE 。这两个属性是相互排斥的。同时具有的可以的会导致语法错误（我还没有尝试过）。无论哪种方式，你应该只使用其中一个（最有可能的数据源）。

One other thing, your second query specifies both datasource and dbtype. Those two attributes are mutually exclusive. Having both may cause a syntax error (I have not tried it). Either way you should only use one of them (most likely datasource).

SQL注入

这说的 - 上面的查询很容易受到SQL注入攻击。你应该总是使用<一个href="http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f6f.html"相对=nofollow> ＆LT; cfqueryparam＆GT; 所有变量的查询参数，以防止SQL注入。它具有其他的好处，以及（性能，数据类型检查，等等）。但是，SQL注入的保护是最关键的Web应用程序。

That said - the query above is vulnerable to sql injection. You should always use <cfqueryparam> on all variable query parameters to guard against sql injection. It has other benefits as well (performance, data type checking, etcetera). But sql injection protection is the most critical in a web application.

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

改进

由于CF8.0.1 +的，<一个href="http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7afe.html"相对=nofollow> cfselect 可以绑定到一个查询对象（不只是一个数组）。而不是构建一个数组所以，简单地从函数返回的原始查询。

As of CF8.0.1+, cfselect can bind to a query object (not just an array). So instead of building an array, simply return the raw query 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>

然后指定哪一列（S）使用了显示和值属性：

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

这篇关于具有约束力和AJAX下拉的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！