DropDownList with repeated data value fields OnSelected get confused and chooses the first one why?

Question

Current Issue: My DropDownList is provided with DataTextField="COLUMNS_NAME" DataValueField="DATA_TYPE" properties, the DropDownList_SelectedIndexChanged() does not retain the text based on the selected input. But it retains the first value from the list of items

Solution Required: How to Retain the selected input text based on the DATA_TYPE property ? I tried storing the Session["DDLValue"] = DropDownList.SelectedItem.Text but it always retains the first value from the list of items which satisfies the respective DATA_TYPE present in an Index.

i.e. if i choose "e" from The following DropDownList inputs the value retained in DropDownList is "d"

How to retain "e"

COLUMN_NAME  DATA_TYPE  
a            decimal
b            decimal
c            decimal
d            int
e            int
f            varchar
g            varchar  
h            varchar
i            varchar
j            varchar

Aspx Code:

<asp:DropDownList ID="DropDownList5" runat="server"  AutoPostBack="true" OnSelectedIndexChanged ="DropDownList5_SelectedIndexChanged" DataSourceID="MySqlDataSource">
</asp:DropDownList>
<asp:SqlDataSource ID="MySqlDataSource" runat="server">
</asp:SqlDataSource>

C# code:

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        BindDropDownLists();
    }
}

private void BindDropDownLists()
{
    MySqlDataSource.ConnectionString = connection;
    MySqlDataSource.SelectCommand =  "SELECT DATA_TYPE + '_' + convert(varchar(10), ROW_NUMBER() OVER(ORDER BY DATA_TYPE ))as DATA_TYPE, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME = 'RESULT' AND COLUMN_NAME IN ('Column1','column2','Column3'))";

    DropDownList5.DataTextField = "COLUMN_NAME";
    DropDownList5.DataValueField = "DATA_TYPE";
    DropDownList5.DataBind();

}

where are you filling the drop down, is it on the page load ? — Kumar Manish, May 08 '14 at 07:12
May be you are filling the `dropdown` list again before getting the values from the `dropdown` list. — शेखर, May 08 '14 at 07:14

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

I think the you should have unique values for the dropdown. You could try to fabricate the values which you could uniquely identify. Something like:

COLUMN_NAME  DATA_TYPE
a            a_decimal
b            b_decimal
c            c_decimal
d            d_int
e            e_int
f            f_varchar
g            g_varchar
h            h_varchar
i            i_varchar
j            j_varchar

A solution like this will ensure that you have unique values in your dropdown. The reason for using this is that creating DATA_TYPE string and extracting the actual value from it very simple. Just combine COLUMN_NAME and DATA_TYPE with underscore and split on underscore whenever the actual value is required.

The crux: You should try to have unique value for the dropdown. It could be an ID or some other unique value.

Please take this as a starting point and not as a copy paste solution.

i have changed my select command to combine could you please comment if i am doing right ? — Shrivatsan, May 08 '14 at 07:42
Well, if you are doing it right your problem should get resolved automatically :) — Vivek Jain, May 08 '14 at 10:07

score 1 · Accepted Answer · answered May 08 '14 at 07:35

1

After render , your drop down list is look like below , need to use unique value field for proper selection enter image description here

answered May 08 '14 at 07:35

Kumar Manish

3,746
3
37
42

you can use this query (select COLUMN_NAME + '_' + convert(varchar(10), ROW_NUMBER() OVER(ORDER BY COLUMN_NAME )) from col), when you insert into database , split it after "_" and make proper data in the database – Kumar Manish May 08 '14 at 09:02
Could you please add the sql query in the BindDropDownList() function for SqlDatasource.SelectCommand ? – Shrivatsan May 08 '14 at 09:25
try this SELECT COLUMN_NAME + '_' + convert(varchar(10), ROW_NUMBER() OVER(ORDER BY COLUMN_NAME ))as COLUMN_NAME , DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS (TABLE_NAME = 'RESULT' AND COLUMN_NAME IN ('Column1','column2','Column3')) – Kumar Manish May 08 '14 at 09:29
@ Kumar Manish for varchar and datetime data value field it retains correctly but for decimal input the problem still occurs how to handle for all the datatypes. – Shrivatsan May 08 '14 at 09:48
what type of issues came out during decimal value ? – Kumar Manish May 08 '14 at 09:50
@ Kumar Manish it retains the first value again for decimal inputs. I have varchar, decimal and datetime datavalue fields in DDL – Shrivatsan May 08 '14 at 09:52
1

try this query SELECT DATA_TYPE + '_' + convert(varchar(10), ROW_NUMBER() OVER(ORDER BY DATA_TYPE ))as DATA_TYPE + ' _ ' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS (TABLE_NAME = 'RESULT' AND COLUMN_NAME IN ('Column1','column2','Column3')) – Kumar Manish May 08 '14 at 09:58
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/52284/discussion-between-shrivatsan-and-kumar-manish) – Shrivatsan May 08 '14 at 10:05
Could you please provide the sql query i.e change the above query to select information_schema.columns from two different sql database tables? – Shrivatsan May 24 '14 at 05:46

score 0 · Answer 3 · answered May 08 '14 at 07:29

As you have duplicate values in data value field the problem is occurring. It is looking for the first match and selecting it.

If you want to continue with this then you may prefix some auto-increment integer and add it in value field and use substring to get the original value.

Shrivatsan · Answer 4 · 2014-05-08T11:09:37.693

0

Correct Solution : Thanks to @Kumar Manish comments

Select Command inside the BindDropDownLists()

private void BindDropDownLists()
     {
         SqlDataSource.ConnectionString = connection;

         SqlDataSource.SelectCommand= "SELECT DATA_TYPE + '_' + convert(varchar(10), ROW_NUMBER() OVER(ORDER BY DATA_TYPE ))as DATA_TYPE, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME = 'RESULT' AND COLUMN_NAME IN ('Column1','column2','Column3'))";
         DropDownList5.DataTextField = "COLUMN_NAME";
         DropDownList5.DataValueField = "DATA_TYPE";
         DropDownList5.DataBind();
     }

edited May 08 '14 at 11:09

answered May 08 '14 at 10:28

Shrivatsan

105
1
18

@KrishnrajRana Yes its able to retain the DDL value now based on uniquely generated datavalue fields – Shrivatsan May 08 '14 at 10:50

DropDownList with repeated data value fields OnSelected get confused and chooses the first one why?

4 Answers4

Linked