DISTINCT COLOUR, BUT MUST HAVE PRIM KEY

So how do i solve this sql statment

Select distinct colour from wwwstocktbl where colour <> ' ' order by colour

The stament works as a statment but when run in asp i get an error as theres no primary key, but if i add the primary key then the select distinct fails

Select distinct ProductCode, Colour from wwwstocktbl where colour <> ' ' order by colourThe code as written should work correctly. Are you trying to use this code in a data adapter? We are going to need to see some of hte asp.net code in order to be able to help you.

Terri|||SqlSelectCommand4.CommandText = "select DISTINCT COLOUR from wwwstocktbl order by COLOUR"
SqlDataAdapter4.Fill(DataSet21, "wwwstocktbl")
DropDownListMaxPrice = DataBinder.Eval(DataSet21, "Tables[wwwStockTBL].DefaultView.[0].COLOUR")
DropDownListMinPrice.DataBind()

the problem is that to achive the distinct colour you must exclude the ProductCode field, but doing that generates an error as there is no primary key

<<<<---code copied from error page->>>>>>>
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Source Error:

Line 231:
Line 232: SqlSelectCommand4.CommandText = "select DISTINCT COLOUR from wwwstocktbl order by COLOUR"
Line 233: SqlDataAdapter4.Fill(DataSet21, "wwwstocktbl")
Line 234: DropDownListColour = DataBinder.Eval(DataSet21, "Tables(wwwStockTBL).DefaultView.(0).COLOUR")
Line 235: DropDownListColour.DataBind()

<<<<---end code copied from error page->>>>>>
chnaging the line
SqlSelectCommand4.CommandText = "select DISTINCT ProductCode, COLOUR from wwwstocktbl order by COLOUR"

would give a very differant set of results

???? do you think there are two problems here
1 the error being generated and
2 the code needed for the distinct cluse?|||Well, think about it. If you have BLUE in the table 5 times with 5 different codes, and the user chooses BLUE from the dropdownlist, WHICH BLUE is the database supposed to be using?

If you don't particularly care which BLUE your query chooses, then you can use a query like this:

 SELECT MAX(ProductCode) AS ProductCode, Colour FROM wwwstocktbl GROUP BY Colour ORDER BY Colour

This will give you a resultset of unique colours along with the MAX(ProductCode) for each.

Terri|||excelant, that code was realy interesting

after you asked about was i useing a datset i woundered why, so i created a dataset and dataadapter just for the colour question and it worked, but why, in my first sql code tests i selected all the fields, then in the finnal run just the colour and got the error, i then tryed to "dim" a new dataadapter and dataset at run time but with no sucsess, is there some rule about being able to change the fields returned by say a second sql statment such as at design time i define 3 fields but then at run time only request 2?