List a city only once

Hi...I want one listbox showing cities but I dont want to list a city more than one time....

I know that DISTINCT maybe could work...But I dont get it to work correctly....

The code:

<asp:ListBox ID="ListBox1" runat="server" AutoPostBack="True" DataSourceID="DataSource1" DataTextField="City" DataValueField="City"></asp:ListBox>

<asp:SqlDataSource ID="DataSource1" runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT DISTINCT [City] FROM [Location]">

</asp:SqlDataSource>

I got the message:

The text data type cannot be selected as DISTINCT because it is not comparable

The database have a table called "Location" with the column "ID" (int) as primary key and the column "City" (text)

You can't select distinct on "text" datatypes.

Not only that, but text, ntext, and image datatypes are depricated and should no longer be used. Perhaps a datatype change is in order for your Location table.

http://msdn2.microsoft.com/en-us/library/ms187993.aspx|||So what should I do....I just want one of every city....and it must be of the type "text"....Am I right?|||You should probably use varchar or nvarchar datatypes.

You could probably cast your text column to varchar and then execute your select distinct. You could do that on the fly.

See if this works:
select distinct(cast(city as varchar(50))) from location|||

I changed the City from text to VarChar(50) in the database and then I used:

SelectCommand="SELECT DISTINCT(cast(City as varchar(50))) FROM [Location]">

Got the following message:

DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'City'

|||Alias the output.

select distinct(cast(city as varchar(50))) as city from location|||

Thanx it works!

What is the code doing, because I am gonna describe it for someone else?

|||It's converting the datatype of city from "text" to "varchar."

You can't select distinct on a column datatype of text, and as a result your column should have its datatype updated in the table, permanently.

So in the SQL I gave you, we are simply converting on-the-fly instead of making a permanent change.|||

Okey...sorry, but I thaught that I should change it to varchar(50) in the database too....And it was therefore it worked

Now I changed it back to text and I got the following:

Erroemassage:

The data types text and nvarchar are incompatible in the equal to operator

(but what is the different between text and varchar(50)?....)

|||Change it in the database, and then you don't need to execute my SQL. You're first "select distinct city from location" will work just fine then.

You should really change the text field to varchar (or nvarchar if you desire) in the database because the text datatype is no longer supported and is removed from the next version of SQL Server.

So, change it in the database, and ignore everything else I've written here.

As far as your latest error message, I presume you're doing something other than a select distinct (cast......) from location.

Phil|||...|||

Okey....So everything I should do is to change text to varchar(50) in the database....

What is the 50 standing for?...is it max 50 characters? Can I use varchar(Max) instead of varchar(50)?

|||What?

For information on data types, please read: http://msdn2.microsoft.com/en-us/library/ms187752.aspx|||

Tigers21 wrote:

Okey....So everything I should do is to change text to varchar(50) in the database....

What is the 50 standing for?...is it max 50 characters? Can I use varchar(Max) instead of varchar(50)?

Yes, make sure that the length of the varchar field is big enough to hold your data. I wouldn't use max... You aren't going to have a city name 8,000 characters long, are you?|||

Ok...Thank you so much!

(I saw the link you gave me now!).....thanx!