p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Classic ASP Basics (http://p2p.wrox.com/forumdisplay.php?f=61)
-   -   Listing results from joined tables (http://p2p.wrox.com/showthread.php?t=54862)

henry-horse March 4th, 2007 01:28 PM

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 & "<b>" & rsuser("tbl_addbook_committeemembers.role_title") & ":</b> " & rsuser("tbl_addbook_entries.name") & " " & rsuser("tbl_addbook_entries.surname") & "<br>"
      if ((rsuser("tbl_addbook_entries.reveal_telephone")) = true) then
        htmlcode = htmlcode & "Tel: " & rsuser("tbl_addbook_entries.telephone") & "<br>"
      end if
      if ((rsuser("tbl_addbook_entries.reveal_mobile")) = true) then
        htmlcode = htmlcode & "Mob: " & rsuser("tbl_addbook_entries.mobile") & "<br>"
      end if
      if ((rsuser("tbl_addbook_entries.reveal_email")) = true) then
        htmlcode = htmlcode & "Email: " & rsuser("tbl_addbook_entries.email") & "<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 ""
      end if

      '-- Output HTML code for Committee title and chairperson --
      htmlcode = "<b>" & rsuser("title") & "</b><br><br>"
      htmlcode = htmlcode & "<b>Chairperson: " & rsuser("tbl_addbook_committees.role_title") & "</b><br>"
      htmlcode = htmlcode & "" & rsuser("tbl_addbook_entries_chairperson.name") & " " & rsuser("tbl_addbook_entries_chairperson.surname") & "<br>"
      if ((rsuser("tbl_addbook_entries_chairperson.reveal_telephone")) = true) then
        htmlcode = htmlcode & "Tel: " & rsuser("tbl_addbook_entries_chairperson.telephone") & "<br>"
      end if
      if ((rsuser("tbl_addbook_entries_chairperson.reveal_mobile")) = true) then
        htmlcode = htmlcode & "Mob: " & rsuser("tbl_addbook_entries_chairperson.mobile") & "<br>"
      end if
      if ((rsuser("tbl_addbook_entries_chairperson.reveal_email")) = true) then
        htmlcode = htmlcode & "Email: " & rsuser("tbl_addbook_entries_chairperson.email") & "<br>"
      end if
      htmlcode = htmlcode & "<br>"
      response.write htmlcode

      '-- Output HTML code for first Committee member --
      htmlcode = htmlcode & "<b>" & rsuser("tbl_addbook_committeemembers.role_title") & ":</b> " & rsuser("tbl_addbook_entries.name") & " " & rsuser("tbl_addbook_entries.surname") & "<br>"
      if ((rsuser("tbl_addbook_entries.reveal_telephone")) = true) then
        htmlcode = htmlcode & "Tel: " & rsuser("tbl_addbook_entries.telephone") & "<br>"
      end if
      if ((rsuser("tbl_addbook_entries.reveal_mobile")) = true) then
        htmlcode = htmlcode & "Mob: " & rsuser("tbl_addbook_entries.mobile") & "<br>"
      end if
      if ((rsuser("tbl_addbook_entries.reveal_email")) = true) then
        htmlcode = htmlcode & "Email: " & rsuser("tbl_addbook_entries.email") & "<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>Sorry no records were found</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


crabjoe March 5th, 2007 11:06 AM

Pete,

Not sure what you mean. I just went to the link you provided and it seems to be fine. No duplications in records.

Here's the output I see:
================================
[[contact details removed by Admin]]

dparsons March 5th, 2007 11:07 AM

Agreed, i get the same output. Have you since resolved this issue?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from planoie's profile^^
^^Modified text taken from gbianchi profile^^
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

solos March 5th, 2007 02:59 PM

hate it when people dont update threads they start.


henry-horse March 7th, 2007 04:39 PM

Sorry, I did since resolve this but haven't been online since to update the thread, hope I have not caused any offence. Thanks for looking though.


saurabhj March 9th, 2007 07:08 AM

Solos is gr8




All times are GMT -4. The time now is 10:30 AM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.