SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Addict suzkaw's Avatar
    Join Date
    Apr 2002
    Location
    North Carolina
    Posts
    338
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ASP Database results help

    Hi,
    I have created a small script that will read from a related products table then display the products associated with it. However I am only getting it to return one product even though I have multiple listed. Here is the code I am using:

    Code:
     prod = request.QueryString("prod")
     sSQL = "SELECT prodID,related FROM crosssale WHERE prodID='"&prod&"'"
     response.write "<table width='100%' align='center'><tr>"&vbCrLf
     			rs.Open sSQL,cnn,0,1
     				Do While Not rs.EOF 
     					pID = rs("related")
     		    			sSQL2 = "SELECT * FROM products WHERE pID = '"&pID&"' AND pDisplay = 1"
     		    		    'response.write(sSQL2)
     		    		    	rs2.Open sSQL2,cnn,0,1
     		    		    		if NOT rs2.EOF then
     		    		    			response.write "<td align='center'> <a href='proddetail.asp?prod="& pID & "'><img src='"& rs2("pImage") &"' alt='"& rs2("pName") & "' border='0'></a><br>"&rs2("pName")&"<br>"
     		    		    			response.write "<b>" & xxPrice & ":</b> <span class=""price"" id=""pricediv" & Count & """ name=""pricediv" & Count & """>" & FormatEuroCurrency(rs2("pPrice")) & "</span></td>"&vbCrLf
     		    		    		end if
     		    		    	rs2.Close
     					rs.MoveNext
     				Loop
     			rs.Close
     	response.write "</tr></table>"&vbCrLf
    When I write out the query it displays two however it just isn't running through the if for the second time. Am I going about this correctly?
    Thanks Eric.

  2. #2
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Your looping through your first query, but not your second. Could that be your problem?

    You could actually join these tables together like this: (untested):

    Code:
     prod = request.QueryString("prod")
    sSQL = "SELECT c.prodID, c.related, p.* FROM crosssale c " & _
     " INNER JOIN products p ON c.related = p.pID " & _
     " WHERE prodID='"&prod&"'"
    response.write "<table width='100%' align='center'><tr>"&vbCrLf
    rs.Open sSQL,cnn,0,1
      If NOT rs.EOF then
    	 Do While Not rs.EOF 
    		response.write "<td align='center'> <a href='proddetail.asp?prod="& pID & "'><img src='"& rs2("pImage") &"' alt='"& rs2("pName") & "' border='0'></a><br>"&rs2("pName")&"<br>"
    		response.write "<b>" & xxPrice & ":</b> <span class=""price"" id=""pricediv" & Count & """ name=""pricediv" & Count & """>" & FormatEuroCurrency(rs2("pPrice")) & "</span></td>"&vbCrLf
    	  rs.MoveNext
    	Loop
      End if
    rs.Close
    response.write "</tr></table>"&vbCrLf


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •