Click here to Skip to main content
15,881,092 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
getting this Error
Severity		CS0029	Cannot implicitly convert type 'System.Data.OleDb.OleDbDataReader' to 'System.Data.DataRow'


What I have tried:

System.Data.DataTable datatable = new System.Data.DataTable();
            
            var fileName = string.Format("{0}\\TestFile.xls", Directory.GetCurrentDirectory());
            string con =@$"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={fileName};" + @"Extended Properties='Excel 12.0;HDR=Yes;'";
            using (OleDbConnection connection = new OleDbConnection(con))
            {
                connection.Open();
                OleDbCommand command = new OleDbCommand("select * from [test$]", connection);
                using (OleDbDataReader dr = command.ExecuteReader())
                {
                    dr.Read();
                    var dd = dr.GetName(0);
                    datatable.Columns.Add();
                    for (int i = 0; i < dr.FieldCount; i++)
                        datatable.Columns.Add(dr.GetName(i).ToString());

                    //datatable.Load(dr);
//Below code not working
                    while (dr.Read())
                    {
                        try
                        {
                            DataRow dataRow = datatable.NewRow();
                            dataRow = dr;
                            datatable.Rows.Add(dataRow);
                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex);
                        }
                      
                    }
                }
            }
Posted
Updated 3-Aug-22 22:44pm
v2

1 solution

Use a DataAdapter and it will fill it automatically:
C#
string cmdString = string.Format("Select * from [{0}]", sheetName);
OleDbCommand cmd = new OleDbCommand(cmdString);
cmd.Connection = connection;
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
DataTable datatable = dataset.Tables[0];


See also the documentation at OleDbDataReader.Read Method (System.Data.OleDb) | Microsoft Docs[^].
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900