Nested Repeat Region

Report · Oct 21, 2007

Dreamweaver CS3
ASP / VBScript

Hi,

I'm trying to create a nested repeat region to display a list of categories
and, in brackets afterwards, a count of the number of sub-categories
contained in it E.g.

Cats (12)
Dogs (43)
Fish (92)
Etc

I have managed to create my repeat region ok because as soon as I drop a
field in from the recordset, it loops through them all and displays them
correctly. However, when I try and simply write out the total number of
records found, it doesn't display anything.

Recordset 1 is a straighforward select from the table. Recordset 2 adds a
WHERE clause usinga variable based on the 1st recordset. Here is the code
i'm using for the 2 recordsets:

Recordset 1 :

<%
Dim Level1
Dim Level1_cmd
Dim Level1_numRows

Set Level1_cmd = Server.CreateObject ("ADODB.Command")
Level1_cmd.ActiveConnection = MM_SemiDirectory_STRING
Level1_cmd.CommandText = "SELECT * FROM TBL_Level1 ORDER BY [Description]
ASC"
Level1_cmd.Prepared = true

Set Level1 = Level1_cmd.Execute
Level1_numRows = 0
%>

Recordset 2 :

<%
Dim Level2__varLink
Level2__varLink = "1"
If (level1.fields.item("Level1_ID").value <> "") Then
Level2__varLink = level1.fields.item("Level1_ID").value
End If
%>
<%
Dim Level2
Dim Level2_cmd
Dim Level2_numRows

Set Level2_cmd = Server.CreateObject ("ADODB.Command")
Level2_cmd.ActiveConnection = MM_SemiDirectory_STRING
Level2_cmd.CommandText = "SELECT * FROM TBL_Level2 WHERE Level1_ID = ? ORDER
BY Level1_ID ASC"
Level2_cmd.Prepared = true
Level2_cmd.Parameters.Append Level2_cmd.CreateParameter("param1", 5, 1, -1,
Level2__varLink) ' adDouble

Set Level2 = Level2_cmd.Execute
Level2_numRows = 0
%>

Now for the repeated code :

<%
While ((Repeat1__numRows <> 0) AND (NOT Level1.EOF))
%>
<li><a
href="category.asp?level1=<%=(Level1.Fields.Item("Level1_ID").Value)%>"><%=(Level1.Fields.Item("Description").Value)%>
(<%=(Level2_total)%>)
</li></a>

<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Level1.MoveNext()
Wend
%>

Can anyone tell me what I need to do to this code to make it display the
number of sub-categories?

Thanks!!!

Report · Oct 21, 2007

Ok,

So I closed the <li> code before the <a> code. D'oh!

Anyway, it returns a number now........the problem now is that it returns
the same value for each parent repeat!!!

"Mintyman" <test@test.com> wrote in message
news:ffg4bk$7p3$1@forums.macromedia.com...
> Dreamweaver CS3
> ASP / VBScript
>
> Hi,
>
> I'm trying to create a nested repeat region to display a list of
> categories and, in brackets afterwards, a count of the number of
> sub-categories contained in it E.g.
>
> Cats (12)
> Dogs (43)
> Fish (92)
> Etc
>
> I have managed to create my repeat region ok because as soon as I drop a
> field in from the recordset, it loops through them all and displays them
> correctly. However, when I try and simply write out the total number of
> records found, it doesn't display anything.
>
> Recordset 1 is a straighforward select from the table. Recordset 2 adds a
> WHERE clause usinga variable based on the 1st recordset. Here is the code
> i'm using for the 2 recordsets:
>
> Recordset 1 :
>
> <%
> Dim Level1
> Dim Level1_cmd
> Dim Level1_numRows
>
> Set Level1_cmd = Server.CreateObject ("ADODB.Command")
> Level1_cmd.ActiveConnection = MM_SemiDirectory_STRING
> Level1_cmd.CommandText = "SELECT * FROM TBL_Level1 ORDER BY [Description]
> ASC"
> Level1_cmd.Prepared = true
>
> Set Level1 = Level1_cmd.Execute
> Level1_numRows = 0
> %>
>
> Recordset 2 :
>
> <%
> Dim Level2__varLink
> Level2__varLink = "1"
> If (level1.fields.item("Level1_ID").value <> "") Then
> Level2__varLink = level1.fields.item("Level1_ID").value
> End If
> %>
> <%
> Dim Level2
> Dim Level2_cmd
> Dim Level2_numRows
>
> Set Level2_cmd = Server.CreateObject ("ADODB.Command")
> Level2_cmd.ActiveConnection = MM_SemiDirectory_STRING
> Level2_cmd.CommandText = "SELECT * FROM TBL_Level2 WHERE Level1_ID = ?
> ORDER BY Level1_ID ASC"
> Level2_cmd.Prepared = true
> Level2_cmd.Parameters.Append Level2_cmd.CreateParameter("param1", 5,
> 1, -1, Level2__varLink) ' adDouble
>
> Set Level2 = Level2_cmd.Execute
> Level2_numRows = 0
> %>
>
>
> Now for the repeated code :
>
>
> <%
> While ((Repeat1__numRows <> 0) AND (NOT Level1.EOF))
> %>
> <li><a
> href="category.asp?level1=<%=(Level1.Fields.Item("Level1_ID").Value)%>"><%=(Level1.Fields.Item("Description").Value)%>
> (<%=(Level2_total)%>)
> </li></a>
>
>
> <%
> Repeat1__index=Repeat1__index+1
> Repeat1__numRows=Repeat1__numRows-1
> Level1.MoveNext()
> Wend
> %>
>
> Can anyone tell me what I need to do to this code to make it display the
> number of sub-categories?
>
> Thanks!!!
>
>
>

