INNER JOIN QN

[lordfa9]

I was just wondering if anybody else has a problem with using inner join in ASP cause whenever i use an inner JOin command in my SQL query it always dosn't work For example:

<%set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"conn.Open "c:/webdata/northwind.mdb"set rs=Server.CreateObject("ADODB.recordset")sql="SELECT Employees.NameFROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID"rs.Open sql, conn%>

is it my syntax?

[aspnetguy]

it may be Access not your code. Try running the same query in SQL mode in Access

[justsomeguy]

Joins and everything else in SQL don't have anything to do with ASP or PHP or whatever language you are using to send the commands to the database.I guarantee the join is doing exactly what a join does, the question is, is the join what you need to use to do what you want to do? It looks like you are trying to get employees that have placed an order. If Access supports subqueries, this would probably be best:SELECT Name FROM Employees WHERE Employee_ID IN (SELECT Employee_ID FROM Orders)Or, if it doesn't:SELECT E.Name FROM Employees AS E, Orders AS O WHERE E.Employee_ID = O.Employee_ID

[pulpfiction]

What is the error that you are getting?? post it, might be helpful

[lordfa9]

it may be Access not your code. Try running the same query in SQL mode in Access

i'm quite sure it's not access because i ran the query there before using in in my script.As for error msg, i'm not sure cause i misplaced the file but i'll recreate it n get back to you, but i think it's something to do with being unable to excecute the query.

[lordfa9]

recreated my file, seems to work now :)might be hardware or software though, i am now using a new platform.By the way, just a general query, when coding or coding general in notepad (Platform migration: Dreamweaver----> Notepad not good ). If i do the following to a piece of code will there be any difference?

sql="SELECT Subcode, DateGiven, DateDue, Information FROM " & tblname & " Order BY " & sort & " " & ordering & ""

typed in one complete line (notepad expands horizontally forever)or If i press enter to shift the various parts code to a new line eg.

sql="SELECT Subcode, DateGiven, DateDue, Information FROM " & tblname & " Order BY " & sort & " " & ordering & ""

will this affect the code? because i recall doing that before and it seems to cause a lot of funny errors, i didn't do it now and it seems alright

[aspnetguy]

doesn't matter if you write it all in one line or not.EDIT: just some guy is right you need to use & _ to tell your code it contuinues ont he next line....didn't see that part of the question when I posted.

[justsomeguy]

I think with VB you have to use an underscore at the end of a line to tell it that the line continues on the next line.

[lordfa9]

doesn't matter if you write it all in one line or not.EDIT: just some guy is right you need to use & _ to tell your code it contuinues ont he next line....didn't see that part of the question when I posted.

so you mean the code has to be like this?

sql="SELECT Subcode, DateGiven, DateDue, Information & _FROM " & tblname & " & _Order BY " & sort & " " & ordering & "" & _

[aspnetguy]

not quite, like this

sql="SELECT Subcode, DateGiven, DateDue, Information " & _"FROM " & tblname & _" Order BY " & sort & ", " & ordering