Nested loop - Page 2

Question

SheSaidImaPregy 28 Veteran Poster

16 Years Ago

Last chance. It should work also, just like the rest right? lol

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

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

    Do Until i = 3
      If Not RS.EOF Then
        TopCat = RS("topcategory")
        MiddelCat = "RS("middlecategory")

        Response.Write("<td width='190'>")
        Response.Write(RS("topcategory") & "<br>")
        Response.Write("-----------------------------<br>")
        LastTopCat = TopCat
          Do While TopCat = LastTopCat
            Response.Write(MiddleCat)
            RS.MoveNext
            TopCat = RS("topcategory")
            MiddelCat = "RS("middlecategory")
          Loop
        Response.write("</td>")
        i = i + 1

      End If
    Loop

    Response.Write("<tr>")
  Loop

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

Hope I helped.

SheSaidImaPregy 28 Veteran Poster

16 Years Ago

With your original SQL, try the below code:

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

	Do While Not RS.EOF
		Response.Write("<tr>")
		i = 0
		TopCat = RS("topcategory")
		MiddleCat = RS("middlecategory")

		Do While i <> 3
			If Not RS.EOF Then
				If TopCat <> LastTopCat Then
					Response.Write ("<td width=""190"">")
					Response.Write(RS("topcategory") & "<br />")
					Response.Write("-----------------------------")
					LastTopCat = TopCat
					i = i + 1
					
					Do While LastTopCat = TopCat
						Response.Write("<br />" & MiddleCat)
						RS.MoveNext
						If Not RS.EOF Then
							TopCat = RS("topcategory")
							MiddelCat = RS("middlecategory")
						Else
							Exit Do
						End If
					Loop

					Response.Write("</td>")
				End If
			Else
				Response.Write("<td width=""190""></td>")
				i = i + 1
			End If
		Loop

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

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

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

TobbeK 0 Junior Poster · Answer 1 · 2008-02-15T03:30:55+00:00

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.

There you have the zipped test db

SheSaidImaPregy 28 Veteran Poster · Answer 2 · 2008-02-15T03:35:18+00:00

I won't be able to look at it for about 2-3 hours. I don't have access on this machine. Well wait.. let me see. I'll get back to you.

TobbeK 0 Junior Poster · Answer 3 · 2008-02-15T03:35:47+00:00

TobbeK 0 Junior Poster

16 Years Ago

Here you have the zipped db anyway

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with zip files.

labdb.zip (31.35 KB)

SheSaidImaPregy 28 Veteran Poster · Answer 4 · 2008-02-15T04:12:30+00:00

hey, want to know why it errors? lol

It is never finding an end. There's a mistake in the code, and it's where it says "cols"

Remove cols, and have just "3".

For i=1 to 3

And it wont hang. Enjoy.

Oh, also remove response.buffer, and the response.flush.

TobbeK 0 Junior Poster · Answer 5 · 2008-02-15T04:23:10+00:00

Nope :-)

Check the attach

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,MC.middlecategory ASC "
RS.Open SQL,Conn

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

Do Until RS.EOF

Response.Write("<tr>")

For i = 1 To 3
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

SheSaidImaPregy 28 Veteran Poster · Answer 6 · 2008-02-15T04:31:03+00:00

lmao. Doing 4 different tasks is a bit much for me I guess. Of course that won't work lol.

Try this:

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

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

        Do Until LastTopCat <> TopCat
          TopCat = RS("topcategory")
          MiddleCat = RS("middlecategory")

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

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

          RS.MoveNext
        Loop

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

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

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

SheSaidImaPregy 28 Veteran Poster · Answer 7 · 2008-02-15T04:37:33+00:00

That won't work either. Hold on, let me put everything else aside for a minute.

TobbeK 0 Junior Poster · Answer 8 · 2008-02-15T04:39:31+00:00

ADODB.Field (0x800A0BCD)
Antingen är BOF eller EOF satt till True, eller så har aktuell post raderats. Den begärda åtgärden kräver en aktuell post.
/NYSITE/category_window.asp, line 27

And line 27 is:
TopCat = RS("topcategory")

SheSaidImaPregy 28 Veteran Poster · Answer 9 · 2008-02-15T04:40:40+00:00

Ok it will, but you have to add this line after
RS.MoveNext

TopCat = RS("topcategory")

You need a second one there in order to reset the topcat. Keep both. Just add that line after RS.MoveNext and it should work.

SheSaidImaPregy 28 Veteran Poster · Answer 10 · 2008-02-15T04:42:33+00:00

Oh yeah forgot to check for EOF on that portion. Here you go:

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

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

        Do Until LastTopCat <> TopCat
          If Not Rs.EOF Then
            TopCat = RS("topcategory")
            MiddleCat = RS("middlecategory")

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

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

            RS.MoveNext

            If Not RS.EOF Then
              TopCat = RS("topcategory")
            End If
          End If
        Loop

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

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

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

SheSaidImaPregy 28 Veteran Poster · Answer 11 · 2008-02-15T04:44:15+00:00

SheSaidImaPregy 28 Veteran Poster

16 Years Ago

