EasyCFM.COM ColdFusion Forums / MS Access Related Issues / Invalid procedure call

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: Invalid procedure call

book mark this topic Printer-friendly Version  send this discussion to a friend  new posts last

Lossed
01-21-2008 @ 9:45 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

<cfquery name="q" datasource="bogusDB">
     SELECT
          e.id
     FROM
          (Events as e INNER JOIN Races as r on e.id = r.event_ID)
          INNER JOIN Codes as c on c.EventType = r.EventType
     WHERE dateDiff('year',now(),e.Date) BETWEEN -1 AND 1
</cfquery>

produces:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Invalid procedure call



A quick google found this:

http://support.microsoft.com/kb/172347

I tried adding the DSN and DATABASE to the connection string in CF admin but no joy.

Any Ideas?


Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------

louissto56
01-21-2008 @ 9:50 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

Mate. its access not you. I bet if you put that in MySQL it would work fine. The only thing I can see is the parenthasisiraeif- the brackets (()).

SELECT
          e.id
FROM
   Events as e
   INNER JOIN Races as r on e.id = r.event_ID
   INNER JOIN Codes as c on c.EventType = r.EventType

WHERE dateDiff('year',now(),e.Date) BETWEEN -1 AND 1

Dont know if access can handle 2 joins. I tried doing it for the forum tutorial but no luck. I dont no wat the problem is. Its crappy access

*kicks a box that says access*

Louis

____________________________
My Biz List.com.au :: Feb 08

louissto56
01-21-2008 @ 9:52 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

Oh. I dont think you have to alias tables using AS. Try just having a space from the table and the alias:

SELECT
          e.id
FROM
   Events e
   INNER JOIN Races r ON e.id = r.event_ID
   INNER JOIN Codes c ON c.EventType = r.EventType

WHERE DATEDIFF('year',NOW(),e.Date) BETWEEN -1 AND 1

Also, I capitalise SQL functions so I can distinguish it from CF functions. It makes it easier to read

Louis

____________________________
My Biz List.com.au :: Feb 08

Lossed
01-21-2008 @ 10:08 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

If I remove the dateDiff() from the where clause, e.g.:
where 5 between -1 and 1

it executes.

What's that all about?

Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------

This message was edited by Lossed on 1-21-08 @ 10:14 PM

louissto56
01-22-2008 @ 12:32 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1149
Joined: Jan 2007

That is weird. Maybe access doesnt know what a negative number is Question

____________________________
My Biz List.com.au :: Feb 08

emcgregor
01-22-2008 @ 2:05 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
New Member
Posts: 7
Joined: Nov 2007

I think it could be your interval.  Try 'yyyy' instead of 'year'.

Lossed
01-22-2008 @ 3:09 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 1095
Joined: Apr 2004

Thanks emcgregor,

That did the trick.

I finally found a quick, easy site showing access functions. In case anyone is interested:

http://www.techonthenet.com/access/functions/index.php

Lossed
---------------------------
When the only tool you have is a hammer, everything looks like a nail Smile
-----------------------------


Website Designed and Developed by Pablo Varando.