Solved

CFML cfqueryparam & MSSQL error

Posted on 2010-08-13
3
832 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

628 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