c# - 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();

}

Answer 1

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.