Yup that should be it.

TobbeK 0 Junior Poster · Answer 12 · 2008-02-15T04:45:50+00:00

Nope

Feltyp:
Response-objekt, ASP 0104 (0x80070057)
Operationen är inte tillåten

SheSaidImaPregy 28 Veteran Poster · Answer 13 · 2008-02-15T05:06:45+00:00

Change the DoWhile ending from Loop to End While. I am working on an alternate real quick, then I gotta go as its VDay.

TobbeK 0 Junior Poster · Answer 14 · 2008-02-15T05:17:34+00:00

Yepp that one works as well

only a minor error messege :-)

ADODB.Field (0x800A0BCD)
Antingen är BOF eller EOF satt till True, eller så har aktuell post raderats. Den begärda åtgärden kräver en aktuell post.
/NYSITE/category_window.asp, line 33

TobbeK 0 Junior Poster · Answer 15 · 2008-02-15T05:21:06+00:00

Check this out... got it from a guy from another forum.

sSQL = "SELECT * FROM tbmiddlecategory;"
Set rsTemp = Server.CreateObject("ADODB.RecordSet")
rsTemp.Open sSQL, Connection, adoConst, adoConst

aMiddleCategories = rsTemp.GetRows()
rsTemp.Close
Set rsTemp = Nothing

sSQL = "SELECT * FROM topcategory WHERE Condition = '" & sCondition & "';"

Set oRs = Server.CreateObject("ADODB.RecordSet")

oRs.Open sSQL, Connection, adoConst, adoConst
%>

<table border="0" cellpadding="0" cellspacing="0" class="indent" width="100%">
<tr>
<td>
Some Header<br />

<table border="0" cellpadding="2" cellspacing="1" width="100%">
<tr>
<td width="2%">
<strong>#</strong>
</td>
<td>
<strong>Top Category</strong>
</td>
</tr>
<tr>
<td colspan="100%">
<hr />
</td>
</tr>
<%
If oRs.EOF = True Then
%>
<tr>
<td colspan="100%">
NO Top Categories!
</td>
</tr>

<%
End If
Do Until oRs.EOF
If IsNull(iRecord) Then
iRecord = 1
Else
iRecord = iRecord + 1
End If
%>
<tr>
<td>
<strong><%=iRecord%>.</strong>
</td>
<td class="smallprint">
<strong><% =oRs("topcategory") %></strong>
</td>
</tr>
<%
'here's where the magic happens. Now, of course this will create a new <TR> for each sub-category which pertains to the master/primary category - but it formats better. You can play around with the HTML of course if it's not what you're aiming for.

If IsArray(aMiddleCategories) Then
For i = 0 To UBound(aMiddleCategories, x)

'(x = the number of fields your recordset returned. If you return SELECT field1, field2, field3 FROM table - then x '= 3 because you returned 3 fields.)

If aMiddleCategories(x,i) = oRS("ID") Then

'(x=the position of the topcategoryID field returned to the array in your recordset. In the example above, Field1 'would be 0, field2 would be 1, field3 would be 2, etc etc)
'oRS("ID") is the relational field in the top category table that relates to topcategoryID in the middle category 'table
%>
<tr>
<td>
 
</td>
<td colspan="100%" style="padding-left:30px;">
<strong><% =aMiddleCategories(x,i)%></strong> <strong></strong>
</td>
</tr>
<%
End If
Next
ElseIf aMiddleCategories <> "" Then
%>
<tr>
<td>
 
</td>
<td colspan="100%" style="padding-left:30px;">
<strong><% =aMiddleCategories %></strong> <strong></strong>
</td>
</tr>
<%
End If
oRS.MoveNext
Loop
oRs.Close
Set oRs = Nothing
%>
<tr>
<td colspan="100%">
<hr />
</td>
</tr>
</table>
</td>
</tr>
</table><br /><br />

TobbeK 0 Junior Poster · Answer 16 · 2008-02-15T21:42:42+00:00

I have rewritten my code a little.

There MUST be some way to run an additional loop as I have put in to this one as "MARKED". This code works, but loops only out the first post from middlecategory under the first post of topcategory. All of topcategory is received, but no more middles.

It was necessary to separate the topcat and middlecat SQL queries to avoid repeating. And this works well.

I CAN ATTACH A SCREENSHOT IF NEEDED

The database contain 3 tables:
1. tbtopcategory
2. tbconnectcategory ' contains related foreign keys from tbtopcat and tbmiddlecat
3. tbmiddlecategory

If I describe this in this way:
(AS LONG AS ) the topcategory is same topcategory then
middlecategory RS1.MoveNext Loop until (AS LONG AS ) the topcategory is same topcategory and then
topcategory RS.movenext Loop until the next topcategory and so on

SQL = "SELECT DISTINCT TC.topcategory,MC.middlecategory,CC.middlecategorylink AS MCLink "&_
"FROM tbtopcategory TC,tbmiddlecategory MC,tbconnectcategory CC "&_
"WHERE TC.topcategoryID = CC.topcategorylink "&_
"AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
"GROUP BY TC.topcategory "
set RS = Server.CreateObject("ADODB.recordset")
RS.Open SQL,Conn

