repeating values in drop down list

[mr relaxo]

Hi, I am trying to do a product details page for a clothing site. I am placing colours and sizes in two drop down list boxes. The problem i am having is that i get a lot of reapeating values as a result i.e for each colour there are a number of different sizes so i get

 

ddlcolours ddlsizes

 

navy x

navy s

navy m

black x

black s

 

etc etc. Can I make the ddl's only show unique values or do I need to rethink my SQL statement?

 

Dim strSQL As String = "select distinct px.filename,p.description, c.description as 'colours',s.description as 'sizes' from productsXcolours px " _

& "INNER JOIN products p ON px.productID = p.productID " _

& "INNER JOIN colours c on px.colourID = c.colourID " _

& "INNER JOIN productsxsizes pxs ON p.productID = pxs.productID " _

& "INNER JOIN sizes s ON pxs.sizeID = s.sizeID " _

& "where px.productID = '" & (prodID) & "' ;"

 

thank you for your time. :D

[Robby]

Since you're binding two controls you should seperate the SQL into two statements.

 

Excluding Productsxsizes from Colours will give you the Distinct you need. (and vice versa)

[Robby]

I just thought of something, what if the user selects a color and size that do not exist?

 

Isn't it better to display each color and size per product as the user selects a product?

[mr relaxo]

Thanks alot Robby, the two separate sql statements worked great.

 

Im only displaying the related colours and sizes for any one product at a time. Is that what you meant in your last reply? Or do you think I should repopulate the sizes ddl depending on what colour is selected. Yeah thats probably a good idea.

Edited August 16, 2003 by mr relaxo

[Robby]

What I'm thinking is; When you search a product by ID it will return (let's say) red, white, black, and Small, Med, Large, XLarge.

 

Does this mean that each color has a matching size?