Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Post Unanswered: display details in table2 for each table1 record

    hey folks
    i think this should be simple but i just cant do it.
    while looping through the records in table1 i want to loop through and display any and all records from table2 that match the record in table1.
    like this:

    (table1) ID 1 fruits
    (table2) ID 1 apple
    (table2) ID 1 orange
    (table2) ID 1 banana
    (table1) ID 2 vegetables
    (table2) ID 2 carrots
    (table2) ID 2 lettuce
    (table2) ID 2 broccoli

    any help would be greatly appreciated
    thanks,
    Jonah

  2. #2
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    You wanna do a nested loop---> (this is pesudo code)
    Code:
    open rs1 -> "select id,blah from table1"
    while not eof/bof rs1
      open rs2 -> "select * from table2 where COL=" & rs1.id
      while not eof/bof rs2  
       print rs1.blah & rs2.fruit
       next
      endwh
      close rs2
      next
    next
    close rs1
    hope this makes sense...

  3. #3
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    still stuck

    hello,

    i never did get this working, and i ended up skipping it, but now ive got the same problem again.

    i tried your suggestion, maybe im not opening the record sets correctly or somehting.

    could you give me an example with actual code?

    much appreciated,

    Jonah

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    Post the code you have so far.

  5. #5
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Post the code:

    sure thing...
    heres what ive got:

    ---------- the code starts here----------------------

    <TABLE width="215" BORDER=0 cellpadding="0">
    <TBODY>
    <%
    Session.timeout = 5
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\jonah\db\Jsystem.mdb")
    sql = "SELECT [codename].[course code], Max([schedule].[day]) AS day‚̍őå, Min([prices].[price]) AS minprice FROM (codename INNER JOIN schedule ON [codename].[course code]=[schedule].[course code]) INNER JOIN prices ON [codename].[course code]=[prices].[course code] GROUP BY [codename].[course code] HAVING (((codename.[course code]) Like '*S*'))"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
    If rs.eof Then
    rs.AddNew
    End If
    %>
    <%
    On Error Resume Next
    rs.MoveFirst
    do while Not rs.eof
    %>
    <TR>
    <TD width="80%"><img src="images/arrowgray.gif" width="11" height="11">
    <a href="http://www.hfinet.com/detailspage.asp?code=<% Response.write(rs("course code")) %>"><font size="2px"><span class="textstyle">
    <%
    sql2 = "SELECT schedule.[course code], schedule.location FROM schedule WHERE (((schedule.[course code]) = '" & rs.[course code] & "'"
    Set rs2 = Server.CreateObject("ADODB.Recordset")
    rs2.Open sql2, conn, 3, 3
    If rs2.eof Then
    rs2.AddNew
    End If
    %>
    <%
    On Error Resume Next
    rs2.MoveFirst
    do while Not rs2.eof
    %>
    <%=Response.Write(rs2("location"))%>
    <%
    rs2.MoveNext
    loop
    %>
    <%
    rs2.close
    set rs2 = nothing
    conn2.close
    set conn2=nothing
    %>

    <%=Response.Write(rs("days"))%></span></font>“úŠÔ</a>
    @</TD>
    <TD valign="top" nowrap BORDERCOLOR=#c0c0c0 ><span class="textstyle">
    @<font color="#FF0000">\<%=Server.HTMLEncode(formatnumber (rs.Fields("minprice").Value,0))%>`</font></span>
    </TD>
    </TR>
    <%
    rs.MoveNext
    loop
    %>
    <%
    rs.close
    set rs = nothing
    conn.close
    set conn=nothing
    %>
    </TBODY>
    </TABLE>

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    The following 3 lines can be deleted:

    set rs2 = nothing ' delete this because you want to reuse rs2
    conn2.close ' you dont have a conn2 object - you are using conn - which is ok
    set conn2=nothing ' no conn2 object

  7. #7
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Unhappy no go

    thanks for the tip,

    i went ahead and fixed what you said, but it had no effect.
    besides that everything else looks right?

  8. #8
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Talking Figured it out!

    man, looks like i just had a few extra parentheses in the wrong places.
    oops,
    i wish dreamweaver would color code those things like excel does.

    well, thanks for the help,

    heres the final code if it helps anybody else:




    <TABLE width="215" BORDER=0 cellpadding="0">
    <TBODY>
    <%
    Session.timeout = 5
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\jonah\db\Jsystem.mdb")
    sql = "SELECT [codename].[course code], Max([schedule].[day]) AS days, Min([prices].[price]) AS minprice FROM (codename INNER JOIN schedule ON [codename].[course code]=[schedule].[course code]) INNER JOIN prices ON [codename].[course code]=[prices].[course code] GROUP BY [codename].[course code] HAVING (((codename.[course code]) Like '%S%'))"
    Set rs = Server.CreateObject("ADODB.Recordset")
    rs.Open sql, conn, 3, 3
    If rs.eof Then
    rs.AddNew
    End If
    %>
    <%
    On Error Resume Next
    rs.MoveFirst
    do while Not rs.eof
    %>
    <TR>
    <TD width="80%"><img src="images/arrowgray.gif" width="11" height="11">
    <a href="http://www.hfinet.com/detailspage.asp?code=<% Response.write(rs("course code")) %>"><font size="2px"><span class="textstyle">
    <%
    sql2 = "SELECT DISTINCT location FROM schedule WHERE schedule.[course code] = '" & rs("course code") & "'"
    Set rs2 = Server.CreateObject("ADODB.Recordset")
    rs2.Open sql2, conn, 3, 3
    %>
    <%
    On Error Resume Next
    rs2.MoveFirst
    do while Not rs2.eof
    %>
    <%=Response.Write(rs2("location"))%>
    <%
    rs2.MoveNext
    loop
    %>
    <%
    rs2.close
    %>
    <%=Response.Write(rs("days"))%></span></font>“úŠÔ</a>
    @</TD>
    <TD valign="top" nowrap BORDERCOLOR=#c0c0c0 ><span class="textstyle">
    @<font color="#FF0000">\<%=Server.HTMLEncode(formatnumber (rs.Fields("minprice").Value,0))%>`</font></span>
    </TD>
    </TR>
    <%
    rs.MoveNext
    loop
    %>
    <%
    rs.close
    set rs = nothing
    conn.close
    set conn=nothing
    %>
    </TBODY>
    </TABLE>
    Last edited by jonah9; 12-25-03 at 03:47.

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    What was the error you were receiving ? And highlight the code you changed that resolved your issue.

  10. #10
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Lightbulb answer

    no error,
    it would just hang, as it always does when i have a misspelled field or bad syntax in my WHERE clause.

    there were actually a couple of problems in the code:

    1: i was using * instead of % as a wildcard in my WHERE clause (although i dont really know if this causes any problems)

    2: i forgot i had called a MIN on my "day" field as day‚̍őå but then tried to display it as "days" on accident

    3: and this was probably the worst, i put 3 open parentheses when there shouldnt have been any, or i should have closed them at least:

    sql2 = "SELECT schedule.[course code], schedule.location FROM schedule WHERE (((schedule.[course code]) = '" & rs.[course code] & "'"

    should have been:

    sql2 = "SELECT schedule.[course code], schedule.location FROM schedule WHERE schedule.[course code]) = '" & rs.[course code] & "'"

  11. #11
    Join Date
    Dec 2003
    Posts
    7

    Re: display details in table2 for each table1 record

    Can this be done using a SQL join? I am sure this could take care of your issue..and your recordset will only hold the matching infomation from your SQL query


    Originally posted by jonah9
    hey folks
    i think this should be simple but i just cant do it.
    while looping through the records in table1 i want to loop through and display any and all records from table2 that match the record in table1.
    like this:

    (table1) ID 1 fruits
    (table2) ID 1 apple
    (table2) ID 1 orange
    (table2) ID 1 banana
    (table1) ID 2 vegetables
    (table2) ID 2 carrots
    (table2) ID 2 lettuce
    (table2) ID 2 broccoli

    any help would be greatly appreciated
    thanks,
    Jonah

  12. #12
    Join Date
    Jul 2003
    Location
    Japan/California
    Posts
    68

    Question ?

    rhs98, im not sure what you mean.

    could you post some example code so i can see what youre talking about?

  13. #13
    Join Date
    Feb 2002
    Posts
    2,232
    jonah - thanks for posting the solution. I was wondering what those special characters were. Anyway, you can simply your solution by eliminating the select from location and reducing your resultset by putting the having clause as a where clause instead.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •