使用绑定和 AJAX 下拉

Pull Down with binding and AJAX(使用绑定和 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#' 

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

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

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