| Author |
 Topic Search  Topic Options
|
ngaisteve1 
Groupie
Joined: 26 December 2002
Location: Malaysia
Status: Offline
Points: 169
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: SQL problem I think
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
%> |
Edited by ngaisteve1
|
 |
aalavar
Groupie
Joined: 08 December 2003
Status: Offline
Points: 46
|
Post Options
Thanks(0)
Quote Reply
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?
|
|
michael
Senior Member
Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
|
|
MorningZ
Senior Member
Joined: 06 May 2002
Location: United States
Status: Offline
Points: 1793
|
Post Options
Thanks(0)
Quote Reply
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
|
|
ngaisteve1
Groupie
Joined: 26 December 2002
Location: Malaysia
Status: Offline
Points: 169
|
Post Options
Thanks(0)
Quote Reply
Posted: 09 January 2004 at 8:47pm |
|
I have changed sql2 to sql but still the same result.
|
|
Gullanian
Senior Member
Joined: 04 January 2002
Location: England
Status: Offline
Points: 4373
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
ngaisteve1
Groupie
Joined: 26 December 2002
Location: Malaysia
Status: Offline
Points: 169
|
Post Options
Thanks(0)
Quote Reply
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.
|
|
