Error with CFSTOREDPROC and MSACCESS??

Question

I am having a problem with running a storedproc from access. I am using a storedproc because I am selecting based upon date (a cfvariable) and access required dates to be surrounded by #'s. (Is there another way to do this?) --

thanks for any help in advance -

here is what i have in my source..

<cfstoredproc datasource="#DSN#" procedure="qryGetDailyEvents">
<cfprocresult name="qryGetDailyEvents">

<cfprocparam cfsqltype="cf_sql_date" value="#DateFormat(currentDate, "m/d/yyyy")#">
</cfstoredproc>

here is the query i am using in access..
SELECT e.id, e.team AS teamid, t.team, e.eventtime, e.title, e.link, e.notes
FROM tblEvents AS e, tblTeam AS t
WHERE e.team=t.id And e.eventdate=[@today];

and finally
here is the error i am getting
----------------------
Error Occurred While Processing Request
Error Executing Database Query.
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

The error occurred in D:\Hosting\shencrew\NewWebPage\calendar\cal.cfm: line 80
Called from D:\Hosting\shencrew\NewWebPage\calendar.cfm: line 73
Called from D:\Hosting\shencrew\NewWebPage\calendar\cal.cfm: line 80
Called from D:\Hosting\shencrew\NewWebPage\calendar.cfm: line 73

78 : <cfprocparam
79 : cfsqltype="cf_sql_date"
80 : value="#DateFormat(currentDate, "m/d/yyyy")#">
81 : </cfstoredproc>
82 :

--------------------------------------------------------------------------------

SQL {call qryGetDailyEvents( (param 1) )}
DATASOURCE shencrew_accesscf_crew
VENDORERRORCODE 3092
SQLSTATE

Resources:
Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.

Browser Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 2.0.50727; .NET CLR 1.1.4322; .NET CLR 3.0.04506.30)
Remote Address 72.224.77.202
Referrer
Date/Time 11-Apr-07 08:22 AM

Stack Trace (click to expand)
----------------------

paross1 · Accepted Answer

How about just using a cfquery, something like this?

SELECT e.id, e.team AS teamid, t.team, e.eventtime, e.title, e.link, e.notes
FROM tblEvents AS e, tblTeam AS t
WHERE e.team=t.id
And e.eventdate=#CreateODBCDateTime(Now())#

As for the stored procedure, are you declaring the date paramer as a PARAMETERS statement in your access query, such as in the following?

PARAMETERS in_today Date/Time;
SELECT e.id, e.team AS teamid, t.team, e.eventtime, e.title, e.link, e.notes
FROM tblEvents AS e, tblTeam AS t
WHERE e.team = t.id
AND e.eventdate = in_today;

Phil

stephenmbell · Answer

I know that MSACCESS does not support stored procedures however in CFMX (atleast 6.1, i know of) you can run an MSACCESS query as a cfstoredproc.

I ended up using a cfquery with CreateODBCDateTime() and it worked fine.
Thank you for the input.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.