Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | 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 June 2nd, 2004, 09:43 AM
Friend of Wrox
 
Join Date: Aug 2003
Location: Halifax, Nova Scotia, Canada.
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
Default Loop through 2 tables?

Okay this is going to be really hard to explain but I will give it a shot. I have 2 tables, Each has a linking column ("EventId"). So this is what I need it to do. One table I want to use for the title of each table, the second table I want to use for the body of the table. How do I loop through one table to get the title, then find all the records that match the title for the body. Then close the table and start with the next table. I don't even know if that makes senes I will try and illustrate what I want.
(From one database)
Event:
Title:
------------------------
(from the other)
postition,Number,time,date,name
--------------------------------

But I need them to match each other with the "EventId"

Thanks



__________________
-----------------------------------------------------------
\"Don\'t follow someone who\'s not going anywhere\" John Mason
Reply With Quote
  #2 (permalink)  
Old June 2nd, 2004, 10:13 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi morpheus,

Is there a 1 on 1 relation between the two tables? Or does Table 2 contain multiple records for each record in table 1?

In a 1 on 1 scenario, can't you just join them?

SELECT Table1.Column1, Table1.Column2, Table2.Column1, Table2.Column2 FROM Table1 INNER JOIN Table2 ON Table1.EventId = Table2.EventId

This returns a set like this:
Table1.Column1 Table1.Column2 Table2.Column1 Table2.Column2
Bla More Bla Some more bla And even more

Is this what you mean? And when you talk about Body and Title, are you referring to HTML tables or database tables?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #3 (permalink)  
Old June 2nd, 2004, 10:53 AM
Friend of Wrox
 
Join Date: Aug 2003
Location: Halifax, Nova Scotia, Canada.
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I think there is a 1 on 1 relationship. only the eventId links the two tables. Yes I was talking about Html tables, but that's not the big issue I can make them later. I understand what you are saying but it's more complicated than that. Say there is 5 records in Table1 and 25 in table 2. I am going to have 5 records in table2 that correspond to 1 record in table 1.
So lets say that Table1(EventId= 1) correspond to 1 record.
Then Table2 (EventId =1) correspond to 5 records.

So I want this to be displayed
Table 1
Colmun 1, Colmun 2
Table 2
Column 1, Column 2, Column 3, Column 4, Cloumn 5,

Where all columns in both tables are "EventId =1"

Then I want to repeat this for every record in table1, but in it's own seperate HTML Table.

Thanks
Reply With Quote
  #4 (permalink)  
Old June 2nd, 2004, 12:19 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Right, so there is a one to many relation. That is, one record in Table 1 can have many records in table 2, right?

There are a couple of ways to do what you need to do, but before I get into the details, let me confirm that we're both talking about the same stuff. Let's call table 1 Authors and table 2 books. Then 1 author can write many books, so there is a one to many relation between the Authors and the Books table. With the query I showed you earlier, you'd end up with a result set like this:

AuthorFirstName AuthorLastName BookTitle BookISBN
First Name 1 Last Name 1 Book 1 1234
First Name 1 Last Name 1 Book 2 2345
First Name 1 Last Name 1 Book 3 3456
First Name 2 Last Name 2 Book 4 4567

The first two columns come from the Authors table, and the last two from the Books table.

But you want to display it like this:

First Name 1 Last Name 1
Book 1 1234
Book 2 2345
Book 3 3456

First Name 2 Last Name 2
Book 4 4567

Is that correct? Is this the format you're after?

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #5 (permalink)  
Old June 2nd, 2004, 12:27 PM
Friend of Wrox
 
Join Date: Aug 2003
Location: Halifax, Nova Scotia, Canada.
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes that is exactly right, I was using an sql statment like the one you gave me earlier but after that I was lost. I am not sure which loops to use and how to get the data to display like that. Right now it just gets every record in there.

"Don't follow someone who's not going anywhere" John Mason
Reply With Quote
  #6 (permalink)  
Old June 2nd, 2004, 12:44 PM
Friend of Wrox
 
Join Date: Sep 2003
Location: Minneapolis, MN, USA.
Posts: 171
Thanks: 0
Thanked 1 Time in 1 Post
Default

I have had great success with this method in my HTML. Please let me know if it works for you.

<%
set rs = server.createobject("adodb.recordset")
sql = "select table1.Title, table2.*"
sql = sql & " from table1"
sql = sql & " INNER JOIN table2 ON table1.EventId = table2.EventId"
sql = sql & " order by table1.Title"
rs.open sql, cn
if not rs.eof then
    do while not rs.eof