Report · Oct 21, 2007

Ok folks,

I found a way to get what I want. It's not pretty as I had to make an extra
query in the database (that select of the count of all child table results
for each parent category) and am pulling from that but it works and that's
the main thing. I'm sure there is a more effective way of doing it but
that's life!

I'm still interested in knowing the 'proper' way (if there is such a thing)
so if you have the time to show me, i'm all ears!

Cheers!

"Mintyman" <test@test.com> wrote in message
news:ffg507$8k7$1@forums.macromedia.com...
> Ok,
>
> So I closed the <li> code before the <a> code. D'oh!
>
> Anyway, it returns a number now........the problem now is that it returns
> the same value for each parent repeat!!!
>
> "Mintyman" <test@test.com> wrote in message
> news:ffg4bk$7p3$1@forums.macromedia.com...
>> Dreamweaver CS3
>> ASP / VBScript
>>
>> Hi,
>>
>> I'm trying to create a nested repeat region to display a list of
>> categories and, in brackets afterwards, a count of the number of
>> sub-categories contained in it E.g.
>>
>> Cats (12)
>> Dogs (43)
>> Fish (92)
>> Etc
>>
>> I have managed to create my repeat region ok because as soon as I drop a
>> field in from the recordset, it loops through them all and displays them
>> correctly. However, when I try and simply write out the total number of
>> records found, it doesn't display anything.
>>
>> Recordset 1 is a straighforward select from the table. Recordset 2 adds a
>> WHERE clause usinga variable based on the 1st recordset. Here is the
>> code i'm using for the 2 recordsets:
>>
>> Recordset 1 :
>>
>> <%
>> Dim Level1
>> Dim Level1_cmd
>> Dim Level1_numRows
>>
>> Set Level1_cmd = Server.CreateObject ("ADODB.Command")
>> Level1_cmd.ActiveConnection = MM_SemiDirectory_STRING
>> Level1_cmd.CommandText = "SELECT * FROM TBL_Level1 ORDER BY [Description]
>> ASC"
>> Level1_cmd.Prepared = true
>>
>> Set Level1 = Level1_cmd.Execute
>> Level1_numRows = 0
>> %>
>>
>> Recordset 2 :
>>
>> <%
>> Dim Level2__varLink
>> Level2__varLink = "1"
>> If (level1.fields.item("Level1_ID").value <> "") Then
>> Level2__varLink = level1.fields.item("Level1_ID").value
>> End If
>> %>
>> <%
>> Dim Level2
>> Dim Level2_cmd
>> Dim Level2_numRows
>>
>> Set Level2_cmd = Server.CreateObject ("ADODB.Command")
>> Level2_cmd.ActiveConnection = MM_SemiDirectory_STRING
>> Level2_cmd.CommandText = "SELECT * FROM TBL_Level2 WHERE Level1_ID = ?
>> ORDER BY Level1_ID ASC"
>> Level2_cmd.Prepared = true
>> Level2_cmd.Parameters.Append Level2_cmd.CreateParameter("param1", 5,
>> 1, -1, Level2__varLink) ' adDouble
>>
>> Set Level2 = Level2_cmd.Execute
>> Level2_numRows = 0
>> %>
>>
>>
>> Now for the repeated code :
>>
>>
>> <%
>> While ((Repeat1__numRows <> 0) AND (NOT Level1.EOF))
>> %>
>> <li><a
>> href="category.asp?level1=<%=(Level1.Fields.Item("Level1_ID").Value)%>"><%=(Level1.Fields.Item("Description").Value)%>
>> (<%=(Level2_total)%>)
>> </li></a>
>>
>>
>> <%
>> Repeat1__index=Repeat1__index+1
>> Repeat1__numRows=Repeat1__numRows-1
>> Level1.MoveNext()
>> Wend
>> %>
>>
>> Can anyone tell me what I need to do to this code to make it display the
>> number of sub-categories?
>>
>> Thanks!!!
>>
>>
>>
>
>

