OleDbDataAdapter Fill Method not working

Question

0.00/5 (No votes)

See more:

My problem is that the Dataset does not retrieve any data from the excel sheet and it stops at the part of Fill Method.

Here is the code

C#

label1.Text = openFile.FileName.Substring(openFile.FileName.LastIndexOf('\\')+1,openFile.FileName.Length - (openFile.FileName.LastIndexOf('\\')+1));
            string str_conn = "";
            if(label1.Text.EndsWith(".xls"))
                str_conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + openFile.FileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
            else
                str_conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = " + openFile.FileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\";";
            label1.Text = label1.Text.Remove(label1.Text.LastIndexOf('.'));

            OleDbConnection connection = new OleDbConnection(str_conn);
            connection.Open();
            System.Data.DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            String[] excelSheetNames = new String[dt.Rows.Count];
            int i = 0;

            foreach (DataRow row in dt.Rows)
            {
                excelSheetNames[i] = row["TABLE_NAME"].ToString();
                i++;
            }

            OleDbDataAdapter odp = new OleDbDataAdapter("SELECT * FROM ['" + excelSheetNames[0].ToString() + "'];", connection);
            OleDbCommandBuilder m_cbCommandBuilder = new OleDbCommandBuilder(odp);
            DataSet ds = new DataSet();
            odp.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
            dataGridView1.Update();

:((

Posted 2-Jun-10 16:24pm

jovhenni19

Updated 2-Jun-10 19:45pm

Sandeep Mewara

v2

Add a Solution

Comments

Sandeep Mewara 3-Jun-10 2:48am

Look at my updated reply. that should resolve your issue.

manognya kota 9-Oct-12 2:07am

Hi, Did you get the solution for this? even i have the same problem.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

**PSK_** · Answer 1 · 2010-06-02T17:36:00

Solution 1

I am not sure but can you change your select statement as following.

OleDbDataAdapter odp = new OleDbDataAdapter("SELECT * FROM " + excelSheetNames[0].ToString() + "$", connection);

Posted 2-Jun-10 17:36pm

PSK_

Comments

jovhenni19 3-Jun-10 1:55am

it still didn't work :((

Sandeep Mewara · Answer 2 · 2010-06-02T19:49:00

jovhenni19 wrote:
OleDbDataAdapter odp = new OleDbDataAdapter("SELECT * FROM ['" + excelSheetNames[0].ToString() + "'];", connection);

Try:

OleDbDataAdapter odp = new OleDbDataAdapter("SELECT * FROM ['" + excelSheetNames[0].ToString() + "$'];", connection);

I have added a '$' at the end of the Excel sheetname.
Such that the query is something like: select * from [sheet1$]

jovhenni19 wrote:
OleDbDataAdapter odp = new OleDbDataAdapter("SELECT * FROM ['" + excelSheetNames[0].ToString() + "'];", connection);
OleDbCommandBuilder m_cbCommandBuilder = new OleDbCommandBuilder(odp);
DataSet ds = new DataSet();
odp.Fill(ds);

This is WRONG!

You fill dataset with dataadaptor. You are doing reverse.
It needs to be something like:

C#

DataSet myDataSet = new DataSet(); 
OleDbCommand CommandObject = new OleDbCommand ("Select * from employee");
OleDbAdapter myDataAdapter = new OleDbAdapter (CommandObject, con);
myDataAdapter.Fill (myDataSet, "EmployeeData");

Refer: DataSet and DataAdapter [^]

Hope, that should resolve the issue for you!

NCZIMM · Answer 3 · 2010-07-02T12:01:00

I have seen this construct (myDataAdapter.Fill (myDataSet, "EmployeeDate")
)several times now. When I attempt it I get the message "No overload for method 'Fill' takes 2 arguments. All of the "documentation" I have read indicates there is only one argument for fill and that is the data set name. How does this other argument get into the picture??

CalvinGoestoWhiteCastle · Answer 4 · 2010-07-13T21:08:00

"SELECT * FROM [" + excelSheetNames[0].ToString() + "];"

// Removed the single quotes from the original statement.

The single quote will make the table name appear as 'Table 01$' and not Table 01$ which is what is readable in that C# oledb command.

Please try this in the command statement:
1) Keep original code you posted
2) Just change the command by removing the single quotes in the select statement

OleDbDataAdapter Fill Method not working

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0