Error with CFSTOREDPROC and MSACCESS??

[stephenmbell]

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")#" dbvarname="@today">
worked in development but not in production
--->
<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 solution]

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

[Sojovi]

Access does not support Stored Procedures. SP are supported by SQL engines like MS SQL,MySQL5, Oracle, etc....

Regards

[stephenmbell]

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.

[paross1]

Yes, that is correct, you can run a query stored in Access as a stored procedure, and even pass parameters provided you declare the PARAMETERS in the query.

Phil