•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the ASP section within the Web Development category of DaniWeb, a massive community of 428,634 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,003 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our ASP advertiser: Lunarpages ASP Web Hosting
Views: 3661 | Replies: 88 | Solved
 |
•
•
Join Date: Feb 2008
Posts: 135
Reputation: 
Rep Power: 1
Solved Threads: 3
Junior Poster
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
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
•
•
Join Date: Sep 2007
Posts: 1,057
Reputation:
Rep Power: 4
Solved Threads: 61
It is just misplaced buddy, most of it anyway.
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'>")
TopCat = RS("topcategory")
If LastTopCat <> TopCat Then
Response.Write(""&RS("topcategory") &"<br>")
Response.Write("-----------------------------<br>")
LastTopCat = TopCat
End If
Response.Write(RS("middlecategory"))
End If
Response.Write("</td>")
Next
Response.Write("</tr>")
RS.MoveNext
loop
End If
Response.Write("</table>") Last edited by SheSaidImaPregy : Feb 14th, 2008 at 10:49 am.
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
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
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
•
•
Join Date: Sep 2007
Posts: 1,057
Reputation:
Rep Power: 4
Solved Threads: 61
it doesn't make sense. Those two should be inside the same cell. Can you take a screenshot and post it up please?
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
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
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 11:44 am.
•
•
Join Date: Sep 2007
Posts: 1,057
Reputation:
Rep Power: 4
Solved Threads: 61
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.
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.
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
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
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 12:15 pm.
•
•
Join Date: Sep 2007
Posts: 1,057
Reputation:
Rep Power: 4
Solved Threads: 61
Then here you go:
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'>")
TopCat = RS("topcategory")
If LastTopCat <> TopCat or Then
Response.Write(RS("topcategory") & "<br>")
Response.Write("-----------------------------<br>")
LastTopCat = TopCat
Response.Write(RS("middlecategory"))
End If
End If
Response.Write("</td>")
RS.MoveNext
Next
Response.Write("</tr>")
loop
End If
Response.Write("</table>") •
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
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.
The error message is written in swedish but I guess it is enough to see what the problem is.
•
•
•
•
Originally Posted by SheSaidImaPregy 
Then here you go:
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'>") TopCat = RS("topcategory") If LastTopCat <> TopCat or Then Response.Write(RS("topcategory") & "<br>") Response.Write("-----------------------------<br>") LastTopCat = TopCat Response.Write(RS("middlecategory")) End If End If Response.Write("</td>") RS.MoveNext Next Response.Write("</tr>") loop End If Response.Write("</table>")
•
•
Join Date: Feb 2008
Posts: 135
Reputation:
Rep Power: 1
Solved Threads: 3
Junior Poster
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
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
|
•
•
•
•
•
•
•
•
DaniWeb ASP Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
Similar Threads
- nested for loop problem (Java)
- Can not enter multiple data into nested while loop (C++)
- problem creating a for loop for an array (C++)
- Nested loop that runs until user enters 'q' (C++)
- Do/While Loop help (C++)
- QBasic Nested for loop (Legacy and Other Languages)
- need help w/for loop (C++)
Other Threads in the ASP Forum
- Previous Thread: Help: passing variable via table field different page subroutine
- Next Thread: Convert Int to string classic asp
