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
Listing results from joined tables
