ASP RSS ASP RSS

Nested loop

Thread Solved
1 2 Last »

•
•
Join Date: Feb 2008
Posts: 174
Reputation: TobbeK is an unknown quantity at this point 
Solved Threads: 3
TobbeK TobbeK is offline Offline
Junior Poster

Nested loop

 
0
  #1
Feb 14th, 2008
Need some help with a nested loop.

This one (my example code below) prints out a correct 3 column HTML table with the recordset with proper opening and closing HTML tags AS LONG as the GRUOP BY clause is set by the topcategory Db table.

I think I may need a additional loop withing the existing one to handle the one-to-many database query without messing up the HTML table cells and rows.

EXAMPLE CODE:

SQL = "SELECT DISTINCT TC.topcategory,MC.middlecategory "&_
"FROM tbtopcategory TC,tbmiddlecategory MC,tbconnectcategory CC "&_
"WHERE TC.topcategoryID = CC.topcategorylink "&_
"AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
"GROUP BY TC.topcategory "
RS.Open SQL,Conn

Cols = 3

Response.Write("<table width='570' border='1'>")

If Not RS.EOF then

Do Until RS.EOF
Response.Write("<tr>")

For i = 1 To Cols

If RS.EOF then
Response.Write("<td width='190'>xx")
Else

Response.Write("<td width='190'>")

' I NEED START OF LOOP FROM HERE

TopCat = RS("topcategory")
If LastTopCat <> TopCat Then
Response.Write(""&RS("topcategory") &"<br>")
Response.Write("-----------------------------<br>")
LastTopCat = TopCat
End If

Response.Write(RS("middlecategory"))

' END OF LOOP TO HERE

RS.MoveNext

End If
Response.Write("</td>")
Next

Loop
End If
Response.Write("</tr>")
Response.Write("</table>")



thanks in advance
Torbjorn
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2007
Posts: 1,080
Reputation: SheSaidImaPregy is an unknown quantity at this point 
Solved Threads: 68
SheSaidImaPregy SheSaidImaPregy is offline Offline
Veteran Poster

Re: Nested loop

 
0
  #2
Feb 14th, 2008
It is just misplaced buddy, most of it anyway.
ASP Syntax (Toggle Plain Text) 
  1. SQL = "SELECT DISTINCT TC.topcategory,MC.middlecategory "&_
  2. "FROM tbtopcategory TC,tbmiddlecategory MC,tbconnectcategory CC "&_
  3. "WHERE TC.topcategoryID = CC.topcategorylink "&_
  4. "AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
  5. "GROUP BY TC.topcategory "
  6. RS.Open SQL,Conn
  7.  
  8. Cols = 3
  9.  
  10. Response.Write("<table width='570' border='1'>")
  11.  
  12. If Not RS.EOF then
  13.   Do Until RS.EOF
  14.     Response.Write("<tr>")
  15.  
  16.     For i = 1 To Cols
  17.       If RS.EOF then
  18.         Response.Write("<td width='190'>xx")
  19.       Else
  20.         Response.Write("<td width='190'>")
  21.  
  22.         TopCat = RS("topcategory")
  23.  
  24.         If LastTopCat <> TopCat Then
  25.           Response.Write(""&RS("topcategory") &"<br>")
  26.           Response.Write("-----------------------------<br>")
  27.           LastTopCat = TopCat
  28.         End If
  29.  
  30.         Response.Write(RS("middlecategory"))
  31.  
  32.       End If
  33.  
  34.       Response.Write("</td>")
  35.     Next
  36.  
  37.     Response.Write("</tr>")
  38.     RS.MoveNext
  39.   loop
  40. End If
  41.  
  42. Response.Write("</table>")
Last edited by SheSaidImaPregy; Feb 14th, 2008 at 11:49 am.
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 174
Reputation: TobbeK is an unknown quantity at this point 
Solved Threads: 3
TobbeK TobbeK is offline Offline
Junior Poster

Re: Nested loop

 
0
  #3
Feb 14th, 2008
Thank you for your reply

This is a one-to-many DB output so the middlecategory (an undercategory to topcategory) keep on creating new own cells out of bound.

I need those "middlecats" to stay within same cells as topcategory

best regards
Torbjorn
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2007
Posts: 1,080
Reputation: SheSaidImaPregy is an unknown quantity at this point 
Solved Threads: 68
SheSaidImaPregy SheSaidImaPregy is offline Offline
Veteran Poster

Re: Nested loop

 
0
  #4