%>


    <%if TitlePass <> rs("Title") then%>
        <tr>
            <td><%=rs("Title")%></td>
        </tr>
    <%end if%>
        <tr>
            <td><%=rs("Position")%></td>
            <td><%=rs("Number")%></td>
            <td><%=rs("Time")%></td>
            <td><%=rs("Date")%></td>
            <td><%=rs("Name")%></td>
        </tr>
    <%TitlePass = rs("Title")%>


<%
    rs.movenext
    loop
end if
rs.close
set rs = nothing
%>

Notice the routine I use for "TitlePass". I happened to show it in separate delimeters in this case (<% %>). This will allow the title to show every time the title changes. You must have your recordset ordered by "Title" first though in this case. The benefit is that you need not run nested recordsets to accomplish what you are doing. This is only one loop, which means it is way faster for large recordset results.

Reply With Quote
  #7 (permalink)  
Old June 2nd, 2004, 12:53 PM
Friend of Wrox
 
Join Date: Sep 2003
Location: Minneapolis, MN, USA.
Posts: 171
Thanks: 0
Thanked 1 Time in 1 Post
Default

Okay. Let me modify this a bit. I did realize you had two columns in your first table.

<%
set rs = server.createobject("adodb.recordset")
sql = "select table1.Column1 AS Column1, table1.Column2 AS Column2,"
sql = sql & " table2.Column1 AS Column3, table2.Column2 AS Column4,"
sql = sql & " table2.Column3 AS Column5, table2.Column4 AS Column6,"
sql = sql & " table2.Column5 AS Column7, table1.EventId AS EventId"
sql = sql & " from table1"
sql = sql & " INNER JOIN table2 ON table1.EventId = table2.EventId"
sql = sql & " order by table1.EventId"
rs.open sql, cn
if not rs.eof then
    do while not rs.eof
%>


    <%if EventPass <> rs("EventId") then%>
        <tr>
            <td><%=rs("Column1")%></td>
            <td><%=rs("Column2")%></td>
        </tr>
    <%end if%>
        <tr>
            <td><%=rs("Column3")%></td>
            <td><%=rs("Column4")%></td>
            <td><%=rs("Column5")%></td>
            <td><%=rs("Column6")%></td>
            <td><%=rs("Column7")%></td>
        </tr>
    <%EventPass = rs("EventId")%>


<%
    rs.movenext
    loop
end if
rs.close
set rs = nothing
%>
Reply With Quote
  #8 (permalink)  
Old June 2nd, 2004, 01:06 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Right, well, that leaves you with at least three possibilities to design this application:

1. Use Data Shaping.
Data Shaping is a bit of an odd beast. In short, Data Shaping allows you retrieve data from a database, where a specific column from a recordset can hold another recordset. So, suppose you select 5 authors, you get only 5 records. The column BooksWritten in turn holds a bound recordset for each author with the books they have written. It's been a while since I wrote my last Data Shaping statement, and I always had a bit of troubles getting them right the first time, so I can't make one up here on the fly. I do suggest to use this URL as a starting point, and see if it fits your needs:

http://msdn.microsoft.com/library/de...pingdetail.asp

Advantages
Pretty quick, and minimal network overhead. No duplicate data is sent over the wire

Disadvantages
Odd and difficult syntax. Especially when you need to maintain your app in six months time ;)

