Duplicate Values from ComboBox

[toadzky]

I have a combo box that is populated from entries in a SQLite table. The entries are city names.

Here is the code for the data binding:

cbb_City.DataSource = data;
cbb_City.DisplayMember = "Accounts.City";
cbb_City.ValueMember = "Accounts.City";

It gets the values just fine but it has an entry for each record. There are only a couple of unique values and thats all I want.

How can I remove the duplicates and just have 1 entry for each city?

[Ramy Mahrous]

Before binding

List<Object> dataAsObj = new List<Object>();
foreach(var x in Data)
{
if(!dataAsObj.Contains(x))
dataAsObj.Add(x);
}
cbb_City.DataSource = dataAsObj;
cbb_City.DisplayMember = "Accounts.City";
cbb_City.ValueMember = "Accounts.City";

[toadzky]

does it matter that "data" is a dataset? i can't foreach through a dataset.

[Ramy Mahrous]

DataSet?! SQLite doesn't have Distinct keyword?

[kvprajapati]

Dear,

Due to ViewState feature, write your code in page load event with IsPostBack status:

if(IsPostBack==false) {
     cbb_City.DataSource = data;
     cbb_City.DisplayMember = "Accounts.City";
     cbb_City.ValueMember = "Accounts.City"; 
     ...
 }

PS: Check also Select query; It must return unique City name. (Use Distinct phrase)

[toadzky]

As far as I know SQLite itself allows the distinct keyword but the SQLite.NET wrapper doesn't implement it I don't think. I'm still experimenting.

And what's wrong with a dataset?

[Ramy Mahrous]

If you execute SQL Statement against SQLite, add distinct!! DataSet hasn't do to any much work over it carries data for you!

[toadzky]

I got it working. Turns out SQLite.NET does implement the DISTINCT keyword but it doesn't work with Datasets for some reason.

Here's the code:

SQLiteCommand get = new SQLiteCommand("SELECT DISTINCT City FROM Accounts;", database);
SQLiteDataReader x = get.ExecuteReader();
while (x.Read())
    cbb_City.Items.Add(x[0]);
cbb_City.SelectedIndex = 0;

Anyone know why a DataAdapter with a Dataset doesn't let me use DISTINCT?

[toadzky]

I'm retracting my last post. Turns out the DISTINCT keyword works just fine with DataAdapters and DataSets. Here's the code I used:

SQLiteDataAdapter city = new SQLiteDataAdapter("SELECT DISINCT City FROM Accounts;",  dataConnection);
city.TableMappings.Add("Table", "City");
city.Fill(dataSet);

cbb_City.DataSource = dataSet;
cbb_City.DisplayMember = "City.City";
cbb_City.ValueMember = "City.City";

Thanks for all your help.