Removing duplicates from a DropdownList

[brian richards]

I'm getting a list of addresses from a database and binding them to a
datagrid. I have a dropdown list that allows the user select a particular
state. The event updates the query to only fetch the slected state. After
that I'd like to populate a seperate DropdownList that allows the user to
see a selected city. So when I bind the data the code looks like this:

public void BindGrid()
{
UpdateSelectStmt();
adptr_LocationSummary.Fill(dsLocationSummary1);
//CityChooser is a DropdownList
CityChooser.DataSource = dsLocationSummary1;
CityChooser.DataTextField = "City";
CityChooser.DataValueField = "City";
CityChooser.DataBind();
//ListItemCollection cities = CityChooser.Items;
//do something here to remove duplicates
dg_LocationSummary.DataBind();
}

Now the problem is that the CityChooser is getting a city for every row, but
what I'd like is to show only unique cities. I couldn't find a flag for
getting only unique items. I can seem to think of a clean, efficient, way to
remove all the duplicates without copying the items into a new list and
binding (though it wouldn't technically be binding I suppose) the
CityChooser to the new list.

Thanks

Brian

[William F. Robertson, Jr.]

Why don't you just change your SQL query to get only unique cities. Are you
trying to get the CityChooser to have the same city listed twice, but the
dg_LocationSummary listed only once?

bill

"brian richards" <brian...@hotmail.com> wrote in message
news:u0Es8jpW...@TK2MSFTNGP10.phx.gbl...

[S. Justin Gengo]

Brian,

You could filter your initial select statement by using the keyword
DISTINCT.

SELECT DISTINCT citynames FROM tblEntries

If you can't filter the records at the database request level then you can
always build a view in a similar fashion from those records.
Dim DataView As New DataView(dsLocationSummary1.Tables(0))

DataView.RowFilter = "DISTINCT citynames ASC"

CityChooser.DataSource = DataView;

CityChooser.DataTextField = "City";
CityChooser.DataValueField = "City";

Sincerely,

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche

"brian richards" <brian...@hotmail.com> wrote in message
news:u0Es8jpW...@TK2MSFTNGP10.phx.gbl...

[makthar]

In your query use DISTINCT
SELECT DISTINCT CITY FROM <tablename> WHERE STATE='<state
name>'. This will bring only one of each city from the
table.

>.
>

[brian richards]

Well the dg_SummaryLocation is the datagrid. By default it shows all the
locations for all the states (8000+) paged and what not. Then they can pick
a specific state. From there I modify the query and rebind the datagrid. Now
I want them to be able to narrow down by city. There are multiple street
addresses for each city and I want to display all of those. The query uses
distinct but for this app distinct is a State, City, Address, Account
Number. So I may also have duplicate addresses if there is more than one
accoutn number associated with that location. And opposite of what you said
I want the city listed in teh CityChooser once but it will probably show up
more than once in the datagrid.

Thanks

Brian

"William F. Robertson, Jr." <wfrob...@kpmg.com> wrote in message
news:ufEZLHqW...@tk2msftngp13.phx.gbl...

[brian richards]

I tried this:

public void BindGrid()
{
UpdateSelectStmt();
adptr_LocationSummary.Fill(dsLocationSummary1);

DataView ndv = new DataView(dsLocationSummary1.Tables[0]);
ndv.RowFilter = "DISTINCT city ASC";
CityChooser.DataSource = ndv;

CityChooser.DataTextField = "City";
CityChooser.DataValueField = "City";
CityChooser.DataBind();

dg_LocationSummary.DataBind();
}

But I get the same results. I could just do a new query: "select distinct
city from table where state = 'NC'" or whatever, but I'm trying not to
access the database again if at all possible.

I also tried:

public void BindGrid()
{
UpdateSelectStmt();
adptr_LocationSummary.Fill(dsLocationSummary1);

CityChooser.DataSource = dsLocationSummary1;
CityChooser.DataTextField = "City";
CityChooser.DataValueField = "City";
CityChooser.DataBind();

for(int i = 0;i<CityChooser.Items.Count;i++)
{
while( (i+1) < CityChooser.Items.Count && CityChooser.Items[i] ==
CityChooser.Items[i+1])
{
CityChooser.Items.RemoveAt(i+1);
}
}
dg_LocationSummary.DataBind();
}

But that didn't work either.

But got the same results if see my other post you can see why
"S. Justin Gengo" <sjg...@aboutfortunate.com> wrote in message
news:ewB$AMqWDH...@TK2MSFTNGP11.phx.gbl...

[brian richards]

Maybe I ought to mention that I'm using the same datasource (thus query) for
the datagrid and the CityChooser dropdown. I'm merely binding a single
column to the drop down list.

-Brian

"brian richards" <brian...@hotmail.com> wrote in message
news:u0Es8jpW...@TK2MSFTNGP10.phx.gbl...

[S. Justin Gengo]

Brian,

Trying putting the whole select statement into the row filter.

ndv.RowFilter = "select distinct city from table where state = 'NC'";

--
S. Justin Gengo, MCP
Web Developer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche
"brian richards" <brian...@hotmail.com> wrote in message

news:OutHBTqW...@TK2MSFTNGP12.phx.gbl...

[brian richards]

Well I got it to work like this:

public void BindGrid()
{
UpdateSelectStmt();
adptr_LocationSummary.Fill(dsLocationSummary1);

CityChooser.DataSource = dsLocationSummary1;
CityChooser.DataTextField = "City";
CityChooser.DataValueField = "City";
CityChooser.DataBind();

for(int i = 0;i<CityChooser.Items.Count;i++)
{

while( (i+1) < CityChooser.Items.Count && CityChooser.Items[i].Value ==
CityChooser.Items[i+1].Value)
{
CityChooser.Items.RemoveAt(i+1);
}
}
dg_LocationSummary.DataBind();
}

I wasn't comparing the values before. But this doesn't feel like a very
good solution. Meaning what about when I'm doing this for 8000 cities?

-Brian

"S. Justin Gengo" <sjg...@aboutfortunate.com> wrote in message
news:ewB$AMqWDH...@TK2MSFTNGP11.phx.gbl...

[Marina]

Why not run a separate query to get the distinct cities? That's really the
way to do it.

"brian richards" <brian...@hotmail.com> wrote in message

news:e1P83YqW...@TK2MSFTNGP11.phx.gbl...

[J 2 the B]

In your query just do a "SELECT DISTINCT City FROM CityTable". That
query will get only the unique 'City' values in the table 'CityTable'.

Hope this helps.
JB

"brian richards" <brian...@hotmail.com> wrote in message news:<u0Es8jpW...@TK2MSFTNGP10.phx.gbl>...