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
Copy link to clipboard
Copied
There are a few problems I see with your query:
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.
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.
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.
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 : |
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.
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 | |||||||||
| |||||||||
Resources:
| |||||||||
|
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) |
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.
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:
| |||||||||
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:
| |||||||||
|
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) |
Copy link to clipboard
Copied
Oops. Sorry about that. Should be "IS NOT" instead of "NOT IS".
-Carl V.
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 : |
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.
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 | |||||||||
| |||||||||
Resources:
| |||||||||
|
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) |
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.
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 | |||||||||
| |||||||||
Resources:
| |||||||||
|
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) |
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.
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
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.
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.
Copy link to clipboard
Copied
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.