Duplicate text values in dropdownlist, asp.net

Question

This is my database table, SqlDataSource SelectCommand of my dropdownlist and text and value field property of my dropdownlist. I want my selected country have all resultsId correspending to it. I mean when i select spain i want to have both 2 and 3 for resultsid. But problem is, in my dropdownlist i have duplicate country texts and i cannot change it.

country      resultsId
spain          2
spain          3
china          2
china          4
canada         1
canada         4
england        1
england        3
usa            1
usa            2

    SelectCommand="SELECT distinct countries, resultsid  FROM countrytable" 
    DataTextField="country" DataValueField="resultsid"

I tried this SelectCommand:

    SelectCommand="SELECT resultsId,(select country, count(*) 
    from countrytable group by country having count(*) >1) FROM countrytable

But and now, i have this error :

    Only one expression can be specified in the select list when the 
    subquery is not introduced with EXISTS

I am using sql server and asp.net c#

Possible duplicate of [Only one expression can be specified in the select list when the subquery is not introduced with EXISTS](https://stackoverflow.com/questions/1904314/only-one-expression-can-be-specified-in-the-select-list-when-the-subquery-is-not) — IrishChieftain, Jan 22 '18 at 12:45

lucky · Answer 1 · 2018-01-22T13:47:24.287

1

The error is pretty clear that tells, your query must only return one value for specified select clause. So, you could apply self join;

select c.* from countrytable c inner join
(select country from countrytable group by country having count(*) >1) c2
ON c.country = c2.country

edited Jan 22 '18 at 13:47

answered Jan 22 '18 at 12:47

lucky

12,734
4
24
46

But this way only one value returns from query and that is max one. I want both resultsid values to return with the selection. If it is possible. – Mehmet Ali Bilgin Jan 22 '18 at 12:57
i have this error message, No column name was specified for column 2 of 'c2'. i tried to fix but i couldn't. – Mehmet Ali Bilgin Jan 22 '18 at 13:15

score 1 · Accepted Answer · answered Jan 22 '18 at 13:01

1

You should use STUFF for this.

select distinct country, STUFF
(
    (select ',' + cast(resultsid as varchar) from countrytable c2 
    where c1.country = c2.country
    for XML PATH ('')),1,1,''
) 
from countrytable c1

answered Jan 22 '18 at 13:01

Bharadwaj

2,535
1
22
35

I'm glad to know, you can accept it as an answer if this is what you looking for :) – Bharadwaj Jan 22 '18 at 14:20

AlbertK · Answer 3 · 2018-01-22T20:58:57.117

You can't implement your reqiurements using SqlDataSource since Asp.Net data-bound controls don't support multiple values for one item. Therefore you need to prepare you data to have only one value for one item.

You have minimum two options here:

1) In your particular case the simplest way is to change your query to concatinate values in one string like it clearly showed @Bharadwaj

1) In more common case you can use ObjectDataSource. You need to create a method that will return a collection of objects that will have properties for DataTextField and DataValueField for example Country = "spain", ResultIds = "2,3". Inside of this method you can use your query and do any data processing that can't be done by sql.

Anyway you should have only one value (in this case the concatinated string) for one datasource item. Then you will just parse the string to take values from your DropDownList

Duplicate text values in dropdownlist, asp.net

3 Answers3