Problems reading Excel file from ASP.NET

I'm having some problems reading an Excel file form ASP.NET. I'm trying to add a range of excel data to a grid view which will be updated every time I open my application. I would really appreciate the help- this has been really frustrating to fix as I know it's probably something really simple that I'm missing now.

I have named a range of cells in excel 'MyRange'. The error message I am getting says that it is not picking up this range. I have the error message below and my code below that. I'm assuming that I can have the excel file accessed in the same folder as my ASP.NET project.

Here is the error message:
 The Microsoft Jet database engine could not find the object 'MyRange'.  Make sure the object exists and that you spell its name and the path name correctly.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: The Microsoft Jet database engine could not find the object 'MyRange'.  Make sure the object exists and that you spell its name and the path name correctly.

Source Error:

Line 59:
Line 60:         // Fill the DataSet with the information from the worksheet.
Line 61:         objAdapter1.Fill(objDataset1, "MyRange");
Line 62:
Line 63:         // Bind data to DataGrid control.

protected void Page_Load(object sender, EventArgs e)
    {

        String excelConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
    "Data Source=" + Server.MapPath("~/AllStocks.xls") + ";" +
    "Extended Properties=Excel 8.0;";

        OleDbConnection objConn = new OleDbConnection(excelConn);

        objConn.Open();

        OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM MyRange", objConn);

        OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

        objAdapter1.SelectCommand = objCmdSelect;

        DataSet objDataset1 = new DataSet();

        objAdapter1.Fill(objDataset1, "XLData");

        GridView3.DataSource = objDataset1.Tables[0].DefaultView;
        GridView3.DataBind();

        objConn.Close();
}

Open in new window

richgnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Amandeep Singh BhullarCommented:
MyRange must be the name of Excel Sheet.
Just verify it, i think the name of your excel sheet is different

or try the query as
SELECT * FROM [MyRange$]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kishoreb123Commented:
Hi richgn

I have exactly used your code & am able to generate data without any error. Pl check permissions on the file.
2010-04-24-152843.gif
0
kishoreb123Commented:
Table Naming ConventionsThere are several ways you can reference a table (or range) in an Excel workbook:    * Use the sheet name followed by a dollar sign (for example, [Sheet1$] or [My Worksheet$]). A workbook table that is referenced in this manner includes the whole used range of the worksheet.
      Select * from [Sheet1$]
    * Use a range with a defined name (for example, [MyNamedRange]):
      Select * from [MyNamedRange]
    * Use a range with a specific address (for example, [Sheet1$A1:B10]):
      Select * from [Sheet1$A1:B10]

Please go thru the KB article for details:

http://support.microsoft.com/kb/316934
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.