Solved

CFML cfqueryparam & MSSQL error

Posted on 2010-08-13
3
819 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now