Join 2 Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Join 2 Tables

Author

Topic

siausi82
Starting Member

2 Posts

Posted - 2004-11-14 : 21:06:57

Hi

I'm really new at SQL. I would like to do a simple query with SQL taking data from two tables, and display it using ASP.

The tables are:
Table Name: Employee
Columns: EmployeeID, FirstName, Surname

Table Name; Position
Columns: EmployeeID, Position

Both tables uses EmployeeID as their primary key.

The query I've done is:

"SELECT Employee.EmployeeID, Employee.FirstName, Position.Position" &_
"FROM Employee, Position" &_
"WHERE Employee.EmployeeID = Position.EmployeeID;"

I've also attached entire code below.

Would really appreciate your help! Thank you!!

<html><head><title>Join 2 Tables</title></head><body>
<%
'Dimension variables
Dim adoCon 'Holds the Database Connection Object
Dim rsGuestbook 'Holds the recordset for the records in the database
Dim strSQL 'Holds the SQL query for the database

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("TheDB.mdb")

'Set an active connection to the Connection object using DSN connection
adoCon.Open "DSN=TheDB"

'Create an ADO recordset object
Set rsGuestbook = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = &_
"SELECT Employee.EmployeeID, Employee.FirstName, Position.Position" &_
"FROM Employee, Position" &_
"WHERE Employee.EmployeeID = Position.EmployeeID;"

'Open the recordset with the SQL query
rsGuestbook.Open strSQL, adoCon

'Loop through the recordset
Do While not rsGuestbook.EOF

'Write the HTML to display the current record in the recordset
Response.Write ("<br>")
Response.Write (rsGuestbook("EmployeeID"))
Response.Write("<br>")
Response.Write(rsGuestbook("FirstName"))
Response.Write("<br>")
Response.Write(rsGuestbook("Position"))

'Move to the next record in the recordset.
rsGuestbook.MoveNext

Loop

'Reset server objects
rsGuestbook.Close
Set rsGuestbook = Nothing
Set adoCon = Nothing
%>
</body></html>

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-14 : 21:16:54

What's wrong with what you have?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

siausi82
Starting Member

2 Posts

Posted - 2004-11-14 : 21:55:41

I don't know...it just doesn't work

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-14 : 22:46:08

Do you get an error?
Does it connect to the server?
Does it create a recordset?

Just noticed this is access not sql server - might want to put it in the access forum.
You also seem to be opening the connection twice.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources