Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New to the CF scene
    Join Date
    Dec 2004
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help need on Multiple Recordset

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

  • #2
    New to the CF scene
    Join Date
    Dec 2004
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •