I have a database with a products table, colors table and product colors table. I have created a SQL statement to extract the colors associated with a specific product based on ProductID. I am having a problem figuring out how to get the product name to show one time and then fill a select box with each color. Any help would be appreciated. Thank you.
SQL Statement:
sql = "SELECT DISTINCT Products.ProductName, Colors.ColorDescription FROM Products INNER JOIN (Colors INNER JOIN ProductColors ON Colors.ColorID = ProductColors.ColorID) ON Products.ProductID = ProductColors.ProductID WHERE Products.ProductID = " & id & ";"
Current ASP Code that repeats the product name with each color:
<% While Not objRec.EOF %>
<tr>
<td valign="top"><a href="productdetail.asp?ProductID=<%= objRec("ProductName") %>"><%= objRec("ProductName") %></a></td>
<td valign="top"><%= objRec("ColorDescription") %></td>
</tr>
<% objRec.MoveNext
Wend %>
Something like:
<%
count = 1
While Not objRec.EOF
if count = 1 then
..... code for the first time here
else
.... code for the rest of the times here
end if
count = count + 1
objRec.MoveNext
Wend
%>
??
Thanks for the reply. I did something like that and it works fine. Now I just need to figure out how to get another parameter working - size. I think I am going to try a group by statement.