So, I would first like to thank everyone for always being able to push me in the right direction, whenever I'm stuck...that being said, I do have a problem.

I have a web page where a user can select a commodity code, and based on the selection a buyer's contact info should popup.

The db contains three tables 1) buyerTable, 2) linkToBuyerTable, 3) commodityCodeList.

The error message that I get is:

Microsoft JET Database Engine (0x80040E14)
Syntax error in JOIN operation.
/buyerListing/resultsPage.asp, line 76

Any help is much appreciated...

Terry


<%
    Dim commodity, staff

    commodity = Request.Form("commodity")
    staff = Request.Form("staff")

%>

<%
    Dim objCommand, objCommand2, selectedGroup, objRS

    Set objCommand = Server.CreateObject("ADODB.Command")

    objCommand.ActiveConnection = strConnect

    objCommand.CommandText = "SELECT buyerTable.buyerName, buyerTable.phone, buyerTable.email, buyerTable.title " & _
        "FROM buyerTable INNER JOIN " & _
        "(linkToBuyerTable INNER JOIN " & _
        "commodityCodeList " & _
        "ON (linkToBuyerTable.categoryId = commodityCodeList.categoryId)) " & _
        "ON (buyerTable.buyerInitials = linkToCategory.buyerInitials) " & _
        "WHERE commodityCodeList.categoryId = commodity"

    objCommand.CommandType = adCmdText

    objCommand.Parameters("commodity") = commodity

    Set objRS = objCommand.Execute

    While Not objRS.EOF

        Response.Write objRS("buyerName") & "<br>"
        Response.Write objRS("title") & "<br>"
        Response.Write objRS("phone") & "<br>"
        Response.Write objRS("<a href='mailto:'email'>'email'") & "<br>"

        objRS.MoveNext
    Wend

>ON (buyerTable.buyerInitials = linkToCategory.buyerInitials)
you specify linkToCategory instead of linkToBuyerTable

>WHERE commodityCodeList.categoryId = commodity
>objCommand.Parameters("commodity") = commodity
you cannot add parameter that way

if you want to use parameter, try (assume commodity is an integer):

"SELECT buyerTable.buyerName, buyerTable.phone, " & _
"buyerTable.email, buyerTable.title FROM buyerTable " & _
"INNER JOIN linkToBuyerTable ON " & _
"buyerTable.buyerInitials = linkToBuyerTable.buyerInitials " & _
"INNER JOIN commodityCodeList ON "
"ON linkToBuyerTable.categoryId = commodityCodeList.categoryId " & _
"WHERE commodityCodeList.categoryId = ?"

objCommand.Parameters.Append objCommand.CreateParameter("commodity", adInteger, , , commodity)"


or without append any paramter to Command object:

"SELECT buyerTable.buyerName, buyerTable.phone, " & _
"buyerTable.email, buyerTable.title FROM buyerTable " & _
"INNER JOIN linkToBuyerTable ON " & _
"buyerTable.buyerInitials = linkToBuyerTable.buyerInitials " & _
"INNER JOIN commodityCodeList ON "
"ON linkToBuyerTable.categoryId = commodityCodeList.categoryId " & _
"WHERE commodityCodeList.categoryId = " & commodity

Regards,
Khoa Nguyen

Thanks Khoa...it was a stupid typo.

t~