btintermedia
asked on
CFML cfqueryparam & MSSQL error
I have the following query:
<cfquery datasource="DSN_Vineyard" name="calendar_events">
SELECT TOP <cfqueryparam cfsqltype="cf_sql_integer" value="#select_no#"> *
FROM calendar
WHERE church_id = <cfqueryparam cfsqltype="cf_sql_integer" value="#church_id#">
AND (
(calendar_startdate >= <cfqueryparam cfsqltype="CF_SQL_TIMESTAM P" value="#dateformat(now(), 'mm/dd/yyyy')#">
AND calendar_enddate <= <cfqueryparam cfsqltype="CF_SQL_TIMESTAM P" value="#dateformat(dateadd ('ww', 2, now()), 'mm/dd/yyyy')#">)
or
(calendar_startdate >= <cfqueryparam cfsqltype="CF_SQL_TIMESTAM P" value="#dateformat(now(), 'mm/dd/yyyy')#"> AND calendar_enddate is null)
or
(calendar_startdate >= <cfqueryparam cfsqltype="CF_SQL_TIMESTAM P" value="#dateformat(now(), 'mm/dd/yyyy')#"> AND priority = 'Y')
)
ORDER BY calendar_startdate asc
</cfquery>
It is throwing the following error:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 1: Incorrect syntax near '@P1'.
The error occurred in E:\CustomerData\webspaces\ webspace_0 0104265\ww wroot\modu les\CalHil ites.cfm: line 26
24 : (calendar_startdate >= <cfqueryparam cfsqltype="CF_SQL_TIMESTAM P" value="#dateformat(now(), 'mm/dd/yyyy')#"> AND calendar_enddate is null)
25 : or
26 : (calendar_startdate >= <cfqueryparam cfsqltype="CF_SQL_TIMESTAM P" value="#dateformat(now(), 'mm/dd/yyyy')#"> AND priority = 'Y')
27 :
28 : ORDER BY calendar_startdate asc
SQL SELECT TOP (param 1) * FROM calendar WHERE church_id = (param 2) AND (calendar_startdate >= (param 3) AND calendar_enddate <= (param 4) ) or (calendar_startdate >= (param 5) AND calendar_enddate is null) or (calendar_startdate >= (param 6) AND priority = 'Y') ORDER BY calendar_startdate asc
DATASOURCE DSN_Vineyard
VENDORERRORCODE 170
SQLSTATE HY000
Resources:
I think that it is related to the cfqueryparam cfsqltype="CF_SQL_TIMESTAM P but I am not sure
<cfquery datasource="DSN_Vineyard" name="calendar_events">
SELECT TOP <cfqueryparam cfsqltype="cf_sql_integer"
FROM calendar
WHERE church_id = <cfqueryparam cfsqltype="cf_sql_integer"
AND (
(calendar_startdate >= <cfqueryparam cfsqltype="CF_SQL_TIMESTAM
AND calendar_enddate <= <cfqueryparam cfsqltype="CF_SQL_TIMESTAM
or
(calendar_startdate >= <cfqueryparam cfsqltype="CF_SQL_TIMESTAM
or
(calendar_startdate >= <cfqueryparam cfsqltype="CF_SQL_TIMESTAM
)
ORDER BY calendar_startdate asc
</cfquery>
It is throwing the following error:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Line 1: Incorrect syntax near '@P1'.
The error occurred in E:\CustomerData\webspaces\
24 : (calendar_startdate >= <cfqueryparam cfsqltype="CF_SQL_TIMESTAM
25 : or
26 : (calendar_startdate >= <cfqueryparam cfsqltype="CF_SQL_TIMESTAM
27 :
28 : ORDER BY calendar_startdate asc
SQL SELECT TOP (param 1) * FROM calendar WHERE church_id = (param 2) AND (calendar_startdate >= (param 3) AND calendar_enddate <= (param 4) ) or (calendar_startdate >= (param 5) AND calendar_enddate is null) or (calendar_startdate >= (param 6) AND priority = 'Y') ORDER BY calendar_startdate asc
DATASOURCE DSN_Vineyard
VENDORERRORCODE 170
SQLSTATE HY000
Resources:
I think that it is related to the cfqueryparam cfsqltype="CF_SQL_TIMESTAM
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The full solution is that cfqueryparams are not allowed for select top queries. (or sorts bys)
for date format, try 'dd mmm yyyy'