Feb 14th, 2008
it doesn't make sense. Those two should be inside the same cell. Can you take a screenshot and post it up please?
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 174
Reputation: TobbeK is an unknown quantity at this point 
Solved Threads: 3
TobbeK TobbeK is offline Offline
Junior Poster

Re: Nested loop

 
0
  #5
Feb 14th, 2008
Here is screenshot of the HTML output

As you can see the (topcats) is the ones with "-------------" below. The rest is "middlecats" and they should onle be repeated once per cell together with topcats.

Torbjorn
Last edited by TobbeK; Feb 14th, 2008 at 12:44 pm.
Attached Thumbnails
table.jpg  
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2007
Posts: 1,080
Reputation: SheSaidImaPregy is an unknown quantity at this point 
Solved Threads: 68
SheSaidImaPregy SheSaidImaPregy is offline Offline
Veteran Poster

Re: Nested loop

 
0
  #6
Feb 14th, 2008
Not sure exactly what you want. The code works exactly how it is supposed to.

Are you trying to have each cell be different, regardless of column? If so, move this line:

RS.MoveNext

two lines up, right above the "Next" statement.
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 174
Reputation: TobbeK is an unknown quantity at this point 
Solved Threads: 3
TobbeK TobbeK is offline Offline
Junior Poster

Re: Nested loop

 
0
  #7
Feb 14th, 2008
I have attached an screenshot of setting (GROUP BY) is set to topcategory only.
And as you can see there is 1 topcategory per cell, also there is the first posts of middlecat in the same cell. No more is printed becourse of the limited GROUP BY clause.

This is everything should looks like except the rest of the missing middlecats

SEE ATTACHED SCREENSHOT (table_2.jpg)

SQL = "SELECT DISTINCT TC.topcategory,MC.middlecategory "&_
"FROM tbtopcategory TC,tbmiddlecategory MC,tbconnectcategory CC "&_
"WHERE TC.topcategoryID = CC.topcategorylink "&_
"AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
"GROUP BY TC.topcategory ASC "
RS.Open SQL,Conn




Now this is the closed GROUP BY clause with all of the topcats and middlecats. Only the first post of the middlecats stays with the topcats, and are the rest creating new own cells.

This is not how it should be..

SEE ATTACHED SCREENSHOT (table_3.jpg)

SQL = "SELECT DISTINCT TC.topcategory,MC.middlecategory "&_
"FROM tbtopcategory TC,tbmiddlecategory MC,tbconnectcategory CC "&_
"WHERE TC.topcategoryID = CC.topcategorylink "&_
"AND MC.tbmiddlecategoryID = CC.middlecategorylink " '&_
'"GROUP BY TC.topcategory ASC "
RS.Open SQL,Conn




Torbjorn
Last edited by TobbeK; Feb 14th, 2008 at 1:15 pm.
Attached Thumbnails
table_2.jpg   table_3.jpg  
Reply With Quote Quick reply to this message  
•
•
Join Date: Sep 2007
Posts: 1,080
Reputation: SheSaidImaPregy is an unknown quantity at this point 
Solved Threads: 68
SheSaidImaPregy SheSaidImaPregy is offline Offline
Veteran Poster

Re: Nested loop

 
0
  #8
Feb 14th, 2008
Then here you go:
ASP Syntax (Toggle Plain Text) 
  1. SQL = "SELECT DISTINCT TC.topcategory,MC.middlecategory "&_
  2. "FROM tbtopcategory TC,tbmiddlecategory MC,tbconnectcategory CC "&_
  3. "WHERE TC.topcategoryID = CC.topcategorylink "&_
  4. "AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
  5. "GROUP BY TC.topcategory "
  6. RS.Open SQL,Conn
  7.  
  8. Cols = 3
  9.  
  10. Response.Write("<table width='570' border='1'>")
  11.  
  12. If Not RS.EOF then
  13.   Do Until RS.EOF
  14.     Response.Write("<tr>")
  15.  
  16.     For i = 1 To Cols
  17.       If RS.EOF then
  18.         Response.Write("<td width='190'>xx")
  19.       Else
  20.         Response.Write("<td width='190'>")
  21.  
  22.         TopCat = RS("topcategory")
  23.  
  24.         If LastTopCat <> TopCat or Then
  25.           Response.Write(RS("topcategory") & "<br>")
  26.           Response.Write("-----------------------------<br>")
  27.           LastTopCat = TopCat
  28.           Response.Write(RS("middlecategory"))
  29.         End If
  30.  
  31.       End If
  32.  
  33.       Response.Write("</td>")
  34.       RS.MoveNext
  35.     Next
  36.  
  37.     Response.Write("</tr>")
  38.   loop
  39. End If
  40.  
  41. Response.Write("</table>")
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 174
Reputation: TobbeK is an unknown quantity at this point 
Solved Threads: 3
TobbeK TobbeK is offline Offline
Junior Poster

