How to populate the excelsheetcloumn names into a dropdown list?

Question

Hi i have an excel sheet with headers,i want to populate those headers into a dropdown list...

can any one help me with the select statement and procedure

i am working on a code i dnt know whether its correct or not

      DropDownList list = new DropDownList();
                    string connectionstring = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", Excelpath);  
                    string query1 =  String.Format("select * from [{0}]", DDlist.SelectedItem.Text);                                             
                    // OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query1, connectionstring);                                                
                    using (OleDbConnection conn1 = new OleDbConnection(connectionstring))
                    {
                        OleDbCommand odc1 = new OleDbCommand(string.Format(query1, conn1));
                        conn1.Open();
                        OleDbDataReader dr;
                        dr = odc1.ExecuteReader();
                        while (dr.Read())
                        {
                            list.Items.Add(dr[column.ColumnName].ToString());
                        }
                        dr.Close();
                        conn1.Close();
                    }

in this method i am geting an error at this line

    dr = odc1.ExecuteReader();Error:ExecuteReader: Connection property has not been initialized.

can any one help mw with this, thanks in advance

throwing errot at "list.Items.Add(dr[column.ColumnName].ToString());""System.IndexOutOfRangeException: CUSTOMER_ID"...@Nalaka526 — Dotnet ReSource, Jan 18 '12 at 06:27

score 0 · Answer 1 · answered Jan 18 '12 at 06:24

0

I think you are not passing Connection to OleDBCommand. So try,

OleDbCommand odc1 = new OleDbCommand(query1, conn1);

answered Jan 18 '12 at 06:24

Nalaka526

11,278
21
82
116

score 0 · Answer 2 · answered Jan 18 '12 at 06:31

0

The OleDbCommand class has two main parameters to be passed for a proper execution. That are Query and connection object. In your code you are passing the connection, but it is placed inside the String.Format(), so place it outside. See the code below....

OleDbCommand odc1 = new OleDbCommand(string.Format(query1, conn1));

TO

OleDbCommand odc1 = new OleDbCommand(query1, conn1);

answered Jan 18 '12 at 06:31

Sai Kalyan Kumar Akshinthala

11,704
8
43
67

throwing errot at "list.Items.Add(dr[column.ColumnName].ToString());""System.IndexOutOfRangeExcept‌ion: CUSTOMER_ID"... – Dotnet ReSource Jan 18 '12 at 06:33
Your select statement is little big wrong, select only column names from the table. Go to this link to select only columns http://stackoverflow.com/questions/452464/how-can-i-get-column-names-from-a-table – Sai Kalyan Kumar Akshinthala Jan 18 '12 at 06:39
That statement is for geting the column names from database....how can i use in excel – Dotnet ReSource Jan 18 '12 at 06:46
When you get the only column names from a query result, you can use directly like "list.Items.Add(dr[0].ToString())" ok. – Sai Kalyan Kumar Akshinthala Jan 18 '12 at 06:51

score 0 · Accepted Answer · answered Feb 13 '12 at 15:57

try this it may work.....

                  foreach (DataTable table in DS.Tables)
          {
               foreach (DataColumn column in table.Columns)
                {
                   DropDownList list = new DropDownList();
                   string connectionstring = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", Excelpath);
                   string query1 = String.Format("select * from [{0}]", DDlist.SelectedItem.Text);                                                                      
                   using (OleDbConnection conn1 = new OleDbConnection(connectionstring))
                   {
                       OleDbCommand odc1 = new OleDbCommand(query1, conn1);
                       conn1.Open();
                        DataTable dte = null;
                       DataSet ds = new DataSet();
                       dte = conn1.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                       OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
                       objAdapter1.SelectCommand = odc1;
                       objAdapter1.Fill(ds, "xldata");
                        for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                       {
                           list.Items.Add(ds.Tables[0].Columns[i].ColumnName.ToString());
                       }
                       conn1.Close();
                    }

i think this one suits my answer better from all the answers..thank you@Anil — Dotnet ReSource, Feb 13 '12 at 16:06

How to populate the excelsheetcloumn names into a dropdown list?

3 Answers3