...

View Full Version : Help need on Multiple Recordset



aspfantom
12-08-2004, 03:24 AM
I'm having trouble creating a query from multiple tables where the Primary Keys are the same. I'm trying to get both recordsets when the PK's match.



Here's my code:



<%
Set Conn = Server.CreateObject("ADODB.Connection")
FilePath = Server.MapPath("2004.mdb")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";"
set rs=Server.CreateObject("adodb.Recordset")
sql = "SELECT DatabaseFields.FirstName, DatabaseFields.LastName, Totals.SSN, Totals.LastName, Totals.PayoutTotal, Totals.FeeTotal, Totals.CRATotal"
sql = sql & " FROM DatabaseFields INNER JOIN Totals ON (DatabaseFields.SSN = Totals.SSN) AND (DatabaseFields.LastName = Totals.LastName);

rs.open sqlstmt, conn
If rs.eof then
response.write "<center>There are no records in the database"
response.write "<br>Please check back later</center>"
Else
%>


My trouble starts at the "INNER JOIN" AND "WHERE" statements

If I've made any other mistakes, corrections would be appreciated.


Someone PLEASE lead me in the right direction!!



Thanks in Advance!!

aspfantom
12-08-2004, 02:25 PM
Changed the statement but I'm still getting an Unterminated string constant
error message.

Can anyone help me find my problem



Here's what I got so far.



<%
Set Conn = Server.CreateObject("ADODB.Connection")
FilePath = Server.MapPath("Taxes2004.mdb")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";"
set rs=Server.CreateObject("adodb.Recordset")
sql = "SELECT DatabaseFields.SSN AS SSN,
DatabaseFields.FirstName AS FName,
DatabaseFields.LastName AS LName,
DatabaseFields.Address AS Address,
DatabaseFields.State AS State,
DatabaseFields.City AS City,
DatabaseFields.Email AS Email,
DatabaseFields.HomePhone AS HPhone,
DatabaseFields.WorkPhone AS WPhone,
DatabaseFields.WorkExtension AS WExt,
DatabaseFields.PostalCode AS Zip,
DatabaseFields.MobilePhone AS MPhone,
DatabaseFields.Birthdate AS BDate,
DatabaseFields.Occupation AS Job,
DatabaseFields.FilingStatus AS FileStat,
Totals.SW2Total AS SW2Total,
Totals.NSW2Total AS NSW2Total,
Totals.PayoutTotal AS PayoutTotal,
Totals.DepRefNumber AS RefNumber,
Totals.DepRefName AS RefName,
Totals.DepXFee AS DepXFee,
Totals.DepXChildSSN AS ChildSSN
FROM DatabaseFields
INNER JOIN Totals ON (DatabaseFields.SSN = Totals.SSN)
WHERE ((DatabaseFields.SSN)='"&SNN&"');"

rs.open sqlstmt, conn
If rs.eof then
response.write "<center>There are no records in the database"
response.write "<br>Please check back later</center>"
Else
%>



See initial thread to see my intentions.

Thanks in advance.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum