Classic ASP Discussion - Listing Items Under Categories

Print Page | Close Window

Listing Items Under Categories

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=9125
Printed Date: 29 March 2026 at 9:20am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Listing Items Under Categories

Posted By: Misty
Subject: Listing Items Under Categories
Date Posted: 21 January 2004 at 7:56pm

Let's say that I have the following tables in a database:

Categories: CategoryID, Category

Products: ProductID, CategoryID, ProductName

Let's say that there are 4 categories. I would like to divide the products into 4 categories on a web page.

I would like for it to look something like this:

Gifts

ProductName

Ornaments

ProductName

I know that I could accomplish this by creating 4 different recordsets on the web page. But I wonder if there is a way to do this with only one recordset. Can someone please help me with this? Does this make sense?

Replies:

Posted By: ljamal
Date Posted: 21 January 2004 at 8:46pm

Join the tables together and run one query sorted by category and product names

select category.*, product.* from product, category where
category.categoryID = product.categoryID order by category.name, product.name

Store the categoryID as you loop through the record and only display the category info when you come upon a new categoryID

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: Misty
Date Posted: 24 January 2004 at 2:51pm

I'm lost. I am not sure how to do this. I have no problems with joining the tables and sorting them. I have tried different things. Please look at the below code:

'Start sql string with Join

sqlString = "select * from Category, Products"

sqlString = sqlString & " where Category.CategoryID=Products.CategoryID order by Category, ProductName"

'Create the recordset

set rs = Server.CreateObject("ADODB.Recordset")

rs.Open sqlString, connectionString, adOpenDynamic, adLockOptimistic

I want the Category to show up here. But I have been unsuccessful so far. I would like for the category to display only one time for a section of products that are under this category. I need some help with the code.

<TR>

<TH>ProductName</TH>

<TH>Description </TH>

</TR>

'Set the current record pointer to the first record of the recordset

rs.MoveFirst

'Loop through all the records in the recordset

while not rs.EOF

'Get values

Category = rs("Category") & ""

ProductName = rs("ProductName") & ""

Description = rs("Description") & ""

Posted By: ljamal
Date Posted: 24 January 2004 at 3:20pm

Try this. I didn't run the script for error checking but it should point you in the right direction

<%
     'Start sql string with Join
     sqlString = "select * from Category, Products "&_
                  "where Category.CategoryID=Products.CategoryID"&_
                  " order by Category, ProductName"

     'Create the recordset
     set rs = Server.CreateObject("ADODB.Recordset")
     rs.Open sqlString, connectionString, adOpenDynamic, adLockOptimistic

     intCatID = 0

     Do while not rs.EOF
             ' Check if Category Name has alreayd been displayed
             if intCatID<>Clng(rs("CategoryID")) then
                  intCatID = Cng(rs("CategoryID"))

                   'Close table if a table is open
                      if intCatID > 0 then
                              Response.Write "</table>"
                      end if
                   ' Display Category Name and Open Table
                  Response.Write rs("Category")
                  Response.Write ""&vbcrlf&_
                                     " <table borde=1>"&vbcrlf&_
                                     " <tr><th>Product Name</th>"&_
                                     " <th>Description</th></tr>"
             end if

                   ' Write Product information
                  Response.Write "<tr><td>"&rs("ProductName")&"</td>"&_
                                     " <td>"&rs("Description")&"</td></tr>"&vbcrl f

             rs.MoveNext
     Loop

     'Close Open table
             Response.Write </table">

     'Close the recordset
     rs.Close
     Set rs = Nothing
%>

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: Misty
Date Posted: 24 January 2004 at 10:42pm

Thank you! I have it working now.