ASP, SQL, JOINs ... OH MY! Question.

Greetings,

I'm was reading SQL Joins info at W3Schools

[ URL ]

Just basically tryin to figure out how to show information (via asp) from two DIFFERENT tables.

here, as an example, they use two tables

employees (table)
-----------------------------------------------
Employee_ID Name
-----------------------------------------------
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari

Order (table)
---------------------------------------------------
Prod_ID Product Employee_ID
234 Printer 01
657 Table 03
865 Chair 03

I wrote the following in my asp to test the sql string:

-----------------------------------------------------
SET oRSsc = Server.CreateObject( "ADODB.Recordset" )
oRSsc.OPEN strSQL = "SELECT employees.Name, orders.Product "
strSQL = strSQL & " FROM employees, orders "
strSQL = strSQL & " WHERE employees.employeeID = orders.employeeID;" "DSN=scDSN" (last line all on one line)

response.write strsql
----------------------------------------------------------------
then i promptly get the following error message:
----------------------------------------------------------------
Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/applications/subcontractors/default.asp, line 24, column 69
strSQL = strSQL & " WHERE employees.employeeID = orders.employeeID;" "DSN=scDSN"

--------------------------------------------------------------------------
Question: What da HECK am i doing wrong?

My training environment: access2k, winXP, IIS
My reading: Beginning asp dbases (wrox) no help.

Thanks in Advance,

Okay - there's a few things wrong here.

Firstly - the database. By the looks of it, it hasn't been normalised (look it up ).
For the task you are attempting, you will need three tables.
i) Employees
ii) Products
iii) Orders

The order table will consist of
OrderID | ProductID | EmployeeID | Qty
(you can make the OrderID & ProductID the Primary Key as there should never be a duplicate (note the qty field))

--------------
Now that that's outta the way - your SQL syntax.
The best way to suss out syntax issue is to do a response.write on the string first.
So, move your response.write strsql ABOVE the DB call - and you should see whats going on.

Also - what's this: "DSN=scDSN" - all DSN stuff should be done BEFORE connecting to the DB. And, remove the quotes - that might be your problem.

HTH - stumpy