0

Let's loose all the stupid loops.

Oh please tell me you "Dim LastTopCat" at the top of the page right?

Try this:

If Not RS.EOF Then
	Response.Write("<table width=""570"" border=""1"">")
	TopCat = RS("topcategory")
	MiddleCat = RS("middlecategory")
	LastTopCat = ""
	i = 3

	Do While Not RS.EOF
		If i = 3 Then
			Response.Write("<tr>")
			i = 0
		End If

		If TopCat <> LastTopCat Then
			Response.Write ("<td width=""190"">")
			Response.Write(TopCat & "<br />")
			Response.Write("-----------------------------")
			LastTopCat = TopCat
		End If

		If TopCat = LastTopCat Then
			Response.Write(MiddleCat)
		End If

		RS.MoveNext

		If TopCat <> RS("topcategory") Then
			TopCat = RS("topcategory")
		End If

		MiddleCat = RS("middlecategory")

		If TopCat <> LastTopCat Then
			Resposne.Write("</td>")
			i = i + 1
		End If

		If i = 3 Then
			Response.Write("</tr>")
		End If
	Loop

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

This is what it hopefully will looks like

This is faked table

Attachments table_10.jpg 171.54 KB
0

There is two recordsets available, one for topcats and one for middles

One by each SQL query
RS and RS1

0

wait, you didn't declare the variable before? Run the last code where you said it looks good.

The reason why it kept skipping over is that it didn't keep LastTopCat in memory, therefore when it compared, it compared TopCat to nothing, hence failing.

Dim LastTopCat at the top of the page and try the 2nd to last code I gave you.

0

I don't know then. It should all work just fine, especially with the 2nd to last code I gave you. I mean, it looped through all the top cats, caught the middles, then went on. Now nothing at all?

0

The only wrong I can find was Resposne.Write and changed that to Response.Write

0

I reloaded it a couple of times and this is the output

And row 47 is this one
If TopCat <> RS("topcategory") Then

Attachments table_11.jpg 112.14 KB
0

This one:

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
0

That one (on page 6) is also dimmed and that one is working in the matter of 1 topcat and 1 middlecat

0

Yep, I can agree with that

The reason why I split the SQL query into 2 queries was the repeating issue. You will only get the first middlecat along with each topcategory within the query (SQL). The (SQL1) combined with (SQL) was meant as a workaround to pick out every middles connected to each topcat without the need of repeating topcats or middlecats.

Maybe the solution is to optimize the SQL !? I don't know?

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

0

Oh brother, try this code and then I will tell you what's wrong.

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 AND Len(TopCat) > 3 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 (Len(TopCat) > 3 AND TopCat <> LastTopCat) 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
0

Your databases aren't connected correctly. Your topcategory and middlecategory should be connected like this:

TopCat
- TopCatID
- TopCatName

MiddleCat
- MiddleCatID
- TopCatID
- MiddleCatName

They aren't, and because of that you are pulling empty values.

When you pull the TopCat Name, you prolly are able to pull 1 value with it.

You need a database restructure.

0

Yes it's what your database is pulling, not how the code handles it. You need a database redesign/restructure to better relate the data.

If it requires you for two queries to grab the information, then the information isn't related correctly.

If you want to see what I mean, run this code:

If Not RS.EOF Then
  Do Until RS.EOF
    Response.write(RS("topcategory") & " ~ " & RS("middlecategory") & "<br />")
    RS.MoveNext
  Loop
End If
0

The connecting table (tbconnectcategory) which holding the foreign keys looks like this:

TbConnectCategory
- topcategorylink <> tbtopcategoryID
- middlecategorylink <> tbmiddlecategoryID

That should be enough as long as keys are unique and the query is intelligent enought (he he), depends who the writer is. Maybe I should rewrite my SQLs

0

MAN you are absoluteley right.

As talked about before (GROUP BY) cluse was set to top category. I deleted that one and VOILA

Hang on


CHECK THE ATTACH - IT IS NOT A FAKE IT'S FOR REAL

Attachments table_12.jpg 165.21 KB
0

Oh okay that's how you have it set. Yeah that is fine. Not highly recommended, but it works. Okay then try this query:

SQL = "SELECT 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 "

You have to lost the DISTINCT, otherwise you will only grab One Top Category, which means you only grab one MiddleCategory. Drop the destinct, and no need for the MCLink.

And now your thing should work.

0

NO NO, you have to keep GROUP BY clause in there, if you don't, your records won't go in the right places, and you'll have duplicate columns!

You just need to lose the DISTINCT. DISTINCT grabs only the unique ones. So if you have 10 TopCats named NameMyTopCat, it will only pull 1. Since it only pulls 1, it will only pull one MiddleCat. That's where you failed.

0

Yes this was the right one, added middlecat as well to GROUP BY and it is working

SQL = "SELECT 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, MC.middlecategory ASC"
set RS = Server.CreateObject("ADODB.recordset")

1

You probably don't even need middlecategory. If you wish, you should do it this way:

SQL = "SELECT TC.topcategory, DISTINCT(MC.middlecategory) "&_
"FROM tbtopcategory TC, tbmiddlecategory MC, tbconnectcategory CC "&_
"WHERE TC.topcategoryID = CC.topcategorylink "&_
"AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
"GROUP BY TC.topcategory"

This way it will grab only the unique sub names (which you don't want duplicates of anyway). No need for group by, as when you have uniques, grouping 1, 2, 3 will turn out as 1, 2, 3. It's pointless.

Glad to see its working. Show me? :)

Votes + Comments
One of a kind
0

I don't how to thank you enough for the most valued and professional help any can ever get on any forum.

0

This is the output (before I prints it out and hang it in some golden frame)


You probably don't even need middlecategory. If you wish, you should do it this way:

SQL = "SELECT TC.topcategory, DISTINCT(MC.middlecategory) "&_
"FROM tbtopcategory TC, tbmiddlecategory MC, tbconnectcategory CC "&_
"WHERE TC.topcategoryID = CC.topcategorylink "&_
"AND MC.tbmiddlecategoryID = CC.middlecategorylink " &_
"GROUP BY TC.topcategory"

This way it will grab only the unique sub names (which you don't want duplicates of anyway). No need for group by, as when you have uniques, grouping 1, 2, 3 will turn out as 1, 2, 3. It's pointless.

Glad to see its working. Show me? :)

Attachments table_12.jpg 165.21 KB
0

Dont hesitate to contact me if there is anything I can do for help. I will be here now and then.

Again thanks and many greetings from Stockholm

sincerely
Torbjorn

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.