EasyCFM.COM ColdFusion Forums / MS Access Related Issues / syntax errors

   Reply to Discussion | New Discussion << previous || next >> 
Posted By Discussion Topic: syntax errors

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

grabit
03-04-2007 @ 8:50 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 497
Joined: Jun 2003

HELP Please
This is driving me to distraction. Can someone please help with this error and tell me where i have gone wrong please.
---------------
Code and error
-------------==
Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'topic = first band fit blast class today AND posttype = 2 AND members.ID = 8'.  
  
The error occurred in C:\CFusionMX\wwwroot\Forum\viewMessages.cfm: line 39

37 : SELECT thread.ID, topic, postdate, memberID, threads.username, posttype, posttext, name, status, datejoined, members.username, members.ID
38 : FROM threads, members
39 : WHERE topic = #URL.topic# AND posttype = 2 AND members.ID = #url.memberID#
40 : </cfquery>

Thanks in advance
Grabit


kicker
03-04-2007 @ 9:01 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 717
Joined: Oct 2004

try putting single quotes around the topic value like so:

WHERE topic = '#URL.topic#' AND posttype = 2 AND members.ID = #url.memberID#



Craig
Clearcg.com

grabit
03-04-2007 @ 9:11 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 497
Joined: Jun 2003

tried that and when i do i get
Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.  
  
The error occurred in C:\CFusionMX\wwwroot\Forum\viewMessages.cfm: line 39

37 : SELECT thread.ID, topic, postdate, memberID, threads.username, posttype, posttext, name, status, datejoined, members.username, members.ID
38 : FROM threads, members
39 : WHERE topic = '#URL.topic#' AND posttype = 2 AND members.ID = #url.memberID#
40 : </cfquery>
41 :



Thanks to you all

CJ
03-05-2007 @ 12:50 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4262
Joined: Oct 2002

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

-CJ-
@ #coldfusion/DALNet
http://charlie.griefer.com

Teachers open the door. You enter by yourself.
—Chinese Proverb

grabit
03-05-2007 @ 1:24 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 497
Joined: Jun 2003

Thanks Charlie
I had missed the reserved word "name" so i changed that to memname.
However i still get that original error message about wrong syntax
Any other clues please
cheers
Grabit

Thanks to you all

mquack
03-05-2007 @ 4:10 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1544
Joined: Jan 2005

It appears that your topic is a text type, rather than a numeric type.  You need single quotes around it.


www.rachelqueensg.com
Amazon Wish List

mquack
03-05-2007 @ 4:11 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Moderator
Posts: 1544
Joined: Jan 2005

Ooooops... never mind.  I see you already did that.  Guess I should've read more closely the first time.

Are the datatypes correct on the other columns?

www.rachelqueensg.com
Amazon Wish List

CJ
03-05-2007 @ 11:50 AM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4262
Joined: Oct 2002

post the following:

1) your current <cfquery> code
2) the values for #URL.topic# and #URL.memberID#
3) the datatypes for topic and members.ID

if URL.topic contains a single quote, it'll hose your SQL the way you're currently set up.  i'd highly suggest using <cfqueryparam> to avoid that... even if it isn't the problem here, it could show up later.

-CJ-
@ #coldfusion/DALNet
http://charlie.griefer.com

Teachers open the door. You enter by yourself.
—Chinese Proverb

grabit
03-05-2007 @ 12:33 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Senior Member
Posts: 497
Joined: Jun 2003

Thanks Charlie info as follows
query
---------
<cfquery name="showposts" datasource="#dsn#">
SELECT threads.ID, topic, postdate, memberID, threads.username, posttype, posttext, memname, status, datejoined, members.ID as memID
FROM threads, members
WHERE posttype=2 AND topic='#URL.topic#' AND memID=#url.memberID#
</cfquery>

the previous pages link to pass url's
--------------------------------------
viewMessages.cfm?topic=#showtopics.topic#&memberID=#showtopics.memberID#

values as passed (from the error message screen)
-------------------------------------------------
SELECT threads.ID, topic, postdate, memberID, threads.username, posttype, posttext, memname, status, datejoined, members.ID as memID FROM threads, members WHERE posttype=2 AND topic='first band fit blast class today' AND memID=8

table is formatted as follows
posttype - integer with no decimal places
memberID - long integar with no decimal places
topic  - text with a character limit of 100

Thanks to you all

CJ
03-05-2007 @ 2:42 PM
Reply
Edit
Profile
Send P.M.
My Gravatar!
Powered by Gravatar
Administrator
Posts: 4262
Joined: Oct 2002

nothing's jumping out at me.

what's the exact error message that you're getting now?

can you copy/paste that SQL (in the generated error message) into Access and run it?  what happens?

-CJ-
@ #coldfusion/DALNet
http://charlie.griefer.com

Teachers open the door. You enter by yourself.
—Chinese Proverb


Website Designed and Developed by Pablo Varando.