DaniWeb Forum Index > Web Development > ASP

Nested loop

Please support our ASP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

Last »

•

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

#11

Feb 14th, 2008

Okay here is the work around. And sorry, I left out the </td> last time, that's why it looks a bit funky.

 Help with Code Tags 
 ASP Syntax (Toggle Plain Text) 
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
 
        Response.Write("</td>")
        RS.MoveNext
      End If
    Next
 
    Response.Write("</tr>")
  loop
End If
 
Response.Write("</table>")
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

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

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

Response.Write("</table>")

•

Join Date: Feb 2008

Posts: 174

Reputation: TobbeK is an unknown quantity at this point

Solved Threads: 3

TobbeK

Offline

Junior Poster

Re: Nested loop

#12

Feb 14th, 2008

There is some error, i get back in short

•

Join Date: Feb 2008

Posts: 174

Reputation: TobbeK is an unknown quantity at this point

Solved Threads: 3

TobbeK

Offline

Junior Poster

Re: Nested loop

#13

Feb 14th, 2008

I am really grateful for all the help I can get. This is a tricky coding we are doing.

This is With the GROUP BY clause closed, so all of the middlecats can be received.

Here is the Screen

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"

Attached Thumbnails

•

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

#14

Feb 14th, 2008

I left in the "or" command with no argument.

 Help with Code Tags 
 ASP Syntax (Toggle Plain Text) 
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
          Response.Write(RS("middlecategory"))
        End If
 
        Response.Write("</td>")
        RS.MoveNext
      End If
    Next
 
    Response.Write("</tr>")
  loop
End If
 
Response.Write("</table>")
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
          Response.Write(RS("middlecategory"))
        End If

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

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

Response.Write("</table>")

•

Join Date: Feb 2008

Posts: 174

Reputation: TobbeK is an unknown quantity at this point

Solved Threads: 3

TobbeK

Offline

Junior Poster

Re: Nested loop

#15

Feb 14th, 2008

Yes i noticed the Or argument before and take it away, but it is the same output as my last post.

Is there anyway to count the middlecategory posts for each of the topcategory, flag the last middlecategory and write a </tr> if the last post has been reached. I have tried that but without success.

What I am trying to do here is (a simple) small directory link table (like Yahoo). With Topcategory and undercategory links (URLs) Nothing fancy, even if it is trickier that first thought.

Last edited by TobbeK; Feb 14th, 2008 at 3:37 pm.

•

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

#16

Feb 14th, 2008

Okay, then this is the way I would do it if you don't mind me rewriting:

 Help with Code Tags 
 ASP Syntax (Toggle Plain Text) 
Response.Write("<table width='570' border='1'>")
 
Do While Not RS.EOF
  Response.Write("<tr>")
 
  For i = 1 To 3
    If Not RS.EOF Then
      Response.Write("<td width='190'>")
 
      TopCat = RS("topcategory")
      MiddleCat = RS("middlecategory")
 
      If TopCat <> "" Then
        Response.Write(RS("topcategory") & "<br>")
 
        If MiddleCat <> "" Then
          Response.Write("-----------------------------<br>")
          Response.Write(RS("middlecategory"))
        End If
      End If
 
      TopCat = ""
      MiddleCat = ""
 
      Response.Write("</td>")
      RS.MoveNext
    Else
      Response.Write("<td width='190'></td>")
    End If
  Next
 
  Response.Write("</tr>")
Loop
 
Response.Write("</table>")
Response.Write("<table width='570' border='1'>")

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

  For i = 1 To 3
    If Not RS.EOF Then
      Response.Write("<td width='190'>")

      TopCat = RS("topcategory")
      MiddleCat = RS("middlecategory")

      If TopCat <> "" Then
        Response.Write(RS("topcategory") & "<br>")

        If MiddleCat <> "" Then
          Response.Write("-----------------------------<br>")
          Response.Write(RS("middlecategory"))
        End If
      End If

      TopCat = ""
      MiddleCat = ""

      Response.Write("</td>")
      RS.MoveNext
    Else
      Response.Write("<td width='190'></td>")
    End If
  Next

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

Response.Write("</table>")

•

Join Date: Feb 2008

Posts: 174

Reputation: TobbeK is an unknown quantity at this point

Solved Threads: 3

TobbeK

Offline

Junior Poster

Re: Nested loop

#17

Feb 14th, 2008

It's getting better. But there still is some repeating

Check the attach.

•

Originally Posted by SheSaidImaPregy

Okay, then this is the way I would do it if you don't mind me rewriting:
Help with Code Tags

ASP Syntax (Toggle Plain Text)
Response.Write("<table width='570' border='1'>")
 
Do While Not RS.EOF
 Response.Write("<tr>")
 
 For i = 1 To 3
 If Not RS.EOF Then
 Response.Write("<td width='190'>")
 
 TopCat = RS("topcategory")
 MiddleCat = RS("middlecategory")
 
 If TopCat <> "" Then
 Response.Write(RS("topcategory") & " ")
 
 If MiddleCat <> "" Then
 Response.Write("----------------------------- ")
 Response.Write(RS("middlecategory"))
 End If
 End If
 
 TopCat = ""
 MiddleCat = ""
 
 Response.Write("</td>")
 RS.MoveNext
 Else
 Response.Write("<td width='190'></td>")
 End If
 Next
 
 Response.Write("</tr>")
Loop
 
