Prashubh Prashubh - 2 months ago 7
ASP.NET (C#) Question

My aim is to extract column names from a datable and put them in a dropdownlist in aspx. Facing issues

I am storing some column headers from an excel worksheet to a data table and extracting those column names/headers to populate my dropdown list in the aspx page. But sometimes I'm getting System.Data.DataRowView and sometimes it shows "Modified". I am using my datatable as DataSource for this Dropdownlist and the databinding is regularly failing. Can anyone help me with the code? Just a specimen so as to check where I am going wrong?

Here's the code:

protected void Button2_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
if (Path.GetExtension(FileUpload1.FileName) == ".xlsx")
{
DataTable dt = new DataTable();
ExcelPackage package = new ExcelPackage(FileUpload1.FileContent);
dt = package.ToDataTable(); //Datatable data from excel file

ListItem l = new ListItem();
for (int i = 0; i < dt.Columns.Count; i++)
{
DropDownList1.DataSource = dt;
l.Text = (dt.Columns[i].ColumnName).ToString();
l.Value = (dt.Columns[i].ColumnName).ToString();
DropDownList1.DataTextField = l.Text;
DropDownList1.DataValueField = l.Value;
DropDownList1.Items.Add(l);
DropDownList1.DataBind();
}
}
}
}

Answer

You don't need to add the items to the dropdownlist if you set the datatable as datasource, as this action will do that for you. You just have to set the property that will display the desired text and the hidden value of each item. Since I don't know the structure of your Datatable, I will post a sample code of my project:

        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter("SELECT Id, Name FROM Projects"), cn);
        da.Fill(dt);
        DropDownList1.DataSource = dt;
        DropDownList1.DataTextField = "Name";
        DropDownList1.DataValueField = "Id";
        DropDownList1.DataBind();

This operation will already add all the items stored in the datatable into the dropdownlist

EDIT: If the DataTable structure is dynamic/unknown, you will have to add the items by using a loop such as this:

      DropDownList1.Items.Clear();
      DataTable dt = new DataTable();
      ExcelPackage package = new ExcelPackage(FileUpload1.FileContent);
      dt = package.ToDataTable(); //Datatable data from excel file
      for (int i = 0; i < dt.Columns.Count; i++)
      {
            //Now depending on whether you will have to access them by value or not:
            DropDownList1.Items.Add(new ListItem(dt.Columns[i].ColumnName));    //Without Value
            DropDownList1.Items.Add(new ListItem(dt.Columns[i].ColumnName, i)); //With a numeric value that will serve like an index
      }        
Comments