• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Flexible Criteria Search?

Participant ,
Nov 17, 2013 Nov 17, 2013

Copy link to clipboard

Copied

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

TOPICS
Advanced techniques

Views

1.5K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Nov 18, 2013 Nov 18, 2013

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Nov 21, 2013 Nov 21, 2013

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Nov 22, 2013 Nov 22, 2013

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Nov 22, 2013 Nov 22, 2013

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Nov 22, 2013 Nov 22, 2013

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Nov 22, 2013 Nov 22, 2013

Copy link to clipboard

Copied

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/admin/reports/reports_multiple_search_action.cfm:39) at cfreports_multiple_search_action2ecfm1645272818.runPage(C:/inetpub/wwwroot/appraiseri/admin/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) 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Nov 22, 2013 Nov 22, 2013

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Nov 22, 2013 Nov 22, 2013

Copy link to clipboard

Copied

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) 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Nov 25, 2013 Nov 25, 2013

Copy link to clipboard

Copied

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

-Carl V.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Nov 25, 2013 Nov 25, 2013

Copy link to clipboard

Copied

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 :

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Nov 25, 2013 Nov 25, 2013

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Nov 25, 2013 Nov 25, 2013

Copy link to clipboard

Copied

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/admin/reports/reports_multiple_search_action.cfm:99) at cfreports_multiple_search_action2ecfm1645272818.runPage(C:/inetpub/wwwroot/appraiseri/admin/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) 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Nov 25, 2013 Nov 25, 2013

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Nov 25, 2013 Nov 25, 2013

Copy link to clipboard

Copied

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/admin/reports/reports_multiple_search_action.cfm:99) at cfreports_multiple_search_action2ecfm1645272818.runPage(C:/inetpub/wwwroot/appraiseri/admin/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) 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Nov 26, 2013 Nov 26, 2013

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Nov 26, 2013 Nov 26, 2013

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Nov 26, 2013 Nov 26, 2013

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
Nov 26, 2013 Nov 26, 2013

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Nov 27, 2013 Nov 27, 2013

Copy link to clipboard

Copied

LATEST

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation