ASP Database results help

[suzkaw]

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:

 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?

[Thing]

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):

 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