Nested repeat in table

Can you post the results of the current code?

One Idea:  Instead of doing so many queries to the database, why not just do 3 tables of one column apiece?  You'd need only 3 calls to the db and 3 recordsets, each containing subcategories for that category.

The best way to do this is to use an array. You can then manipulate these into rows and columns as you want.. let me go dig out the code :-)

Can you post the results of the current code?

One Idea:  Instead of doing so many queries to the database, why not just do 3 tables of one column apiece?  You'd need only 3 calls to the db and 3 recordsets, each containing subcategories for that category.

Results of current code at www.solwayfeeders.com

Assuming all three have 'product' lines then you're missing both the first and second.  

Look's like you're actively changing the page. A second look gave me an ADODB error....

On second thought, I agree with Markhoy...Use an Array.   Along that line:  You could end up with 3 columns by 6 rows.  As you loop through to generage html, odd rows are Categories, even rows are Products (for linking and formatting purposes).

Good luck....

YES, otherwise it gets complicated (soory I didn't look at your SQL). The best way is to put the recordsets into arrays and then manipulate the arrays depending upon the row and column values.

In this way you can have two columns, three columns etc.

I did have some code I used on a site but I can't find it.. I think it was 4guys

Is the data normalised ie each product uses the ID of the parent category as the PID (for each product)?

I now have the following code, however it is VERY slow to load?

                  <table width="100%">
                    <%
startrw = 0
endrw = HLooper1__index
numberColumns = 3
numrows = 3
while((numrows <> 0) AND (Not StockCats.EOF))
     startrw = endrw + 1
     endrw = endrw + numberColumns
 %>
                    <tr valign="top">
                      <%
While ((startrw <= endrw) AND (Not StockCats.EOF))
%>
                      <td>
                        <a href="ProductsResultsList1.asp?Cat=<%=(StockCats.Fields.Item("NUMBER").Value)%>"><b><font face="Arial, Helvetica, sans-serif" size="2"><%=(StockCats.Fields.Item("NAME").Value)%></font></b></a>
                        <%
Dim products__catparam
products__catparam = "0"
if (StockCats("NUMBER") <> "") then products__catparam = StockCats("NUMBER")
%>
                        <%
'Begin Recordset for Nesting
set products = Server.CreateObject("ADODB.Recordset")
products.ActiveConnection = MM_sageproducts_STRING
products.Source = "SELECT DISTINCT Sum(OrderDetails.quantity) AS SumOfQuantity, STOCK.ProductID, STOCK.STOCK_CODE, STOCK.STOCK_CAT, STOCK.DESCRIPTION  FROM (STOCK INNER JOIN OrderDetails ON STOCK.STOCK_CODE = OrderDetails.productID) INNER JOIN Orders ON OrderDetails.orderID = Orders.orderID  GROUP BY STOCK.ProductID, STOCK.STOCK_CODE, STOCK.STOCK_CAT, STOCK.WEB_PUBLISH, STOCK.DESCRIPTION  HAVING (((STOCK.STOCK_CODE)<>'3501') AND ((STOCK.WEB_PUBLISH)=Yes AND STOCK.STOCK_CAT = " + Replace(products__catparam, "'", "''") + "))  ORDER BY Sum(OrderDetails.quantity) DESC , STOCK.STOCK_CODE;  "
products.CursorType = 0
products.CursorLocation = 2
products.LockType = 3
products.Open()
products_numRows = 0
%>
                        <br>
                        <font face="Arial, Helvetica, sans-serif" size="1">
                        <%
Dim Repeat1__num1Rows
Repeat1__num1Rows = 3
Dim Repeat1__Nestedindex
Repeat1__index = 0
products_numRows = products_numRows + Repeat1__num1Rows
%>
<%'Inner Repeat Loop
While ((Repeat1__num1Rows <> 0) AND (NOT products.EOF))
%>
                        [<a href="ProductsDetail1.asp?STOCK_CODE=<%=(products.Fields.Item("STOCK_CODE").Value)%>"><%=(products.Fields.Item("DESCRIPTION").Value)%></a>]
<%
  Repeat1__Nestedindex=Repeat1__Nestedindex+1
  Repeat1__num1Rows=Repeat1__num1Rows-1
  products.MoveNext()
Wend
%>
                        ...<a href="ProductsResultsList1.asp?Cat=<%=(products.Fields.Item("STOCK_CAT").Value)%>">more</a></font>
                      </td>
              <%
     startrw = startrw + 1
     StockCats.MoveNext()
     Wend
     %>      
                    </tr>
                    <%          
 numrows=numrows-1
 Wend
 %>
                  </table>

Thanks, got there in the end.

The reason it's so slow:  you're making upwards of 9 separate calls to the database.  That's a long query to be compiled and run by the db 9 separate times.  I would consider a stored procedure or view to make this run faster.  The principle is that the compiled stored procedure is already 'known' and much faster to execute than a 'brand-new' query every time.

Hi Orroland,

Please maintain your other question: https://www.experts-exchange.com/questions/20474904/Very-strange-duplication-of-data.html

Regards,
Wakie.