0

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

0

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.

0

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.

0

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

Attachments table_9.jpg 174.76 KB
0

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
0

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

0

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.

0

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
0

Nope

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

0

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.

0

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

0

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>
&nbsp;
</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>
&nbsp;
</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 />

0

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

0

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
0

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

0

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.

Attachments table_1.jpg 67.53 KB table_2.jpg 101.34 KB
0

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

Next

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


RS.Close
Conn.Close

Attachments table_6.jpg 161.83 KB
0

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.

0

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.

0

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.

0

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

0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.