Classic ASP Discussion - SQL problem I think

Print Page | Close Window

SQL problem I think

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=8667
Printed Date: 01 April 2026 at 1:12am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: SQL problem I think

Posted By: ngaisteve1
Subject: SQL problem I think
Date Posted: 08 January 2004 at 2:54am

The problem I am facing is I can only see one product category only. It should be a product category, a list of companies inside it, then another product category, another list of companies inside it. Now is one product category, then all the list of companies.

All help is appreciated.


    <%      & nbsp;  
   sql = "SELECT * FROM productcat ORDER BY productcat"
   rs.CursorLocation = 3   
   rs.open sql, conn
      
   if not rs.eof then
    do while not rs.eof
    response.write "<tr><td>Product Category: " & rs("productcat") & "</td></tr>"
        
    set rs2 = Server.CreateObject("ADODB.recordset")
    set sql2 = Server.CreateObject("ADODB.command")    
         
    sql2 = "Select * FROM customer as c, cust_productcat as cpc, ProductCat as pc "
    sql2 = sql2 & "WHERE cpc.ProductCatID = " & rs("productcatid") & " "
    sql2 = sql2 & "AND cpc.ProductCatID = pc.ProductCatID "
    sql2 = sql2 & "AND cpc.CompanyID = c.CustID  "           ;           ;           ;           ;           ;           ;           ;        
    sql2 = sql2 & "ORDER BY  c.companyname"       &nbs p; 
    rs2.CursorLocation = 3
    rs2.open sql2,  conn         &n bsp;    
                                                            
    if not rs2.eof then
     Do While Not  rs2.EOF          ;        
      if rs2("listing") = "Yes" then
       response.write "<tr><td><br>" & rs2("companyname") & " *</td></tr>"
      else
       response.write "<tr><td><br>" & rs2("companyname") & "</td></tr>"
      end if     
      rs2.MOVENEXT
     LOOP
    END IF
    rs2.close
    set rs2 = nothing
   rs.movenext
   loop
   end if         
   %>

Replies:

Posted By: aalavar
Date Posted: 08 January 2004 at 8:10am

If you get rid of the loop that goes through all the companies, does it display all of your product categories? Or are you saying that it displays a product category, all the companies, then another product category, then all of the companies again?

Posted By: michael
Date Posted: 08 January 2004 at 9:35am

I have found nested recordset to be very unreliable, they do work but often cause problems. If you have hierarchical data and would like to display it that way have a look at ADO Data Shaping which will take care of it. There are a bunch of tutorials if you do a search on google or so.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: MorningZ
Date Posted: 09 January 2004 at 11:05am

why do you have this line?

set sql2 = Server.CreateObject("ADODB.command")

after that line, "sql2" is a string containing a SQL statement

-------------
Contribute to the working anarchy we fondly call the Internet

Posted By: ngaisteve1
Date Posted: 09 January 2004 at 8:47pm

I have changed sql2 to sql but still the same result.

Posted By: Gullanian
Date Posted: 09 January 2004 at 8:56pm

Try putting the creation of the recordset object outside the loop, and also the dropping of the object outside the loop. Had a simliar problem, this seemed to be the problem.

Posted By: ngaisteve1
Date Posted: 09 January 2004 at 8:59pm

Alright, I got it actually, just couldn't see, 'hidden' under one of the records coz didn't have line spacing. I only found it using Ctrl + F. Thanks.