How to set data table two data column as datasource in asp.net dropdown

Question

I have a DataTable called cachedData. It values like this | COL1 | COL2 | | -------- | -------------- | | 12 |- | | -| 59| | 32 |- | | -| 63|

I want to set these values to a dropdown without considering these as two columns.

Ex : dropdown values should be 12,59,32,63

I tried but this gives me an error.Please help me to bind this dataTable values to dropdown

            if (cachedData != null)
            {

                this.DigitalChannelSignatureDropDownList.DataTextField = "COL1"+"COL2";
                this.DigitalChannelSignatureDropDownList.DataValueField = "COL1"+"COL2";
                this.DigitalChannelSignatureDropDownList.DataSource = cachedData;
                this.DigitalChannelSignatureDropDownList.DataBind();

            }

Answer 1

Well, it is not clear what the "-" values are in your table?

However, you could use a union query, and say this:

 SELECT COL1 AS MyCol FROM MyTable WHERE COL1 <> '-'
 UNION ALL 
 SELECT COL2 AS MyCol FROM MyTable WHERE COL2 <> '-'

The output of above then becomes this:

  MyCol
  -----
  12        
  32        
  59        
  63        

And perhaps your '-' was for null values, so change the above SQL to:

 SELECT COL1 AS MyCol FROM MyTable WHERE COL1 is not null
 UNION ALL 
 SELECT COL2 AS MyCol FROM MyTable WHERE COL2 is not null

So, now to load up the drop-down list, we have this:

Markup:

        <asp:DropDownList ID="DropDownList1" runat="server">

        </asp:DropDownList>

And code behind is thus this:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string strSQL =
                @"SELECT COL1 AS MyCol FROM MyTable WHERE COL1 <> '-'
                UNION ALL 
                SELECT COL2 AS MyCol FROM MyTable WHERE COL2 <> '-'";

            DropDownList1.DataTextField = "MyCol";
            DropDownList1.DataSource = General.MyRst(strSQL);
            DropDownList1.DataBind();

            DropDownList1.Items.Insert(0,"Please Select"); // optional 

        }
    }

And of course, code to return a datatable from the SQL can be this:

    public DataTable MyRst(string strSQL)
    {
        DataTable rstData = new DataTable();

        using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))
        {
            using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))
            {
                cmdSQL.Connection.Open();
                rstData.Load(cmdSQL.ExecuteReader());
            }
        }
        return rstData;
    }