19 Replies Latest reply on Nov 27, 2013 8:40 AM by Carl Von Stetten

    Flexible Criteria Search?

    seasonedweb

      Greetings

       

      I contsructed a form that allows the user to search up to five criteria:

       

      Date range,

      appraiser,

      order status,

      client

      priority

       

      I've attempted to set the action page:

       

      <cfquery name="search_flexible_criteria" datasource="#Request.BaseDSN#">

      SELECT order_ID, order_number, order_client_ID, order_rush_status_ID, main_orders.order_status_ID, order_appraiser_ID, order_create_date, order_property_street, order_property_city, client_ID, client_company, appraiser_ID, appraiser_fname, appraiser_lname, lookup_order_status.order_status_ID, order_status, rush_status_ID, rush_status

      FROM  main_orders MO, lookup_clients LC, lookup_appraisers LA, lookup_order_status LS, lookup_rush_status LR

      WHERE 1 = 1

       

      <cfif IsDefined("Form.StartDate")>

      <cfset Session.StartDate = "#CreateODBCDate(FORM.StartDate)#">

      <cfset Session.EndDate = "#CreateODBCDate(FORM.EndDate)#">

      AND (order_create_date BETWEEN #Session.StartDate# AND #Session.EndDate#)</cfif>

       

      <cfif IsDefined("Form.order_client_ID") AND Form.order_client_ID NEQ 0>

       

      AND    client_ID  = #Form.order_client_ID# </cfif>

       

      <cfif IsDefined("Form.order_appraiser_ID") AND Form.order_appraiser_ID NEQ 0>

        AND  appraiser_ID  = #Form.order_appraiser_ID#</cfif>

       

       

      <cfif IsDefined("Form.order_status_ID") AND Form.order_status_ID NEQ 0>

         AND   MO.order_status_ID = #Form.order_status_ID#

         </cfif>

        

      <cfif IsDefined("Form.order_rush_status_ID") AND Form.order_rush_status_ID NEQ 0>

          AND  rush_status_ID = #Form.order_rush_status_ID#

          </cfif>

       

      ORDER BY order_create_date DESC

       

      </cfquery>  

       

      The first error encountered is "AND  rush_status_ID = #Form.order_rush_status_ID#"

       

      Not yet got this to work- any advice would be greatly appreciated.

       

      Thanks

       

      seasonedweb

        • 1. Re: Flexible Criteria Search?
          Carl Von Stetten Adobe Community Professional & MVP

          There are a few problems I see with your query:

          1. Your FROM clause lists 5 tables, but no join statements to tie them together (or alternately, any WHERE clause statements that tie them together).  You need to add some INNER or LEFT JOINs to make it work.
          2. You've assigned aliases to the tables (MO, LC, LA, LS, LR), but you don't use them in identifying where the columns in your SELECT and WHERE clause are coming from.  Prefix each column name with the appropriate alias (e.g.: MO.order_ID).
          3. You should use StructKeyExists() instead of IsDefined() for performance reasons.  So the first one would be <cfif StructKeyExists(Form, "StartDate")>.
          4. You should also check to make sure Form.EndDate is provided as well.
          5. Unless there is a reason you need to do this in some other place in your code, you don't need to put the ODBCDates into session.  In fact, depending on the format of the date input field in your form, you can use CFQueryParam to make sure the date is passed to the database in a valid format.
          6. **USE CFQUERYPARAM**!!  Whenever you pass values to a query that come from a source you can't absolutely trust not to be a hacking attempt (especially form fields), ALWAYS use CFQueryParam to prevent SQL Injection.  This will also make sure numbers, strings, and dates are passed to the database in the proper formats.  It also can improve query performance as the database can reuse the query plan from previous times the query has run, even if the values passed to the query are changing.

           

          If after addressing all of these issues the query continues to throw errors, post your updated query definition and we'll take another look.

           

          HTH,

          -Carl V.

          • 2. Re: Flexible Criteria Search?
            seasonedweb Level 1

            Carl:

             

            Based on your feedback, I did this:

             

             

             

            <cfquery name="search_flexible_criteria" datasource="#Request.BaseDSN#">

            SELECT order_ID, order_number, order_client_ID, order_rush_status_ID, main_orders.order_status_ID, order_appraiser_ID, order_create_date, order_property_street, order_property_city, client_ID, client_company, appraiser_ID, appraiser_fname, appraiser_lname, lookup_order_status.order_status_ID, order_status, rush_status_ID, rush_status

            FROM  main_orders, lookup_clients, lookup_appraisers, lookup_order_status, lookup_rush_status

            WHERE order_client_ID = client_ID

            AND order_appraiser_ID = appraiser_ID

            AND main_orders.order_status_ID = lookup_order_status.order_status_ID

            AND order_rush_status_ID = rush_status_ID

             

            <cfif StructKeyExists(Form, "StartDate")>

            <cfset StartDate = "#FORM.StartDate#">

            <cfset EndDate = "#FORM.EndDate#">

             

            AND (order_create_date BETWEEN #StartDate# AND #EndDate#)</cfif>

             

            <cfif StructKeyExists(Form, "order_client_ID") AND Form.order_client_ID NEQ 0>

             

             

            AND client_ID  = #Form.order_client_ID# </cfif>

             

             

            <cfif StructKeyExists(Form, "order_appraiser_ID") AND Form.order_appraiser_ID NEQ 0>

             

             

            AND  appraiser_ID  = #Form.order_appraiser_ID#</cfif>

             

             

            <cfif StructKeyExists(Form, "order_status_ID") AND Form.order_status_ID NEQ 0>

             

            AND order_status_ID = #Form.order_status_ID#</cfif>

             

             

             

            <cfif StructKeyExists(Form, "order_rush_status_ID") AND Form.order_rush_status_ID NEQ 0>

             

            AND  rush_status_ID = #Form.order_rush_status_ID#</cfif>

             

            ORDER BY order_create_date DESC

             

            </cfquery>  

             

            My query results are:

             

            <cfset nowtime = now()>

             

            <cfoutput query="search_flexible_criteria">

             

            <tr bgcolor="###iif(currentrow MOD 2,DE('ffffff'),DE('d9ecff'))#">

             

            <td width="8%" class="tddynamic">#DateDiff("d", order_create_date, nowtime)# </td>

             

            <td width="10%" class="tddynamic">#DateFormat(order_create_date, "mm/dd/yyyy")#</td>

             

            <td width="8%" class="tddynamic">#order_number#</td>

             

            <td width="15%" class="tddynamic">#order_property_street# #order_property_city#</td>

             

             

            <td width="12%" class="tddynamic">#order_status#</td>

             

            <td width="8%" class="tddynamic">#rush_status#</td>

             

            <td width="10%" class="tddynamic">#appraiser_lname#, #appraiser_fname#</td>

             

             

            </tr>

            </cfoutput>

             

            Is this closer to the solution (other than not using cfqueryparam)?

             

            Thanks again

             

            Norman B.

            • 3. Re: Flexible Criteria Search?
              Carl Von Stetten Adobe Community Professional & MVP

              Norman,

               

              Inside your CFQUERY tag you should still make use of table aliases (it looks like you actually removed them entirely this time), or prefix each mention of a database column with the full table name (using aliases requires much less typing though).  This is a good habit to get into, as it is not uncommon for multiple tables to have identically named columns.  Without prefixing the columns with the table name or alias, the database will throw an error about ambiguous column names.

               

              Is this new query you provided working as expected now?  Or is it still throwing errors?

               

              -Carl V.

              • 4. Re: Flexible Criteria Search?
                seasonedweb Level 1

                Carl:

                 

                If I run the action page with no form search, it simply returns all records (about 3000) .

                 

                If I use the form page to select any criteria (say, orders by appraiser/date range), it errors here:

                 

                  

                Error Executing Database Query.

                [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'AND'.
                The error occurred in C:/inetpub/wwwroot/appraiseri/admin/reports/reports_multiple_search_action.cfm: line 39
                37 : <cfif StructKeyExists(Form, "order_rush_status_ID") AND Form.order_rush_status_ID NEQ 0> 38 : 
                39 : AND order_rush_status_ID = #Form.order_rush_status_ID#</cfif>
                • 5. Re: Flexible Criteria Search?
                  Carl Von Stetten Adobe Community Professional & MVP

                  Can you post the full stacktrace from the error?  That way we can see the exact SQL that is being submitted to the database.  If you need to, sanitize any sensitive information (username/password, etc.).

                   

                  -Carl V.

                  • 6. Re: Flexible Criteria Search?
                    seasonedweb Level 1

                    OK:

                     

                    Error Executing Database Query.

                    [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'AND'.
                    The error occurred in C:/inetpub/wwwroot/appraiseri/admin/reports/reports_multiple_search_action.cfm: line 39
                    37 : <cfif StructKeyExists(Form, "order_rush_status_ID") AND Form.order_rush_status_ID NEQ 0> 38 : 39 : AND order_rush_status_ID = #Form.order_rush_status_ID#</cfif> 40 : 41 : ORDER BY order_create_date DESC 

                    VENDORERRORCODE  156
                    SQLSTATE  HY000
                    SQL   SELECT order_ID, order_number, order_client_ID, order_rush_status_ID, main_orders.order_status_ID, order_appraiser_ID, order_create_date, order_property_street, order_property_city, client_ID, client_company, appraiser_ID, appraiser_fname, appraiser_lname, lookup_order_status.order_status_ID, order_status, rush_status_ID, rush_status FROM main_orders, lookup_clients, lookup_appraisers, lookup_order_status, lookup_rush_status WHERE order_client_ID = client_ID AND order_appraiser_ID = appraiser_ID AND main_orders.order_status_ID = lookup_order_status.order_status_ID AND order_rush_status_ID = rush_status_ID AND (order_create_date BETWEEN AND ) AND appraiser_ID = 5 AND order_status_ID = 10 ORDER BY order_create_date DESC
                    DATASOURCE  appraiseriSQL
                    Resources:
                    Browser  Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/6.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; InfoPath.2)
                    Remote Address  127.0.0.1
                    Referrer  http://127.0.0.1/appraiseri/admin/reports/reports_multiple_search.cfm
                    Date/Time  22-Nov-13 07:37 PM
                    Stack Trace (click to expand)
                    at cfreports_multiple_search_action2ecfm1645272818.runPage(C:/inetpub/wwwroot/appraiseri/adm in/reports/reports_multiple_search_action.cfm:39) at cfreports_multiple_search_action2ecfm1645272818.runPage(C:/inetpub/wwwroot/appraiseri/adm in/reports/reports_multiple_search_action.cfm:39)

                    java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'AND'. at macromedia.jdbc.sqlserverbase.ddca.b(Unknown Source) at macromedia.jdbc.sqlserverbase.ddca.a(Unknown Source) at macromedia.jdbc.sqlserverbase.ddb9.b(Unknown Source) at macromedia.jdbc.sqlserverbase.ddb9.a(Unknown Source) at macromedia.jdbc.sqlserver.tds.ddr.v(Unknown Source) at macromedia.jdbc.sqlserver.tds.ddr.a(Unknown Source) at macromedia.jdbc.sqlserver.tds.ddr.a(Unknown Source) at macromedia.jdbc.sqlserver.ddj.m(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.e(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.a(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.v(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.r(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.execute(Unknown Source) at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:359) at coldfusion.sql.Executive.executeQuery(Executive.java:1442) at coldfusion.sql.Executive.executeQuery(Executive.java:1201) at coldfusion.sql.Executive.executeQuery(Executive.java:1131) at coldfusion.sql.SqlImpl.execute(SqlImpl.java:406) at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:1056) at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:685) at cfreports_multiple_search_action2ecfm1645272818.runPage(C:\inetpub\wwwroot\appraiseri\admin\reports\reports_multiple_search_action.cfm:39) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:244) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:444) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65) at coldfusion.filter.IpFilter.invoke(IpFilter.java:64) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:443) at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48) at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40) at coldfusion.filter.PathFilter.invoke(PathFilter.java:112) at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:30) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:94) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62) at coldfusion.filter.RequestThrottleFilter.invoke(RequestThrottleFilter.java:151) at coldfusion.CfmServlet.service(CfmServlet.java:204) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:928) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:414) at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:203) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:539) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:298) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:662) 
                    • 7. Re: Flexible Criteria Search?
                      Carl Von Stetten Adobe Community Professional & MVP

                      There is a case where code line ColdFusion is reporting as the problem is not actually where the problem is (it's just where the problem manifests).  The problem is this line:

                       

                      <cfif StructKeyExists(Form, "StartDate")>

                       

                      That key (and its related Form.EndDate key) will likely always exist, but are not always filled in.  However, since you aren't trapping for an empty or invalid entry in either of the fields, ColdFusion is **ALWAYS** executing the three lines of code that follow it.  You need to revise it to at least the following:

                       

                      <cfif StructKeyExists(Form, "StartDate") AND StructKeyExists(Form, "EndDate") AND form.StartDate NOT IS "" AND form.EndDate NOT IS "">

                       

                      You could use "NEQ" instead of "NOT IS", but I prefer to reserve "EQ" and "NEQ" for numeric equals comparisons (per the ColdFusion docs explanation of EQ and IS).

                       

                      Again, I want to emphasize using <CFQueryParam> anywhere you pass form data to a query.  In this case, it will also validate that the user has entered data that can be properly parsed as a date before sending the query on its way.  If the data can't be properly parsed, it should throw an error that will you will more easily understand.

                       

                      -Carl V.

                      • 8. Re: Flexible Criteria Search?
                        seasonedweb Level 1

                        Invalid CFML construct found on line 19 at column 98.

                        ColdFusion was looking at the following text:

                        NOT

                        The CFML compiler was processing:

                        • A cfif tag beginning on line 19, column 2.
                        • A cfif tag beginning on line 19, column 2.
                        The error occurred in C:/inetpub/wwwroot/appraiseri/admin/reports/reports_multiple_search_action.cfm: line 19
                        17 : <cfset EndDate = "#FORM.EndDate#"> 18 : 19 : <cfif StructKeyExists(Form, "StartDate") AND StructKeyExists(Form, "EndDate") AND form.StartDate NOT IS "" AND form.EndDate NOT IS ""> 20 : 21 :

                        Resources:
                        Browser  Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/6.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; InfoPath.2)
                        Remote Address  127.0.0.1
                        Referrer  http://127.0.0.1/appraiseri/admin/reports/reports_multiple_search.cfm
                        Date/Time  22-Nov-13 08:06 PM
                        Stack Trace (click to expand)


                        coldfusion.compiler.ParseException: Invalid CFML construct found on line 19 at column 98. at coldfusion.compiler.cfml40.generateParseException(cfml40.java:13130) at coldfusion.compiler.cfml40.jj_consume_token(cfml40.java:13001) at coldfusion.compiler.cfml40.cfif(cfml40.java:308) at coldfusion.compiler.cfml40.cfml(cfml40.java:4554) at coldfusion.compiler.cfml40.cfelse(cfml40.java:442) at coldfusion.compiler.cfml40.cfif(cfml40.java:316) at coldfusion.compiler.cfml40.cfml(cfml40.java:4554) at coldfusion.compiler.cfml40.start(cfml40.java:4994) at coldfusion.compiler.NeoTranslator.parsePage(NeoTranslator.java:694) at coldfusion.compiler.NeoTranslator.parsePage(NeoTranslator.java:675) at coldfusion.compiler.NeoTranslator.parseAndTransform(NeoTranslator.java:428) at coldfusion.compiler.NeoTranslator.translateJava(NeoTranslator.java:370) at coldfusion.compiler.NeoTranslator.translateJava(NeoTranslator.java:147) at coldfusion.runtime.TemplateClassLoader$TemplateCache$1.fetch(TemplateClassLoader.java:436) at coldfusion.util.LruCache.get(LruCache.java:180) at coldfusion.runtime.TemplateClassLoader$TemplateCache.fetchSerial(TemplateClassLoader.java:362) at coldfusion.util.AbstractCache.fetch(AbstractCache.java:58) at coldfusion.util.SoftCache.get_statsOff(SoftCache.java:133) at coldfusion.util.SoftCache.get(SoftCache.java:81) at coldfusion.runtime.TemplateClassLoader.findClass(TemplateClassLoader.java:609) at coldfusion.filter.PathFilter.invoke(PathFilter.java:101) at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:30) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:94) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62) at coldfusion.filter.RequestThrottleFilter.invoke(RequestThrottleFilter.java:151) at coldfusion.CfmServlet.service(CfmServlet.java:204) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:928) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:414) at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:203) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:539) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:298) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:662) 
                        • 9. Re: Flexible Criteria Search?
                          Carl Von Stetten Adobe Community Professional & MVP

                          Oops.  Sorry about that.  Should be "IS NOT" instead of "NOT IS".

                          -Carl V.

                          • 10. Re: Flexible Criteria Search?
                            seasonedweb Level 1

                            Carl:

                             

                            OK this:

                             

                            <cfquery name="search_flexible_criteria" datasource="#Request.BaseDSN#">

                            SELECT

                            mo.order_ID,

                            mo.order_number,

                            mo.order_property_type_ID,

                            mo.order_order_type_ID,

                            mo.order_report_type_ID,

                            mo.order_client_ID,

                            mo.order_appraiser_ID,

                            mo.order_status_ID,

                            mo.order_rush_status_ID,

                            mo.order_property_street,

                            mo.order_property_city,

                            mo.order_create_date,

                             

                            ls.order_status_ID,

                            ls.order_status,

                             

                             

                            lc.client_ID,

                            lc.client_company,

                             

                            la.appraiser_ID,

                            la.appraiser_fname,

                            la.appraiser_lname,

                             

                            lo.order_type_ID,

                            lo.order_type,

                             

                            lp.property_type_ID,

                            lp.property_type,

                             

                            lr.report_type_ID,

                            lr.report_type,

                             

                            lrs.rush_status_ID,

                            lrs.rush_status

                             

                             

                            FROM  (((((main_orders mo

                             

                               

                                   

                                    LEFT JOIN lookup_status ls

                                    ON mo.order_status_ID = ls.order_status_ID)

                                   

                                    LEFT JOIN lookup_clients lc

                                    ON mo.order_client_ID = lc.client_ID)

                                   

                                   

                                    LEFT JOIN lookup_appraisers la

                                    ON mo.order_appraiser_ID = la.appraiser_ID)

                                   

                                    LEFT JOIN lookup_order_type lo

                                    ON mo.order_order_type_ID = lo.order_type_ID)

                                   

                                    LEFT JOIN lookup_property_type lt

                                    ON mo.order_property_type_ID = lt.property_type_ID)

                                   

                                    LEFT JOIN lookup_report_type lr

                                    ON mo.order_report_type_ID = lr.report_type_ID)

                                   

                                   

                                    LEFT JOIN lookup_rush_status lrs

                                    ON mo.order_rush_status_ID = lrs.rush_status_ID

                                   

                                    WHERE 1 = 1

                             

                             

                             

                             

                            <cfif StructKeyExists(Form, "StartDate") AND StructKeyExists(Form, "EndDate") AND form.StartDate IS NOT  "" AND form.EndDate IS NOT  "">

                             

                            AND (order_create_date BETWEEN #FORM.StartDate# AND #FORM.EndDate#)</cfif>

                             

                            <cfif StructKeyExists(Form, "order_client_ID") AND Form.order_client_ID NEQ 0>

                             

                             

                            AND client_ID  = #Form.order_client_ID# </cfif>

                             

                             

                            <cfif StructKeyExists(Form, "order_appraiser_ID") AND Form.order_appraiser_ID NEQ 0>

                             

                             

                            AND  appraiser_ID  = #Form.order_appraiser_ID#</cfif>

                             

                             

                            <cfif StructKeyExists(Form, "order_status_ID") AND Form.order_status_ID NEQ 0>

                             

                            AND order_status_ID = #Form.order_status_ID#</cfif>

                             

                            <cfif StructKeyExists(Form, "order_rush_status_ID") AND Form.order_rush_status_ID NEQ 0>

                             

                            AND order_rush_status_ID = #Form.order_rush_status_ID#</cfif>

                             

                             

                            GROUP   

                                BY    

                               

                                mo.order_ID,

                            mo.order_number,

                            mo.order_property_type_ID,

                            mo.order_order_type_ID,

                            mo.order_report_type_ID,

                            mo.order_client_ID,

                            mo.order_appraiser_ID,

                            mo.order_status_ID,

                            mo.order_rush_status_ID,

                            mo.order_property_street,

                            mo.order_property_city,

                            mo.order_create_date,

                             

                            ls.order_status_ID,

                            ls.order_status,

                             

                             

                            lc.client_ID,

                            lc.client_company,

                             

                            la.appraiser_ID,

                            la.appraiser_fname,

                            la.appraiser_lname,

                             

                            lo.order_type_ID,

                            lo.order_type,

                             

                            lp.property_type_ID,

                            lp.property_type,

                             

                            lr.report_type_ID,

                            lr.report_type,

                             

                            lrs.rush_status_ID,

                            lrs.rush_status

                             

                            ORDER BY order_create_date DESC

                             

                            </cfquery>  

                             

                            Error Executing Database Query.

                            [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ')'.
                            The error occurred in C:/inetpub/wwwroot/appraiseri/admin/reports/reports_multiple_search_action.cfm: line 99
                            97 : <cfif StructKeyExists(Form, "order_rush_status_ID") AND Form.order_rush_status_ID NEQ 0> 98 : 99 : AND order_rush_status_ID = #Form.order_rush_status_ID#</cfif> 100 : 101 :
                            • 11. Re: Flexible Criteria Search?
                              Carl Von Stetten Adobe Community Professional & MVP

                              You'll need to post the full stacktrace of the error (or at least the compliled SQL statement).

                               

                              Also, you should be able to safely remove the entire GROUP BY clause, as it is unnecessary (you aren't doing any aggregation in the SELECT statement, so it serves no purpose).

                               

                              -Carl V.

                              • 12. Re: Flexible Criteria Search?
                                seasonedweb Level 1

                                Carl:

                                 

                                I removed the "group by"- here is the error:

                                 

                                Error Executing Database Query.

                                [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ')'.
                                The error occurred in C:/inetpub/wwwroot/appraiseri/admin/reports/reports_multiple_search_action.cfm: line 99
                                97 : <cfif StructKeyExists(Form, "order_rush_status_ID") AND Form.order_rush_status_ID NEQ 0> 98 : 99 : AND order_rush_status_ID = #Form.order_rush_status_ID#</cfif> 100 : 101 : ORDER BY order_create_date DESC 

                                VENDORERRORCODE  102
                                SQLSTATE  HY000
                                SQL   SELECT mo.order_ID, mo.order_number, mo.order_property_type_ID, mo.order_order_type_ID, mo.order_report_type_ID, mo.order_client_ID, mo.order_appraiser_ID, mo.order_status_ID, mo.order_rush_status_ID, mo.order_property_street, mo.order_property_city, mo.order_create_date, ls.order_status_ID, ls.order_status, lc.client_ID, lc.client_company, la.appraiser_ID, la.appraiser_fname, la.appraiser_lname, lo.order_type_ID, lo.order_type, lp.property_type_ID, lp.property_type, lr.report_type_ID, lr.report_type, lrs.rush_status_ID, lrs.rush_status FROM (((((main_orders mo LEFT JOIN lookup_status ls ON mo.order_status_ID = ls.order_status_ID) LEFT JOIN lookup_clients lc ON mo.order_client_ID = lc.client_ID) LEFT JOIN lookup_appraisers la ON mo.order_appraiser_ID = la.appraiser_ID) LEFT JOIN lookup_order_type lo ON mo.order_order_type_ID = lo.order_type_ID) LEFT JOIN lookup_property_type lt ON mo.order_property_type_ID = lt.property_type_ID) LEFT JOIN lookup_report_type lr ON mo.order_report_type_ID = lr.report_type_ID) LEFT JOIN lookup_rush_status lrs ON mo.order_rush_status_ID = lrs.rush_status_ID WHERE 1 = 1 ORDER BY order_create_date DESC
                                DATASOURCE  appraiseriSQL
                                Resources:
                                Browser  Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/6.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; InfoPath.2)
                                Remote Address  127.0.0.1
                                Referrer 
                                Date/Time  25-Nov-13 05:38 PM
                                Stack Trace (click to expand)
                                at cfreports_multiple_search_action2ecfm1645272818.runPage(C:/inetpub/wwwroot/appraiseri/adm in/reports/reports_multiple_search_action.cfm:99) at cfreports_multiple_search_action2ecfm1645272818.runPage(C:/inetpub/wwwroot/appraiseri/adm in/reports/reports_multiple_search_action.cfm:99)

                                java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ')'. at macromedia.jdbc.sqlserverbase.ddca.b(Unknown Source) at macromedia.jdbc.sqlserverbase.ddca.a(Unknown Source) at macromedia.jdbc.sqlserverbase.ddb9.b(Unknown Source) at macromedia.jdbc.sqlserverbase.ddb9.a(Unknown Source) at macromedia.jdbc.sqlserver.tds.ddr.v(Unknown Source) at macromedia.jdbc.sqlserver.tds.ddr.a(Unknown Source) at macromedia.jdbc.sqlserver.tds.ddr.a(Unknown Source) at macromedia.jdbc.sqlserver.ddj.m(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.e(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.a(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.v(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.r(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.execute(Unknown Source) at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:359) at coldfusion.sql.Executive.executeQuery(Executive.java:1442) at coldfusion.sql.Executive.executeQuery(Executive.java:1201) at coldfusion.sql.Executive.executeQuery(Executive.java:1131) at coldfusion.sql.SqlImpl.execute(SqlImpl.java:406) at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:1056) at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:685) at cfreports_multiple_search_action2ecfm1645272818.runPage(C:\inetpub\wwwroot\appraiseri\admin\reports\reports_multiple_search_action.cfm:99) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:244) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:444) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65) at coldfusion.filter.IpFilter.invoke(IpFilter.java:64) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:443) at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48) at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40) at coldfusion.filter.PathFilter.invoke(PathFilter.java:112) at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:30) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:94) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62) at coldfusion.CfmServlet.service(CfmServlet.java:204) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:928) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:414) at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:203) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:539) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:298) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:662) 
                                • 13. Re: Flexible Criteria Search?
                                  Carl Von Stetten Adobe Community Professional & MVP

                                  OK, I think I found it.  There are not enough opening parenthesis at the beginning of the FROM clause (you need one more).  However, you don't even need to use paraentheses to separate the different joins.  So, this:

                                  FROM  (((((main_orders mo

                                   

                                   

                                   

                                          LEFT JOIN lookup_status ls

                                          ON mo.order_status_ID = ls.order_status_ID)

                                   

                                          LEFT JOIN lookup_clients lc

                                          ON mo.order_client_ID = lc.client_ID)

                                   

                                   

                                          LEFT JOIN lookup_appraisers la

                                          ON mo.order_appraiser_ID = la.appraiser_ID)

                                   

                                          LEFT JOIN lookup_order_type lo

                                          ON mo.order_order_type_ID = lo.order_type_ID)

                                   

                                          LEFT JOIN lookup_property_type lt

                                          ON mo.order_property_type_ID = lt.property_type_ID)

                                   

                                          LEFT JOIN lookup_report_type lr

                                          ON mo.order_report_type_ID = lr.report_type_ID)

                                   

                                   

                                          LEFT JOIN lookup_rush_status lrs

                                          ON mo.order_rush_status_ID = lrs.rush_status_ID

                                   

                                  could instead be this:

                                   

                                  FROM  main_orders mo

                                          LEFT JOIN lookup_status ls ON mo.order_status_ID = ls.order_status_ID

                                          LEFT JOIN lookup_clients lc ON mo.order_client_ID = lc.client_ID

                                          LEFT JOIN lookup_appraisers la ON mo.order_appraiser_ID = la.appraiser_ID     

                                          LEFT JOIN lookup_order_type lo ON mo.order_order_type_ID = lo.order_type_ID

                                          LEFT JOIN lookup_property_type lt ON mo.order_property_type_ID = lt.property_type_ID

                                          LEFT JOIN lookup_report_type lr ON mo.order_report_type_ID = lr.report_type_ID

                                          LEFT JOIN lookup_rush_status lrs ON mo.order_rush_status_ID = lrs.rush_status_ID

                                   

                                  HTH,

                                  -Carl V.

                                  • 14. Re: Flexible Criteria Search?
                                    seasonedweb Level 1

                                    Carl:

                                     

                                    Thanks so much for your help (and patience).

                                     

                                    Run without a form search, the page now returns all records. But when using the form,

                                     

                                    Error Executing Database Query.

                                    [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ','.
                                    The error occurred in C:/inetpub/wwwroot/appraiseri/admin/reports/reports_multiple_search_action.cfm: line 99
                                    97 : <cfif StructKeyExists(Form, "order_rush_status_ID") AND Form.order_rush_status_ID NEQ 0> 98 : 99 : AND order_rush_status_ID = #Form.order_rush_status_ID#</cfif> 100 : 101 : ORDER BY order_create_date DESC 

                                    VENDORERRORCODE  102
                                    SQLSTATE  HY000
                                    SQL   SELECT mo.order_ID, mo.order_number, mo.order_property_type_ID, mo.order_order_type_ID, mo.order_report_type_ID, mo.order_client_ID, mo.order_appraiser_ID, mo.order_status_ID, mo.order_rush_status_ID, mo.order_property_street, mo.order_property_city, mo.order_create_date, ls.order_status_ID, ls.order_status, lc.client_ID, lc.client_company, la.appraiser_ID, la.appraiser_fname, la.appraiser_lname, lo.order_type_ID, lo.order_type, lp.property_type_ID, lp.property_type, lr.report_type_ID, lr.report_type, lrs.rush_status_ID, lrs.rush_status FROM main_orders mo LEFT JOIN lookup_order_status ls ON mo.order_status_ID = ls.order_status_ID LEFT JOIN lookup_clients lc ON mo.order_client_ID = lc.client_ID LEFT JOIN lookup_appraisers la ON mo.order_appraiser_ID = la.appraiser_ID LEFT JOIN lookup_order_type lo ON mo.order_order_type_ID = lo.order_type_ID LEFT JOIN lookup_property_type lp ON mo.order_property_type_ID = lp.property_type_ID LEFT JOIN lookup_report_type lr ON mo.order_report_type_ID = lr.report_type_ID LEFT JOIN lookup_rush_status lrs ON mo.order_rush_status_ID = lrs.rush_status_ID WHERE 1 = 1 AND appraiser_ID = 0,0 AND mo.order_status_ID = 10 ORDER BY order_create_date DESC
                                    DATASOURCE  appraiseriSQL
                                    Resources:
                                    Browser  Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.1; Trident/6.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; .NET4.0C; .NET4.0E; InfoPath.2)
                                    Remote Address  127.0.0.1
                                    Referrer  http://127.0.0.1/appraiseri/admin/reports/reports_multiple_search.cfm
                                    Date/Time  25-Nov-13 08:03 PM
                                    Stack Trace (click to expand)
                                    at cfreports_multiple_search_action2ecfm1645272818.runPage(C:/inetpub/wwwroot/appraiseri/adm in/reports/reports_multiple_search_action.cfm:99) at cfreports_multiple_search_action2ecfm1645272818.runPage(C:/inetpub/wwwroot/appraiseri/adm in/reports/reports_multiple_search_action.cfm:99)

                                    java.sql.SQLException: [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near ','. at macromedia.jdbc.sqlserverbase.ddca.b(Unknown Source) at macromedia.jdbc.sqlserverbase.ddca.a(Unknown Source) at macromedia.jdbc.sqlserverbase.ddb9.b(Unknown Source) at macromedia.jdbc.sqlserverbase.ddb9.a(Unknown Source) at macromedia.jdbc.sqlserver.tds.ddr.v(Unknown Source) at macromedia.jdbc.sqlserver.tds.ddr.a(Unknown Source) at macromedia.jdbc.sqlserver.tds.ddr.a(Unknown Source) at macromedia.jdbc.sqlserver.ddj.m(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.e(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.a(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.v(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.r(Unknown Source) at macromedia.jdbc.sqlserverbase.ddel.execute(Unknown Source) at coldfusion.server.j2ee.sql.JRunStatement.execute(JRunStatement.java:359) at coldfusion.sql.Executive.executeQuery(Executive.java:1442) at coldfusion.sql.Executive.executeQuery(Executive.java:1201) at coldfusion.sql.Executive.executeQuery(Executive.java:1131) at coldfusion.sql.SqlImpl.execute(SqlImpl.java:406) at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:1056) at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:685) at cfreports_multiple_search_action2ecfm1645272818.runPage(C:\inetpub\wwwroot\appraiseri\admin\reports\reports_multiple_search_action.cfm:99) at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:244) at coldfusion.tagext.lang.IncludeTag.doStartTag(IncludeTag.java:444) at coldfusion.filter.CfincludeFilter.invoke(CfincludeFilter.java:65) at coldfusion.filter.IpFilter.invoke(IpFilter.java:64) at coldfusion.filter.ApplicationFilter.invoke(ApplicationFilter.java:443) at coldfusion.filter.RequestMonitorFilter.invoke(RequestMonitorFilter.java:48) at coldfusion.filter.MonitoringFilter.invoke(MonitoringFilter.java:40) at coldfusion.filter.PathFilter.invoke(PathFilter.java:112) at coldfusion.filter.LicenseFilter.invoke(LicenseFilter.java:30) at coldfusion.filter.ExceptionFilter.invoke(ExceptionFilter.java:94) at coldfusion.filter.ClientScopePersistenceFilter.invoke(ClientScopePersistenceFilter.java:28) at coldfusion.filter.BrowserFilter.invoke(BrowserFilter.java:38) at coldfusion.filter.NoCacheFilter.invoke(NoCacheFilter.java:46) at coldfusion.filter.GlobalsFilter.invoke(GlobalsFilter.java:38) at coldfusion.filter.DatasourceFilter.invoke(DatasourceFilter.java:22) at coldfusion.filter.CachingFilter.invoke(CachingFilter.java:62) at coldfusion.filter.RequestThrottleFilter.invoke(RequestThrottleFilter.java:151) at coldfusion.CfmServlet.service(CfmServlet.java:204) at coldfusion.bootstrap.BootstrapServlet.service(BootstrapServlet.java:89) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at coldfusion.monitor.event.MonitoringServletFilter.doFilter(MonitoringServletFilter.java:42) at coldfusion.bootstrap.BootstrapFilter.doFilter(BootstrapFilter.java:46) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:928) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:414) at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:203) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:539) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:298) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:662) 
                                    • 15. Re: Flexible Criteria Search?
                                      Carl Von Stetten Adobe Community Professional & MVP

                                      @seasonedweb,

                                       

                                      The problem in the compiled SQL is this bit: AND appraiser_ID = 0,0.  It is the comma that is causing the error.

                                       

                                      What is being passed in the Form.order_appraiser_ID variable?  If the intended value is "0,0" (which seems like a strange ID), then it needs to be wrapped in single quotes because it's a string.  If that's the case, I'll repeat again that you should be using <cfqueryparam>, as that would have resolved this issue because it would have wrapped the value in single quotes automatically (if you had used it and set the cfsqltype attribute to "CF_SQL_VARCHAR").

                                       

                                      -Carl V.

                                      • 16. Re: Flexible Criteria Search?
                                        seasonedweb Level 1

                                        Carl:

                                         

                                        I've always give a default "0" value to the drop down select box if they don't select it:

                                         

                                        <cfquery name="get_all_appraiser" datasource="#Request.BaseDSN#">

                                        SELECT appraiser_ID, appraiser_fname, appraiser_lname, appraiser_pw

                                        FROM lookup_appraisers

                                        WHERE appraiser_pw IS NOT NULL

                                        ORDER BY appraiser_lname

                                        </cfquery>

                                         

                                        <select name="order_appraiser_ID" class="forminputfield">

                                         

                                        <option value="0">-- Select an Appraiser --</option>

                                         

                                         

                                        <cfoutput query="get_all_appraiser"><option value="#appraiser_ID#">#appraiser_lname#, #appraiser_fname#</option></cfoutput></select>

                                         

                                        Is there a better way to do this?

                                         

                                        Thanks

                                         

                                        Norman

                                        • 17. Re: Flexible Criteria Search?
                                          Carl Von Stetten Adobe Community Professional & MVP

                                          Norman,

                                           

                                          Can you dump the contents of the form scope before your query runs, and paste the results?  There appears to be something wrong with the value of form.appraiser_ID. 

                                           

                                          Also, is it possible you accidentally have two controls on the form with the name "order_appraiser_ID" (maybe as a result of a copy/paste operation)?  If you have two form controls with the same name, ColdFusion will merge them into a single form variable and pass in the combined values as a list.

                                           

                                          -Carl V.

                                          • 18. Re: Flexible Criteria Search?
                                            seasonedweb Level 1

                                            Running the form with no selections:

                                             

                                            StartDate:    [empty string]

                                            EndDate:    [empty string]

                                            appraiser_ID:    0

                                            report_type_ID:    0

                                            type_ID:    0

                                            status_ID:    0

                                            client_ID:    0

                                            property_type_ID:    0

                                            rush_status_ID:    0

                                             

                                            With all fields chosen:

                                             

                                            StartDate:    10/01/2013

                                            EndDate:    11/26/2013

                                            appraiser_ID:    5

                                            report_type_ID:    28

                                            type_ID:    1

                                            status_ID:    10

                                            client_ID:    4

                                            property_type_ID:    3

                                            rush_status_ID:    1

                                             

                                            Making 2 selections works, but choosing three (appraiser, date range, status, e.g.) shows 0 trecords found- incorrect.

                                             

                                            There was a double appraiser_ID entry- that's fixed.

                                            • 19. Re: Flexible Criteria Search?
                                              Carl Von Stetten Adobe Community Professional & MVP

                                              Norman,


                                              I suggest copying the SQL query over to SQL Server Management Studio and testing your query there.  Try it with all the combinations of inputs that you can think of hardcoded into the query, and see if you get the results you are expecting.  This will help to identify whether there is a ColdFusion code logic issue or just problems with the way the query is being compiled.

                                               

                                              Also, you might want to start a new thread if you still have issues with the query results, as the problem identified in the original post to this thread have now been resolved, and this thread is getting rather long.

                                               

                                              -Carl V.