ODBC Error Code = 37000 (Syntax error or access violation)

ok i get this error

Error Occurred While Processing Request
Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'shoppingcart.itemid = sizes.itemid Inner Join colors On shoppingcart.itemid = colors.itemid'.



The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (92:1) to (92:74).


Date/Time: 08/28/04 08:44:42
Browser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; .NET CLR 1.1.4322)
Remote Address: 212.39.108.22
Query String: pageid=shoppingcart&menu=1



when i run this query

<cfquery name="seleccart" datasource="#datasourcename#" blockfactor="100">
SELECT shoppingcart.itemid, shoppingcart.ID AS SCID, shoppingcart.quantity, shoppingcart.size, shoppingcart.color, colors.color, colors.ID AS CID, sizes.size, sizes.ID AS SID
FROM shoppingcart Inner Join sizes On shoppingcart.itemid = sizes.itemid
Inner Join colors On shoppingcart.itemid = colors.itemid
WHERE shoppingcart.scid = #session.scart#
</cfquery>



anyone have any idea what it can be???

Can you copy and execute the generated SQL (look in the debugging information) in the Access SQL editor? I assume it doesn't.

My guess is that it's your inner join syntax (try putting in parenthesis around the join statements, or use the alternate join syntax in the where clause instead of in the from clause), or that shoppingcart.scid is a character field and you may need quotes around the variable.

i fixed it

it works with this query

<cfquery name="seleccart" datasource="#datasourcename#" blockfactor="100">
SELECT shoppingcart.itemid, shoppingcart.ID AS SCID2, shoppingcart.quantity, shoppingcart.size, shoppingcart.color, colors.color AS ITEMCOLOR, colors.ID AS CID, sizes.size AS ITEMSIZE, sizes.ID AS SID
FROM ((shoppingcart Inner Join sizes On shoppingcart.itemid = sizes.itemid)
Inner Join colors On shoppingcart.itemid = colors.itemid)
WHERE shoppingcart.scid = #session.scart#
ORDER BY sizes.size, colors.color
</cfquery>