Response.Write("</table>")
Response.Write("<table width='570' border='1'>") Do While Not RS.EOF Response.Write("<tr>") For i = 1 To 3 If Not RS.EOF Then Response.Write("<td width='190'>") TopCat = RS("topcategory") MiddleCat = RS("middlecategory") If TopCat <> "" Then Response.Write(RS("topcategory") & " ") If MiddleCat <> "" Then Response.Write("----------------------------- ") Response.Write(RS("middlecategory")) End If End If TopCat = "" MiddleCat = "" Response.Write("</td>") RS.MoveNext Else Response.Write("<td width='190'></td>") End If Next Response.Write("</tr>") Loop Response.Write("</table>")

Attached Thumbnails

•

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

#18

Feb 14th, 2008

That's because I didn't keep the If TopCat is the same as the LastTopCat. I don't know how your DB is set up, so here's the good code:

 Help with Code Tags 
 ASP Syntax (Toggle Plain Text) 
Response.Write("<table width='570' border='1'>")
 
Do While Not RS.EOF
  Response.Write("<tr>")
 
  For i = 1 To 3
    If Not RS.EOF Then
      Response.Write("<td width='190'>")
 
      TopCat = RS("topcategory")
      MiddleCat = RS("middlecategory")
 
      If TopCat <> "" and TopCat <> LastTopCat Then
        Response.Write(RS("topcategory") & "<br>")
        LastTopCat = TopCat
 
        If MiddleCat <> "" Then
          Response.Write("-----------------------------<br>")
          Response.Write(RS("middlecategory"))
        End If
      End If
 
      TopCat = ""
      MiddleCat = ""
 
      Response.Write("</td>")
      RS.MoveNext
    Else
      Response.Write("<td width='190'></td>")
    End If
  Next
 
  Response.Write("</tr>")
Loop
 
Response.Write("</table>")
Response.Write("<table width='570' border='1'>")

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

  For i = 1 To 3
    If Not RS.EOF Then
      Response.Write("<td width='190'>")

      TopCat = RS("topcategory")
      MiddleCat = RS("middlecategory")

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

        If MiddleCat <> "" Then
          Response.Write("-----------------------------<br>")
          Response.Write(RS("middlecategory"))
        End If
      End If

      TopCat = ""
      MiddleCat = ""

      Response.Write("</td>")
      RS.MoveNext
    Else
      Response.Write("<td width='190'></td>")
    End If
  Next

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

Response.Write("</table>")

However, your "DISTINCT" should have picked through this so there are no repetitions.

Last edited by SheSaidImaPregy; Feb 14th, 2008 at 4:17 pm.

•

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

#19

Feb 14th, 2008

Try changing your SQL to this before changing the above code.

 Help with Code Tags 
 ASP Syntax (Toggle Plain Text) 
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 "
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 "

•

Join Date: Feb 2008

Posts: 174

Reputation: TobbeK is an unknown quantity at this point

Solved Threads: 3

TobbeK

Offline

Junior Poster

Re: Nested loop

#20

Feb 14th, 2008

Nope the last one did not work (same error as two posts ago)

I cut the source of your code before the last one.

I looks good, but the topcategories is repeated with the middlecategories.

On the LEFT side you see the topcategories and on the RIGHT the middlecategories.
The topcategories should be written only once.

<table width='570' border='1'>
<tr>
<td width='190'>Bil, Båt & Motor ----------------------------- Bilservice</td>
<td width='190'>Bil, Båt & Motor ----------------------------- Bildelar & Biltillbehör</td>
<td width='190'>Butiker & Shopping ----------------------------- Hälsa & Skönhet</td>
</tr>
<tr>
<td width='190'>Butiker & Shopping ----------------------------- Bygg & Trädgård</td>
<td width='190'>Butiker & Shopping ----------------------------- Hus, Hem & Trädgård</td>
<td width='190'>Butiker & Shopping ----------------------------- Film, Musik & Spel</td>
</tr>
<tr>
<td width='190'>Butiker & Shopping ----------------------------- Mobilt & Telefoni</td>
<td width='190'>Butiker & Shopping ----------------------------- Kläder, Mode & Skor</td>
<td width='190'>Butiker & Shopping ----------------------------- Glasögon & Kontaktlinser</td>
</tr>
<tr>
<td width='190'>Butiker & Shopping ----------------------------- Hemelektronik & Foto</td>
<td width='190'>Butiker & Shopping ----------------------------- Företag & Kontor</td>
<td width='190'>Data, IT & Telekommunikation ----------------------------- Datorer & Nätverk</td>
</tr>
<tr>
<td width='190'>Data, IT & Telekommunikation ----------------------------- Mobilt & Telefoni</td>
<td width='190'>Data, IT & Telekommunikation ----------------------------- Internettjänster</td>
<td width='190'>Ekonomi & Juridik ----------------------------- Advokat- & Juristbyråer</td>
</tr>
<tr>
<td width='190'>Ekonomi & Juridik ----------------------------- Administration & Redovisning</td>
<td width='190'>Ekonomi & Juridik ----------------------------- Bank, Finans & Egendom</td>
<td width='190'>Industri & Transport ----------------------------- Åkeri & Transporttjänster</td>
</tr>
<tr>
<td width='190'>Marknadsföring & Media ----------------------------- Reklam & Annons</td>
<td width='190'>Marknadsföring & Media ----------------------------- Internetmarknadsföring</td>
<td width='190'>Marknadsföring & Media ----------------------------- Design & Formgivning</td>
</tr>
<tr>
<td width='190'>Marknadsföring & Media ----------------------------- PR & Kommunikation</td>
<td width='190'>Sport & Fritid ----------------------------- Äventyr & Naturupplevelser</td>
<td width='190'>xx</td>
</tr>
</table>