Open Connection Error with a SELECT INNERJOIN 1

[StarScream]

Syntax Error In FROM Clause

I am trying to get this ASP to output info from a specific record. I input the name of the system "Microsoft" which correlates to a single record which has 4 people on it. Each person does a different job. The person I want is SysPersID. But this would obviously just return a number. So I want to INNER JOIN to the PersonnelTable and look up that ID to return the PersonnelName. Heres the code:

<%
    Set DataConn = Server.CreateObject(&quot;ADODB.Connection&quot;)
    DataConn.Open &quot;Driver=Microsoft Access Driver (*.mdb);DBQ=&quot; & Server.MapPath(&quot;Database.mdb&quot;)

    Set cmdTemp = Server.CreateObject(&quot;ADODB.Command&quot;)
    Set rstTable = Server.CreateObject(&quot;ADODB.Recordset&quot;)

    cmdTemp.CommandText = &quot;SELECT PersonnelName FROM PersonnelTable INNERJOIN SystemTable ON PersonnelID=SysPersID WHERE SystemName='Microsoft'&quot;
    cmdTemp.CommandType = 1
    Set cmdTemp.ActiveConnection = DataConn

rstTable.Open cmdTemp, , 1, 3

    Response.write "Person=" & Server.URLEncode(rstTable("PersonnelName"))

    rstTable.Close
    Set rstTable = Nothing
    DataConn.Close
    Set DataConn = Nothing
%>

It says I have a syntax error in the FROM clause (I bolded the line). Any ideas what I'm doing to get the syntax error ? Thanks.

 

[krisbrixon]

First thing I would try is to run the query in query analyzer or what ever access has to do that. That way you know if the problem is with the SQL Query or ASP code.

PS INNERJOIN is two words INNER JOIN

http://jbgames.com/code/sql/InnerJoin.htm

Kris

 

[StarScream]

Oops!

I made INNER JOIN two words, then it says "Join Expression Not Supported"

I'm missing something obvious, ain't I?

PJP

 

[Simpleton]

yep your connecting to an MS access database which doesn't support ansi joins

you'll need

FROM PersonnelTable, SystemTable
WHERE SystemName='Microsoft' and PersonnelID=SysPersID

Shoul do it hopefully

 

[StarScream]

Thanks Simple, it was...er...simple ;-) once you pointed out the reason. Thanks. You get a star!

PJP