SQL1 = "SELECT middlecategory " &_
"FROM tbmiddlecategory WHERE tbmiddlecategoryID = '" & RS("MCLink") & "'"
set RS1 = Server.CreateObject("ADODB.recordset")
RS1.Open SQL1,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

' -------------------START MIDDLECAT LOOP
Do Until RS1.EOF
Response.Write(RS1("middlecategory"))
RS1.MoveNext
Loop
' -------------------END MIDDLECAT LOOP

RS.MoveNext

End If
Response.Write("</td>")
Next
Response.Write("</tr>")
Loop
End If
Response.Write("</table>")
Conn.Close

best
regards
Torbjorn

TobbeK 0 Junior Poster · Answer 17 · 2008-02-15T22:48:14+00:00

Hi there
Fun that you are back!

Nope that one didn't work. Repeating middles.

If there is some way to run middlecat loops for each the of topcat loops.

<td>
Topcat
middlecat,middlecat,middlecat and so on ...
</td>
Topcat moveNext
<td>
Topcat
middlecat,middlecat,middlecat and so on ...
</td>
If cols = 3 Then
</tr>
If Not RS.EOF Then
<tr>
<td> And so on

SheSaidImaPregy 28 Veteran Poster · Answer 18 · 2008-02-15T22:51:53+00:00

Show me the screenshot, so I am not guessing in the dark please :)

TobbeK 0 Junior Poster · Answer 19 · 2008-02-15T23:07:11+00:00

OKIDOKI

ATTACHMENT

table_1
This is my code
Only one middlecat i viewed under the first topcat and the rest is empty

table_2
This is your code
The middlecat (Bildelar & Biltillbehör) is repeated out of bound into next topcategory cell.
The first one is correct. The number of repeated middlecategory under this specific topcategory is also correct, but only the first is showed up.

TobbeK 0 Junior Poster · Answer 20 · 2008-02-15T23:24:30+00:00

Here is another approach

I have used RS.Getrows instead and put everything into an array

CHECK the attachment,

As you can see all of the topcategeries and middlecategories is in their cells properly.
The problem with this code is another, I cant break it into 3 columns.

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

RS.Close
Conn.Close

SheSaidImaPregy 28 Veteran Poster · Answer 21 · 2008-02-15T23:29:05+00:00

work with me here.. the code I gave you had "MiddleCat" and "MiddelCat", which is why MiddleCat is repeating, since it is never being reset.

Just make that change, MiddelCat --> MiddleCat

Same with last nights code, many typo's lol.

TobbeK 0 Junior Poster · Answer 22 · 2008-02-15T23:37:03+00:00

Yepp you're right, changed the middels to middle

Still it it is only the first of each middle that shows up. Take a look at the attached screen in my last post to see what I mean by that. In top above the dotted line is the topcat, below is the middles. There is another code in that post.

SheSaidImaPregy 28 Veteran Poster · Answer 23 · 2008-02-15T23:37:59+00:00

How many middles are there for each one. You have 7 cats, look in your DB for the middles in each one so I have the number. Then I can trully know what I am looking at.

TobbeK 0 Junior Poster · Answer 24 · 2008-02-15T23:41:31+00:00

Each middlecat is separated by a (,) below the dotted line in the attached table 3 posts ago.

The number is of middlecats is different for each topcategory

SheSaidImaPregy 28 Veteran Poster · Answer 25 · 2008-02-15T23:48:35+00:00

Ok, try this:

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

	Do While Not RS.EOF
		Response.Write("<tr>")
		i = 0
		TopCat = RS("topcategory")
		MiddleCat = RS("middlecategory")

		Do While i <> 3
			If Not RS.EOF Then
				If Not LastTopCat = TopCat Then
					Response.Write ("<td width=""190"">")
					Response.Write(RS("topcategory") & "<br />")
					Response.Write("-----------------------------")
					LastTopCat = TopCat
				End If
					
				Response.Write("<br />" & MiddleCat)

				RS.MoveNext

				If Not RS.EOF Then
					TopCat = RS("topcategory")
					MiddleCat = RS("middlecategory")
				End If

				If Not LastTopCat = TopCat or RS.EOF Then
					Response.Write("</td>")
					i = i + 1
				End If
			Else
				Response.Write("<td width=""190""></td>")
				i = i + 1
			End If
		Loop

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

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

What was failing in the previous code was the Do While TopCat = LastTopCat

It was only seeing that hit once.

EDITED: Had to move the i = i + 1 down

TobbeK 0 Junior Poster · Answer 26 · 2008-02-15T23:51:36+00:00

TobbeK 0 Junior Poster

16 Years Ago

Same :- ) not bad but same

SheSaidImaPregy 28 Veteran Poster · Answer 27 · 2008-02-15T23:52:23+00:00

I edited it, it was mistake of placement of I, retry it with new code.

TobbeK 0 Junior Poster · Answer 28 · 2008-02-15T23:56:24+00:00

TobbeK 0 Junior Poster

16 Years Ago

Same same but good looking.