Stumped By The Easiest Things 2

[StarScream]

Help,

For some reason I can't get this to work. I've written several completed DBs and am working on a new project. Had it all working fine until I tried to write an ASP page using this query. Any thoughts on what I'm doing wrong? Thanks.

SELECT PersonnelName FROM PersonnelTable INNERJOIN SystemTable ON PersonnelID=SysPersID WHERE SystemName='Microsoft'

PS - Here is the ASP that the query is in. It comes back with an error in the bolded line below.

<%
    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
%>

PJP

 

[RiverGuy]

Try making innerjoin two words.

 

[StarScream]

Hey RiverGuy,

Yeah I tried that when I realized it. But the error is still present: "Join expression not supported."

Makes me think the error is in the SELECT statement, and not the rest of the code.

Any thoughts?

PJP

 

[RiverGuy]

Have you tried just the word 'Join' without 'Inner'? It originally said that Join isn't supported?

If that is the case, why don't you try and execute that from the connection without attaching a recordset....see if it bugs out on that too.

 

[StarScream]

That's a great idea!

I'm gonna go check them both out. I'll let you know shortly!

PJP

 

[RiverGuy]

Thanks. I've usually thought Access was pretty liberal in syntax, but you can't ever be quitr sure.

 

[StarScream]

Okay, neither idea worked. Too be honest, right now I don't even know if its the SELECT statement thats wrong or the rest of the ASP script.

Anyone else have any suggestions?

PJP

 

[r937]

your sql is fine, assuming you write INNER JOIN and not INNERJOIN

rudy

 

[StarScream]

Thank Rudy for confirmation!

But I get a "syntax error in the FROM clause" or a "Join expression is not supported" error. Can't seem to shake it. I can't imagine that such a simple and common thing can cause such a big problem. Does anyone have an example that does something similar that can send me a link?

Thanks.
PJP

 

[r937]

wtf? sql/server, right? whoa, must be something else going on, because that join syntax is fine

try this equivalent

SELECT PersonnelName
FROM PersonnelTable,SystemTable
WHERE PersonnelID=SysPersID
AND SystemName='Microsoft'

rudy

 

[r937]

er, i meant, access, right?

that inner join syntax was valid in access 97

r