2. Execute a second query for the details
This option and option 3 might be the ones that make the most sense in your situation. Which one to choose depends on a number of factors, including the size of each record from table 1. Suppose you have 40 columns with author data and each author has written a 1,000 books, you are effectively retrieving 999 times 40 columns without actually using them (you only need the first author record and then display the book details). In such a scenario, this option might work. It goes like this (semi-pseudo code, without getting into details too much)
Code:
SQL: SELECT ID, Name FROM Author into rsAuthor
Do While Not rsAuthor.EOF
  Dim AuthorID
  AuthorID = rsAuthor("ID)
  ' Display details of author
  Response.Write("Name: " & rsAuthor("Name")
  ' get book details
  SQL: SELECT ID, Title, Summary FROM Books WHERE AuthorID = AuthorID into rsBooks
  Do While Not rsBooks.EOF
    ' Display book details here
    rsBooks.MoveNext()
  Loop
  rsAuthor.MoveNext()
Loop
In this example, there are two recordsets and loops. The outer loop displays the author data for each individual author. The inner loop retrieves the books details for each author.

Advantages
Minimum network overhead. No duplicate data is sent over the wire. Every author is sent once, and each book is sent once

Disadvantages
Needs an additional round-trip for each recordset with books. This can mean tens or even hundreds of Execute() calls depending on the number of authors.

3. Retrieve all data, and selectively display it.
In many scenarios, I think this is the preferred solution. You waste bandwidth because for each book you also send Author data, but you minimize the number of Execute statements / round-trips to the database. The SQL is basically what I showed you before. So, for each book, you retrieve all author data, but the client decides what to display and what to ignore. Something like this:
Code:
<%
  Dim objAuthorsAndBooks
  Dim rsAuthorsAndBooks
  Set objAuthorsAndBooks = Server.CreateObject("ADODB.Command")
  objAuthorsAndBooks.ActiveConnection = MyConnectionString
  objAuthorsAndBooks.CommandType = adCmdText
  objAuthorsAndBooks.CommandText = "SELECT Author.Name, Book.Title, " & _
        "Book.ISBN FROM Author INNER JOIN Book on AUthor.ID = Book.AuthorID ORDER BY Author.Name"
  Set rsAuthorsAndBooks = objAuthorsAndBooks.Execute()
  Set objAuthorsAndBooks = Nothing
  With rsAuthorsAndBooks
    Dim OldAuthorName
    If Not .EOF then
      ' Loop through entire recordset with duplicate author data and books
      Do While Not .EOF
        ' Are we displaying the same author as the previous record?
        ' This will not be the case for the first record
        ' and also not when you "switch" author.
        If .Fields("Name").Value <> OldAuthorName Then
          ' No, so display autor name, and update OldAuthorName variable
          OldAuthorName = .Fields("Name").Value
          Response.Write("<h2>" & .Fields("Name").Value & "</h2>")
        End If
        ' Now display the book stuff
        Response.Write(.Fields("Title").Value & "(" & _
               .Fields("ISBN").Value & ")<br />")
        .MoveNext()
      Loop            
    End If
    .Close
    Set rsAuthorsAndBooks = Nothing
  End With
%>
I added some comments to this code, so I think it's self-explanatory. If not, feel free to ask for clarification.

Advantages
Quick to build, easy to understand, and minimized round-trips: all data is retrieved with a single SELECT statement

Disadvantages
Waste of bandwidth. For each book, you retrieve the author data, but only use it the first time. For the other 999 books from the same author, you retrieve but discard the author information.
This scenario will only work when you can order by something in the Author table, so you get all author data grouped together. If you need to order by, say, the Books title, you're out of luck. But, from what I can see from your initial request, that's not what you're after.

Does this help??

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Reply With Quote
  #9 (permalink)  
Old June 2nd, 2004, 01:26 PM
Friend of Wrox
 
Join Date: Aug 2003
Location: Halifax, Nova Scotia, Canada.
Posts: 166
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your help guys, DaveGerard I couldn't get your code to work, I kept getting a "out of exceptable range" error.
Imar I have a question for ya

Dim objAuthorsAndBooks(Is this the name of both my table)
Dim objTblEventandTblRace ( is that how I would use this?)


-----------------------------------------------------------
"Don't follow someone who's not going anywhere" John Mason
Reply With Quote
  #10 (permalink)  
Old June 2nd, 2004, 01:33 PM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

No, not at all. Those names can be arbitrarily chosen. If you want, you could do this as well:

Dim MyCoolObjectThatWillGetARecordSetWithAuthorsAndBoo ks
...
Set MyCoolObjectThatWillGetARecordSetWithAuthorsAndBoo ks = Server.CreateObject("ADODB.Command")

But personally, I find those long variable names a bit hard too read. ;)
However, I often try to stick to some naming scheme. In this case, objAuthorsAndBooks says the variable will hold an object (a Command object) that is used to retrieve information about Authors and Books. cmdAuthorsAndBooks or MyCommand would have been good names as well.

All you need to change in my third example is the ConnectionString, the SQL statement and the stuff with .Fields("ISBN").Value
Where it reads Name, Title and ISBN etc, you should put in your column names.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
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
Loop through 17 tables in RecordSet object didimichael C# 1 July 18th, 2008 06:53 AM
loop through tables inside .mdb file using VB.NET remya1000 General .NET 3 September 24th, 2007 12:45 PM
creating tables within tables in access??? carswelljr Access 3 August 23rd, 2006 01:21 PM
Help with for-each loop athanatos XSLT 0 April 10th, 2006 07:20 PM
nested while loop doesn't loop hosefo81 PHP Databases 5 November 12th, 2003 08:46 AM



All times are GMT -4. The time now is 08:17 PM.


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