Skip to main content
S
stephenmbell
Inspiring
April 11, 2007
Answered

Error with CFSTOREDPROC and MSACCESS??

  • April 11, 2007
  • 3 replies
  • 600 views
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)
----------------------
    This topic has been closed for replies.
    Correct answer paross1
    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

    3 replies

    S
    stephenmbellAuthor
    Inspiring
    April 11, 2007
    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.
    P
    paross1
    Participating Frequently
    April 11, 2007
    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
    S
    Sojovi
    Inspiring
    April 11, 2007
    Access does not support Stored Procedures. SP are supported by SQL engines like MS SQL,MySQL5, Oracle, etc....

    Regards
    P
    paross1Correct answer
    Participating Frequently
    April 11, 2007
    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
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices