954,585 Members — Technology Publication meets Social Media
Username:
Password:
Lost login information?
Have something to say? Contribute New Article Reply to this Article
4 Years Ago
0

Nested loop

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

If Not RS.EOF then

Do Until RS.EOF
Response.Write("")

For i = 1 To Cols

If RS.EOF then
Response.Write("xx")
Else

Response.Write("")

' I NEED START OF LOOP FROM HERE

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

Response.Write(RS("middlecategory"))

' END OF LOOP TO HERE

RS.MoveNext

End If
Response.Write("")
Next

Loop
End If
Response.Write("")
Response.Write("")

thanks in advance
Torbjorn

TobbeK
Junior Poster
190 posts since Feb 2008
Reputation Points: 10
Solved Threads: 3
 
4 Years Ago
0

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") &"")
          Response.Write("-----------------------------")
          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>")
SheSaidImaPregy
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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

TobbeK
Junior Poster
190 posts since Feb 2008
Reputation Points: 10
Solved Threads: 3
 
4 Years Ago
0

it doesn't make sense. Those two should be inside the same cell. Can you take a screenshot and post it up please?

SheSaidImaPregy
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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

Attachments table.jpg 334.64KB
TobbeK
Junior Poster
190 posts since Feb 2008
Reputation Points: 10
Solved Threads: 3
 
4 Years Ago
0

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.

SheSaidImaPregy
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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.86KB table_3.jpg 176.62KB
TobbeK
Junior Poster
190 posts since Feb 2008
Reputation Points: 10
Solved Threads: 3
 
4 Years Ago
0

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") & "")
          Response.Write("-----------------------------")
          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>")
SheSaidImaPregy
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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") & "")
          Response.Write("-----------------------------")
          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.32KB table_5.jpg 132.64KB
TobbeK
Junior Poster
190 posts since Feb 2008
Reputation Points: 10
Solved Threads: 3
 
4 Years Ago
0

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

For i = iStart to iStop

TopCat = arrDB(0, i)
If LastTopCat <> TopCat Then
Response.Write("")
TCat = arrDB(0, i) & "
"
Response.write TCat
LastTopCat = TopCat
End If

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

Next

Response.Write("")
Response.Write("")


RS.Close
Conn.Close


Torbjorn

Attachments table_6.jpg 152.69KB
TobbeK
Junior Poster
190 posts since Feb 2008
Reputation Points: 10
Solved Threads: 3
 
4 Years Ago
0

Okay here is the work around. And sorry, I left out the 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") & "")
          Response.Write("-----------------------------")
          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>")
SheSaidImaPregy
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

There is some error, i get back in short

TobbeK
Junior Poster
190 posts since Feb 2008
Reputation Points: 10
Solved Threads: 3
 
4 Years Ago
0

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.99KB
TobbeK
Junior Poster
190 posts since Feb 2008
Reputation Points: 10
Solved Threads: 3
 
4 Years Ago
0

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") & "")
          Response.Write("-----------------------------")
          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>")
SheSaidImaPregy
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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 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.

TobbeK
Junior Poster
190 posts since Feb 2008
Reputation Points: 10
Solved Threads: 3
 
4 Years Ago
Show Comments
1

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

        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>")
SheSaidImaPregy
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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

        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>")
Attachments table_8.jpg 295.66KB
TobbeK
Junior Poster
190 posts since Feb 2008
Reputation Points: 10
Solved Threads: 3
 
4 Years Ago
0

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") & "")
        LastTopCat = TopCat

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

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

SheSaidImaPregy
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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 "
SheSaidImaPregy
Veteran Poster
1,080 posts since Sep 2007
Reputation Points: 43
Solved Threads: 68
 
4 Years Ago
0

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.

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

TobbeK
Junior Poster
190 posts since Feb 2008
Reputation Points: 10
Solved Threads: 3
 

This question has already been solved

Post: Markdown Syntax: Formatting Help
You
 
 
© 2012 DaniWeb® LLC
Home | About Us | Contact Us | Terms of Service | Advertising Opportunities
RSS Feed RSS Feed