Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

CFML cfqueryparam & MSSQL error

Posted on 2010-08-13
3
Medium Priority
?
835 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 1500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

688 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