Report · Oct 21, 2007

On 21 Oct 2007 in macromedia.dreamweaver.appdev, Mintyman wrote:

> I'm trying to create a nested repeat region to display a list of
> categories and, in brackets afterwards, a count of the number of
> sub-categories contained in it E.g.
>
> Cats (12)
> Dogs (43)
> Fish (92)
> Etc
>
> I have managed to create my repeat region ok because as soon as I
> drop a field in from the recordset, it loops through them all and
> displays them correctly. However, when I try and simply write out
> the total number of records found, it doesn't display anything.

If you don't want the sub-elements in the categories, just the category
name and count, do something like this in SQL:

SELECT category, COUNT(category) AS myCount
FROM myTable
GROUP BY category
ORDER BY category;

If what you want something more like:

Cats
Calico
Maine Coon
Teal Point
Total Cats: 3
Dogs
Beagle
Boxer
Bulldog
Golden Retriever
Total Dogs: 4

Look for a 'nested repeat region' extension - I think Tom Muck has such -
and then implement a counter within the region.

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/contact.php

Report · Oct 22, 2007

Hi Joe,

I effectively used your first suggestion. I used a SELECT query similar to
your and added a WHERE clause that linked the parent and child recordset. I
then placed the output code in the repeat region and, hey presto, it worked!

Thanks for the Tom Much suggestion. If I need something like this in the
future, i'll know where to look.

Minty

"Joe Makowiec" <makowiec@invalid.invalid> wrote in message
news:Xns99D0C5D997AADmakowiecatnycapdotrE@216.104.212.96...
> On 21 Oct 2007 in macromedia.dreamweaver.appdev, Mintyman wrote:
>
>> I'm trying to create a nested repeat region to display a list of
>> categories and, in brackets afterwards, a count of the number of
>> sub-categories contained in it E.g.
>>
>> Cats (12)
>> Dogs (43)
>> Fish (92)
>> Etc
>>
>> I have managed to create my repeat region ok because as soon as I
>> drop a field in from the recordset, it loops through them all and
>> displays them correctly. However, when I try and simply write out
>> the total number of records found, it doesn't display anything.
>
> If you don't want the sub-elements in the categories, just the category
> name and count, do something like this in SQL:
>
> SELECT category, COUNT(category) AS myCount
> FROM myTable
> GROUP BY category
> ORDER BY category;
>
> If what you want something more like:
>
> Cats
> Calico
> Maine Coon
> Teal Point
> Total Cats: 3
> Dogs
> Beagle
> Boxer
> Bulldog
> Golden Retriever
> Total Dogs: 4
>
> Look for a 'nested repeat region' extension - I think Tom Muck has such -
> and then implement a counter within the region.
>
> --
> Joe Makowiec
> http://makowiec.net/
> Email: http://makowiec.net/contact.php

Report · Oct 22, 2007

On 22 Oct 2007 in macromedia.dreamweaver.appdev, Mintyman wrote:

> I effectively used your first suggestion. I used a SELECT query
> similar to your and added a WHERE clause that linked the parent and
> child recordset. I then placed the output code in the repeat region
> and, hey presto, it worked!

Glad it worked. It's frequently easier to do stuff in SQL and have the
result set look the way you want it, than to try and do the manipulation
after the fact in the server language.

--
Joe Makowiec
http://makowiec.net/
Email: http://makowiec.net/contact.php