Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP 3 Classic ASP Active Server Pages 3.0 > Classic ASP Basics
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Classic ASP Basics For beginner programmers starting with "classic" ASP 3, pre-".NET." NOT for ASP.NET 1.0, 1.1, or 2.0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 4th, 2007, 01:28 PM
Registered User
 
Join Date: Mar 2007
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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

Reply With Quote
  #2 (permalink)  
Old March 5th, 2007, 11:06 AM
Authorized User
Points: 254, Level: 5
Points: 254, Level: 5 Points: 254, Level: 5 Points: 254, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2007
Location: , , .
Posts: 54
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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]]
Reply With Quote
  #3 (permalink)  
Old March 5th, 2007, 11:07 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
Reply With Quote
  #4 (permalink)  
Old March 5th, 2007, 02:59 PM
Authorized User
 
Join Date: Feb 2007
Location: , , .
Posts: 42
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hate it when people dont update threads they start.

Reply With Quote
  #5 (permalink)  
Old March 7th, 2007, 04:39 PM
Registered User
 
Join Date: Mar 2007
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Reply With Quote
  #6 (permalink)  
Old March 9th, 2007, 07:08 AM
Authorized User
 
Join Date: Jan 2007
Location: , , India.
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to saurabhj Send a message via Yahoo to saurabhj
Default

Solos is gr8


Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem listing database tables raphaelsenra Pro VB Databases 0 January 6th, 2005 11:38 AM
Mysql Sorting results from multiple tables dcb22 SQL Language 8 October 25th, 2004 10:57 AM
Display results from multiple tables Librarian Classic ASP Databases 6 July 6th, 2004 11:28 PM
UPDATE with joined tables DanKent SQL Server 2000 4 July 6th, 2004 08:30 AM
inserting into joined tables cjennings SQL Server 2000 3 July 23rd, 2003 06:10 AM



All times are GMT -4. The time now is 12:07 PM.


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