Re: Nested loop

 
0
  #9
Feb 14th, 2008
SEE THE ATTACHED SREENSHOT (table_4 and table_5) taken by a open GROUP BY clause and a closed one.

The error message is written in swedish but I guess it is enough to see what the problem is.




•
•
•
•
Originally Posted by SheSaidImaPregy View Post
Then here you go:
ASP Syntax (Toggle Plain Text) 
  1. SQL = "SELECT DISTINCT TC.topcategory,MC.middlecategory "&_
  2. "FROM tbtopcategory TC,tbmiddlecategory MC,tbconnectcategory CC "&_
  3. "WHERE TC.topcategoryID = CC.topcategorylink "&_
  4. "AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
  5. "GROUP BY TC.topcategory "
  6. RS.Open SQL,Conn
  7.  
  8. Cols = 3
  9.  
  10. Response.Write("<table width='570' border='1'>")
  11.  
  12. If Not RS.EOF then
  13.   Do Until RS.EOF
  14.     Response.Write("<tr>")
  15.  
  16.     For i = 1 To Cols
  17.       If RS.EOF then
  18.         Response.Write("<td width='190'>xx")
  19.       Else
  20.         Response.Write("<td width='190'>")
  21.  
  22.         TopCat = RS("topcategory")
  23.  
  24.         If LastTopCat <> TopCat or Then
  25.           Response.Write(RS("topcategory") & "<br>")
  26.           Response.Write("-----------------------------<br>")
  27.           LastTopCat = TopCat
  28.           Response.Write(RS("middlecategory"))
  29.         End If
  30.  
  31.       End If
  32.  
  33.       Response.Write("</td>")
  34.       RS.MoveNext
  35.     Next
  36.  
  37.     Response.Write("</tr>")
  38.   loop
  39. End If
  40.  
  41. Response.Write("</table>")
Attached Thumbnails
table_4.jpg   table_5.jpg  
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2008
Posts: 174
Reputation: TobbeK is an unknown quantity at this point 
Solved Threads: 3
TobbeK TobbeK is offline Offline
Junior Poster

Re: Nested loop

 
0
  #10
Feb 14th, 2008
Here is another code where everysting stays withing cells, BUT I can't break the table into 3 columns.

In this one I use an array containing the RS picked up by RS.GETROWS. Another approcah but same thinking.

SEE THE ATTACHED SCREENSHOT (table_6.jpg)

SQL = "SELECT DISTINCT TC.topcategory,MC.middlecategory "&_
"FROM tbtopcategory TC,tbmiddlecategory MC,tbconnectcategory CC "&_
"WHERE TC.topcategoryID = CC.topcategorylink "&_
"AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
"ORDER BY TC.topcategory ASC, MC.middlecategory ASC "
RS.Open SQL,Conn

arrDB = RS.GetRows()
iStart = LBound(arrDB,1)
iStop = UBound (arrDB,2)


Response.Write("<table width='570' border='1'>")

For i = iStart to iStop

TopCat = arrDB(0, i)
If LastTopCat <> TopCat Then
Response.Write("</td><td width='190'>")
TCat = arrDB(0, i) & "<br>"
Response.write TCat
LastTopCat = TopCat
End If

Response.write arrDB(1, i) & " , "

Next

Response.Write("</tr>")
Response.Write("</table>")


RS.Close
Conn.Close


Torbjorn
Attached Thumbnails
table_6.jpg  
Reply With Quote Quick reply to this message  
Reply
1 2 Last »

Quick Reply to Thread
This thread has been marked solved.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the ASP Forum
Thread Tools Search this Thread



archive asp asp.net aspandmssqlserver2005 aspandmssqlserver2005connection aspconnection calendar changeable connection current database databaseconnection diagnostics dreamweaver excel fso html iis microsoft msmsql mssql2005 mssqlserver2005 mssqlserver2005andasp mssqlserverandasp opentextfile record searchbox selectoption server single specfic sqlserver sqlserverconnection toolkit web webserver windows7
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC