Solved

CFML cfqueryparam & MSSQL error

Posted on 2010-08-13
3
825 Views
Last Modified: 2012-05-10
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_TIMESTAMP" value="#dateformat(now(), 'mm/dd/yyyy')#">
        AND calendar_enddate <= <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#dateformat(dateadd('ww', 2, now()), 'mm/dd/yyyy')#">)
                  or
            (calendar_startdate >= <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#dateformat(now(), 'mm/dd/yyyy')#"> AND calendar_enddate is null)
                  or
            (calendar_startdate >= <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" 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_00104265\wwwroot\modules\CalHilites.cfm: line 26
24 :             (calendar_startdate >= <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" value="#dateformat(now(), 'mm/dd/yyyy')#"> AND calendar_enddate is null)
25 :                   or
26 :             (calendar_startdate >= <cfqueryparam cfsqltype="CF_SQL_TIMESTAMP" 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_TIMESTAMP but I am not sure
0
Comment
Question by:btintermedia
3 Comments
 
LVL 19

Accepted Solution

by:
erikTsomik earned 500 total points
ID: 33433542
I think that the problem is here
  SELECT TOP <cfqueryparam cfsqltype="cf_sql_integer" value="#select_no#"> *

try changing cfqueryparam and put the number just for testing
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33434956
for cfsqltype,  try CF_SQL_CHAR or CF_SQL_VARCHAR

for date format,  try 'dd mmm yyyy'
0
 

Author Closing Comment

by:btintermedia
ID: 33435326
The full solution is that cfqueryparams are not allowed for select top queries. (or sorts bys)
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question