...

View Full Version : Problem listing results from joined tables



henry-horse
03-04-2007, 05:38 PM
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:




'-- 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. :confused:

Many thanks
Pete

BarrMan
03-04-2007, 07:36 PM
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.

If Statement Then 'Statement could be - Parameter = value
'-------------
End If

One more thing, to check if your Record is not EOF just do:

If Not Rs.EOF Then

henry-horse
03-04-2007, 07:41 PM
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?

BarrMan
03-04-2007, 07:48 PM
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.

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

henry-horse
03-04-2007, 07:51 PM
Well spotted (groans)!

Thanks,

Pete



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum