I'm trying to run this script to pull a product listing based on the relationship i have created

However i keep getting this error

Syntax error (missing operator) in query expression

I have no clue to what this means or how to fix it. Could someone please help. Thanks a head of time.

Function getParentProdChildDetailSQL(rs)

SQL = "SELECT tblProducts.prodID, tblProducts.prodName, tblProducts.prodImageSmallPath, tblProducts.prodDescription, tblProducts.prodLink, tblProducts.prodPrice, "_
&" tblProducts.prodSaleIsActive, tblProducts.prodSalePrice, tblParentProdDetail.ProdID, tblParentProdDetail.ProdName, "_
&" tblParentProdDetail.subParentProdProductId, tblSub_ParentProd.subParentProdProductId, tblParentProd.ParentprodID, "_
&" tblSub_ParentProd.subParentProdID, tblParentProdDetail.subParentProdProductId "_
'&" FROM tblSub_ParentProd INNER JOIN "_
&" tblProducts INNER JOIN "_
&" tblParentProdDetail ON tblProducts.prodID = tblParentProdDetail.ProdID ON "_
&" tblSub_ParentProd.subParentProdID = tblParentProdDetail.subParentProdProductId INNER JOIN "_
&" tblParentProd ON tblSub_ParentProd.subParentProdProductId = tblParentProd.ParentprodID "_'&" WHERE

Do While Not rs.EOF
SQL = SQL & " subParentProdProductId = " & rs.Fields("ParentProdID") & " OR "
rs.MoveNext
Loop

SQL = Mid(SQL, 1, len(SQL)-3)
getParentProdChildDetailSQL = SQL
Else
getParentProdChildDetailSQL = ""
End If
End Function

I don't see any place where you're executing any SQL that would generate that error. Find the line where you're executing your SQL string (the error will tell you the line number), and instead of executing the SQL command, response.write it. Look at the SQL and see if you notice anything syntactically incorrect. If you're not sure, post it here and maybe someone will notice something.

take your string and paste it into the db command line directly. Replace your variables with constants and see where the error is then.

Another cause is one or more form fields was empty. You should provide default values for all form fields that are used in the query.

ack, ray beat me to it. His way is also a most excellent way to see if one of your values is blank when you write it to screen.

Try to debug your code,

Just comment .Execute(SQL) line, and place a:

Repsonse.Write("<pre>" & SQL & "</pre>")

Line inside your code where you can easily read it.

it's right there, in plain sight

you cannot say

FROM x
INNER JOIN y
INNER JOIN z
ON something
ON something

change this to

FROM x
INNER JOIN y
ON something
INNER JOIN z
ON something


rudy
http://r937.com