使用绑定和 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 记录器得到的错误是 "Error invoking: E​​rror Executing Database Query" 任何建议将不胜感激.也提前感谢您的帮助.

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.此解决方案的主要原因之一是表单中所需代码的简单性.

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 插入 ajax 之前，始终在 CF 中测试它们.直到 验证了 cfc 可以正常工作，因为如果它不能在 CF 中工作，那么它也不能与 ajax 一起工作.唯一的区别是错误会更难找到.

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.

正如其他人建议的那样，从小处着手.自行测试查询.然后继续使用 <cfinvoke> 或简单地从浏览器中使用以下测试值调用它来测试 CFC:

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

错误

关于错误，我们需要查看完整的错误消息以提供更具体的建议.但是，查看查询/代码一些可能的原因是:

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#' 

.. OR CAT_DESC 是数字列，但您的参数为空.这将导致无效的 sql 语句.您需要确保将有效数字传递到查询中(或者您可以在参数为空时跳过 WHERE 子句，具体取决于所需的结果).一种常见的方法是使用 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:

WHERE CAT_DESC = #val(ARGUMENTS.CAT_DESC)#

在第二次查询之后，您似乎还有一个杂散的 </cfif>.(假设不是复制/粘贴错误)

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

另外，您的第二个查询同时指定了 datasource 和 dbtype.这两个属性是互斥的.同时拥有可能会导致语法错误(我没有尝试过).无论哪种方式，您都应该只使用其中一个(很可能是 datasource).

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 注入的影响.您应该始终使用 <cfqueryparam> 在所有变量查询参数上以防止 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+ 开始，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>

然后指定 display 和 value 属性使用哪些列:

Then specify which column(s) to use for the display and value attributes:

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

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