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

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>")

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

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

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.

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

Attachments table_2.jpg 95.86 KB table_3.jpg 176.62 KB

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>")

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.

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>")
Attachments table_4.jpg 131.32 KB table_5.jpg 132.64 KB

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

Attachments table_6.jpg 152.69 KB

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

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>")

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"

Attachments table_7.jpg 97.99 KB

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

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>")

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.

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

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>")
Comments
Master coding

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

Check the attach.

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

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>")
Attachments table_8.jpg 295.66 KB

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:

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.

Try changing your SQL to this before changing the above 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 "

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

Ok so let me get this.. this is exactly how you're doing it, but in tables right:

Category 1
Mid Cat 1
Mid Cat 2
Mid Cat 3

Category 2
Mid Cat 1
Mid Cat 2
Mid Cat 3
Mid Cat 4

And so on..

Is that right?

This is how I would do it then, completely:

If Not RS.EOF then
  Response.Write("<table width=""570"" border=""1"">")
  
  Do Until RS.EOF
    Response.Write("<tr>")

    For i = 1 To Cols
      If Not RS.EOF Then
        TopCat = RS("topcategory")
        MiddleCat = RS("middlecategory")

        If LastTopCat <> TopCat and Flag1 = False Then
          Response.Write("<td width='190'>")
        End If

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

        If MiddleCat <> "" and LastTopCat = TopCat and Flag1 = True Then
          Response.Write(MiddleCat)
        End If

        If LastTopCat <> TopCat and Flag1 = True
          Response.Write("</td>")
          Flag1 = False
        Else
          RS.MoveNext
        End If
      End If
    Next

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

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

You have to set a new variable called "Flag1". Set it to False.

Glad to help.

the last code was crashed,

--
Response-objekt fel 'ASP 0251 : 80004005'
Svarsbuffertens längd har överskridits
/NYSITE/category_window.asp, rad 0
Körning av ASP-sidan gjorde att svarsbuffertens storlek överskreds.
--

I did some cut and paste in the earlier uotput source and this is what it should be compared to the one I send before.

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

That's what the code will do. What happened is that the response.buffer limit was exceeded. Add this line right after the "Do" statement:

response.flush

and make sure that you have this line at the top of the file:

response.buffer="true"

It hangs, no error

Here is my full code

response.buffer="true"
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

If Not RS.EOF then
Response.Write("<table width=""570"" border=""1"">")

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

For i = 1 To Cols
If Not RS.EOF Then
TopCat = RS("topcategory")
MiddleCat = RS("middlecategory")

If LastTopCat <> TopCat and Flag1 = False Then
Response.Write("<td width='190'>")
End If

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

If MiddleCat <> "" and LastTopCat = TopCat and Flag1 = True Then
Response.Write(MiddleCat)
End If

If LastTopCat <> TopCat and Flag1 = True Then
Response.Write("</td>")
Flag1 = False
Else
RS.MoveNext
End If
End If
Next

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

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

Conn.Close

Now it does hangs, but it keep on error
I can email you an Accessdatabase with the exakt tables as i have in my MySQL at this moment.

Its not that, it should work. If it isn't, it is because of settings on your server and you're grabbing way too many records for the recordset to loop through. Try this real quick, adding it on to your SQL statement

Add this onto your sql statement: " LIMIT 20"

I'm old fashion guy so I will stick to the classic. You are right I should update myselfl and my coding some day.

Can i send you the access db. I have make it with the same table structure and names. Maybe it easier to see it live.

There is only 20 posts in the DB so it should not hang things up.

I have tested the LIMIT 20 and there still erroring.

This question has already been answered. Start a new discussion instead.