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 5 of 5
  1. #1
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem listing results from joined tables

    Hi, hoping someone can help me with the following problem if I ask nicely!

    I'm trying to set up a web page that lists a number of 'committees' and the members of each committee underneath the committee heading. I've set up three tables which hold the relevant info:

    tbl_addbook_entries - contains the details for each person, name, email address etc.
    tbl_addbook_committees - contains the name of each committee
    tbl_addbook_committeemembers - a table containing two foreign keys to group committees and people together (one committee can have many members, one member can be in one or more committees)

    I have written the asp code as follows to generate the committee 'breakdown' listing as follows:



    Code:
    '-- Connect to the database --
    set conn = server.createobject("ADODB.Connection")
    conn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strMDBPath
    
    '-- Retrieve committee members and details --
    sqlstring = "SELECT tbl_addbook_committees.order, tbl_addbook_committees.title, tbl_addbook_committees.role_title, tbl_addbook_entries_chairperson.name, tbl_addbook_entries_chairperson.surname, tbl_addbook_entries_chairperson.telephone, tbl_addbook_entries_chairperson.mobile, tbl_addbook_entries_chairperson.email, tbl_addbook_entries_chairperson.reveal_telephone, tbl_addbook_entries_chairperson.reveal_mobile, tbl_addbook_entries_chairperson.reveal_email, tbl_addbook_committeemembers.order, tbl_addbook_committeemembers.role_title, tbl_addbook_committeemembers.vacant, tbl_addbook_entries.name, tbl_addbook_entries.surname, tbl_addbook_entries.telephone, tbl_addbook_entries.mobile, tbl_addbook_entries.email, tbl_addbook_entries.reveal_telephone, tbl_addbook_entries.reveal_mobile, tbl_addbook_entries.reveal_email FROM (tbl_addbook_entries AS tbl_addbook_entries_chairperson INNER JOIN tbl_addbook_committees ON tbl_addbook_entries_chairperson.id = tbl_addbook_committees.chairperson) INNER JOIN (tbl_addbook_entries RIGHT JOIN tbl_addbook_committeemembers ON tbl_addbook_entries.id = tbl_addbook_committeemembers.member) ON tbl_addbook_committees.id = tbl_addbook_committeemembers.comittee ORDER BY tbl_addbook_committees.order, tbl_addbook_committeemembers.order"
    set rsuser=server.createobject("ADODB.Recordset")
    rsuser.open sqlstring, conn, 1, 2
    
    '-- List the records from the address book one by one --
    firstrecord = True
    if (rsuser.eof <> True) then
      rsuser.movefirst
      while not rsuser.eof
        if ((rsuser("title")) = previous_title) then
          '-- Output HTML code for next Committee member if not the first --
          htmlcode = htmlcode & "<font face='Arial' size='2'><b>" & rsuser("tbl_addbook_committeemembers.role_title") & ":</b> " & rsuser("tbl_addbook_entries.name") & " " & rsuser("tbl_addbook_entries.surname") & "</font><br>"
          if ((rsuser("tbl_addbook_entries.reveal_telephone")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Tel: " & rsuser("tbl_addbook_entries.telephone") & "</font><br>"
          end if
          if ((rsuser("tbl_addbook_entries.reveal_mobile")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Mob: " & rsuser("tbl_addbook_entries.mobile") & "</font><br>"
          end if
          if ((rsuser("tbl_addbook_entries.reveal_email")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Email: " & rsuser("tbl_addbook_entries.email") & "</font><br>"
          end if
          htmlcode = htmlcode & "<br>"
          response.write htmlcode
        else
          '-- If not first committee listed, output a horizontal line --
          if firstrecord <> True then
            response.write "<hr color='#800080' size='1'>"
          end if
          
          '-- Output HTML code for Committee title and chairperson --
          htmlcode = "<font face='Arial' size='5' color='#800080'><b>" & rsuser("title") & "</b></font><br><br>"
          htmlcode = htmlcode & "<font face='Arial' size='2'><b>Chairperson: " & rsuser("tbl_addbook_committees.role_title") & "</b></font><br>"
          htmlcode = htmlcode & "<font face='Arial' size='2'>" & rsuser("tbl_addbook_entries_chairperson.name") & " " & rsuser("tbl_addbook_entries_chairperson.surname") & "</font><br>"
          if ((rsuser("tbl_addbook_entries_chairperson.reveal_telephone")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Tel: " & rsuser("tbl_addbook_entries_chairperson.telephone") & "</font><br>"
          end if
          if ((rsuser("tbl_addbook_entries_chairperson.reveal_mobile")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Mob: " & rsuser("tbl_addbook_entries_chairperson.mobile") & "</font><br>"
          end if
          if ((rsuser("tbl_addbook_entries_chairperson.reveal_email")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Email: " & rsuser("tbl_addbook_entries_chairperson.email") & "</font><br>"
          end if
          htmlcode = htmlcode & "<br>"
          response.write htmlcode
          
          '-- Output HTML code for first Committee member --
          htmlcode = htmlcode & "<font face='Arial' size='2'><b>" & rsuser("tbl_addbook_committeemembers.role_title") & ":</b> " & rsuser("tbl_addbook_entries.name") & " " & rsuser("tbl_addbook_entries.surname") & "</font><br>"
          if ((rsuser("tbl_addbook_entries.reveal_telephone")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Tel: " & rsuser("tbl_addbook_entries.telephone") & "</font><br>"
          end if
          if ((rsuser("tbl_addbook_entries.reveal_mobile")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Mob: " & rsuser("tbl_addbook_entries.mobile") & "</font><br>"
          end if
          if ((rsuser("tbl_addbook_entries.reveal_email")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Email: " & rsuser("tbl_addbook_entries.email") & "</font><br>"
          end if
          htmlcode = htmlcode & "<br>"      
          response.write htmlcode
        end if  
        previous_title = rsuser ("title")
        rsuser.movenext
      wend
    else
      response.write "<tr><td colspan=3><font face='Arial' size=2>Sorry no records were found</font></td></tr>"
    end if
    
    '-- Close the database --
    rsuser.close
    set rsuser = nothing
    conn.close
    set conn = nothing
    set x = nothing

    But the page displays the records like this: http://www.ahbpc.org.uk/calendar/addbook_preview.asp (i.e. for each record it repeats the details of all the previous records).

    Can anyone shed any light on what I've done wrong? Most grateful for any pointers / help.

    Many thanks
    Pete
    Last edited by henry-horse; 03-04-2007 at 08:38 PM.

  • #2
    Senior Coder BarrMan's Avatar
    Join Date
    Feb 2005
    Location
    Israel.
    Posts
    1,644
    Thanks
    69
    Thanked 83 Times in 82 Posts
    Hey,
    It's kinda hard for me to read it because it's in the QUOTE tag instead of the CODE tag and because you write the if statements like c/javascript.
    In asp you don't need to add the parenthesis.
    Code:
    If Statement Then 'Statement could be - Parameter = value
    '-------------
    End If
    One more thing, to check if your Record is not EOF just do:
    Code:
    If Not Rs.EOF Then

  • #3
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the advice, sorry I'm not that great with ASP, much more comfortable with C! Sorry didn't realise I'd used the wrong tags... now changed. Do you think this is a problem with my if statements or am I not querying the data in the correct way?

  • #4
    Senior Coder BarrMan's Avatar
    Join Date
    Feb 2005
    Location
    Israel.
    Posts
    1,644
    Thanks
    69
    Thanked 83 Times in 82 Posts
    Much better, thanks.
    I saw in your code that you don't initialize htmlcode when it's printed and that might cause your problem.
    I marked what I'd added with bold tags so you could see it.
    Code:
    '-- Connect to the database --
    set conn = server.createobject("ADODB.Connection")
    conn.open "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=" & strMDBPath
    
    '-- Retrieve committee members and details --
    sqlstring = "SELECT tbl_addbook_committees.order, tbl_addbook_committees.title, tbl_addbook_committees.role_title, tbl_addbook_entries_chairperson.name, tbl_addbook_entries_chairperson.surname, tbl_addbook_entries_chairperson.telephone, tbl_addbook_entries_chairperson.mobile, tbl_addbook_entries_chairperson.email, tbl_addbook_entries_chairperson.reveal_telephone, tbl_addbook_entries_chairperson.reveal_mobile, tbl_addbook_entries_chairperson.reveal_email, tbl_addbook_committeemembers.order, tbl_addbook_committeemembers.role_title, tbl_addbook_committeemembers.vacant, tbl_addbook_entries.name, tbl_addbook_entries.surname, tbl_addbook_entries.telephone, tbl_addbook_entries.mobile, tbl_addbook_entries.email, tbl_addbook_entries.reveal_telephone, tbl_addbook_entries.reveal_mobile, tbl_addbook_entries.reveal_email FROM (tbl_addbook_entries AS tbl_addbook_entries_chairperson INNER JOIN tbl_addbook_committees ON tbl_addbook_entries_chairperson.id = tbl_addbook_committees.chairperson) INNER JOIN (tbl_addbook_entries RIGHT JOIN tbl_addbook_committeemembers ON tbl_addbook_entries.id = tbl_addbook_committeemembers.member) ON tbl_addbook_committees.id = tbl_addbook_committeemembers.comittee ORDER BY tbl_addbook_committees.order, tbl_addbook_committeemembers.order"
    set rsuser=server.createobject("ADODB.Recordset")
    rsuser.open sqlstring, conn, 1, 2
    
    '-- List the records from the address book one by one --
    firstrecord = True
    if (rsuser.eof <> True) then
      rsuser.movefirst
      while not rsuser.eof
        if ((rsuser("title")) = previous_title) then
          '-- Output HTML code for next Committee member if not the first --
          htmlcode = htmlcode & "<font face='Arial' size='2'><b>" & rsuser("tbl_addbook_committeemembers.role_title") & ":</b> " & rsuser("tbl_addbook_entries.name") & " " & rsuser("tbl_addbook_entries.surname") & "</font><br>"
          if ((rsuser("tbl_addbook_entries.reveal_telephone")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Tel: " & rsuser("tbl_addbook_entries.telephone") & "</font><br>"
          end if
          if ((rsuser("tbl_addbook_entries.reveal_mobile")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Mob: " & rsuser("tbl_addbook_entries.mobile") & "</font><br>"
          end if
          if ((rsuser("tbl_addbook_entries.reveal_email")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Email: " & rsuser("tbl_addbook_entries.email") & "</font><br>"
          end if
          htmlcode = htmlcode & "<br>"
          response.write htmlcode
          htmlcode = ""
        else
          '-- If not first committee listed, output a horizontal line --
          if firstrecord <> True then
            response.write "<hr color='#800080' size='1'>"
          end if
          
          '-- Output HTML code for Committee title and chairperson --
          htmlcode = "<font face='Arial' size='5' color='#800080'><b>" & rsuser("title") & "</b></font><br><br>"
          htmlcode = htmlcode & "<font face='Arial' size='2'><b>Chairperson: " & rsuser("tbl_addbook_committees.role_title") & "</b></font><br>"
          htmlcode = htmlcode & "<font face='Arial' size='2'>" & rsuser("tbl_addbook_entries_chairperson.name") & " " & rsuser("tbl_addbook_entries_chairperson.surname") & "</font><br>"
          if ((rsuser("tbl_addbook_entries_chairperson.reveal_telephone")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Tel: " & rsuser("tbl_addbook_entries_chairperson.telephone") & "</font><br>"
          end if
          if ((rsuser("tbl_addbook_entries_chairperson.reveal_mobile")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Mob: " & rsuser("tbl_addbook_entries_chairperson.mobile") & "</font><br>"
          end if
          if ((rsuser("tbl_addbook_entries_chairperson.reveal_email")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Email: " & rsuser("tbl_addbook_entries_chairperson.email") & "</font><br>"
          end if
          htmlcode = htmlcode & "<br>"
          response.write htmlcode
          htmlcode = ""
          
          '-- Output HTML code for first Committee member --
          htmlcode = htmlcode & "<font face='Arial' size='2'><b>" & rsuser("tbl_addbook_committeemembers.role_title") & ":</b> " & rsuser("tbl_addbook_entries.name") & " " & rsuser("tbl_addbook_entries.surname") & "</font><br>"
          if ((rsuser("tbl_addbook_entries.reveal_telephone")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Tel: " & rsuser("tbl_addbook_entries.telephone") & "</font><br>"
          end if
          if ((rsuser("tbl_addbook_entries.reveal_mobile")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Mob: " & rsuser("tbl_addbook_entries.mobile") & "</font><br>"
          end if
          if ((rsuser("tbl_addbook_entries.reveal_email")) = true) then
            htmlcode = htmlcode & "<font face='Arial' size='2'>Email: " & rsuser("tbl_addbook_entries.email") & "</font><br>"
          end if
          htmlcode = htmlcode & "<br>"      
          response.write htmlcode
        end if  
        previous_title = rsuser ("title")
        rsuser.movenext
      wend
    else
      response.write "<tr><td colspan=3><font face='Arial' size=2>Sorry no records were found</font></td></tr>"
    end if
    
    '-- Close the database --
    rsuser.close
    set rsuser = nothing
    conn.close
    set conn = nothing
    set x = nothing
    Last edited by BarrMan; 03-04-2007 at 08:51 PM.

  • #5
    New to the CF scene
    Join Date
    Mar 2007
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Well spotted (groans)!

    Thanks,

    Pete


  •  

    Posting Permissions

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