?
Solved

The Microsoft Office Access database engine could not find the object 'Sheet1$

Posted on 2012-08-31
8
Medium Priority
?
1,488 Views
Last Modified: 2012-12-07
I keep getting this error when running the Website from the client side:
The Microsoft Office Access database engine could not find the object 'Sheet1$

BUT it works perfect if I run it directly from visual studio 2010 pro????
I am SURE the Sheet1 exists in the excel file....

<?xml version="1.0"?>
<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <system.web>
    <httpHandlers>
      <add path="Reserved.ReportViewerWebControl.axd" verb="*" type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
        validate="false" />
    </httpHandlers>
    <compilation debug="true" targetFramework="4.0">
      <assemblies>
        <add assembly="Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A" />
        <add assembly="Microsoft.ReportViewer.Common, Version=10.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A" />
        <add assembly="Microsoft.Build.Framework, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A" />
        <add assembly="System.Management, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A" />
      </assemblies>
      <buildProviders>
        <add extension=".rdlc" type="Microsoft.Reporting.RdlBuildProvider, Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
      </buildProviders>
    </compilation>
    
  </system.web>

  <connectionStrings>
    
    <add name="app" connectionString="Server = server;Database = app;Integrated Security =SSPI" providerName="System.Data.SqlClient" />
  </connectionStrings>
  <system.webServer>
    <validation validateIntegratedModeConfiguration="false" />
    <handlers>
      <add name="ReportViewerWebControlHandler" preCondition="integratedMode" verb="*" path="Reserved.ReportViewerWebControl.axd" type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
    </handlers>
  </system.webServer>
  <location path="ExistingTimeOffForm.aspx" allowOverride="false">
    <system.web>
      <identity impersonate="true" userName="Domain Name\username" password="password"/>
    </system.web>
  </location>
</configuration>

Open in new window

0
Comment
Question by:pauledwardian
7 Comments
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 38354687
>>the object 'Sheet1$

Is that a typo or is the sheet really called Sheet1$
0
 
LVL 45

Expert Comment

by:AndyAinscow
ID: 38354690
ps.
Check the path is correct to the excel workbook and the sheet really is present.
0
 

Author Comment

by:pauledwardian
ID: 38354728
This is my C# code. It browses to the file:

The one I posted above was my web.config code....


  //file upload path
            string path = fileuploadExcel.PostedFile.FileName;
            //Create connection string to Excel work book
            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
            string connect = ConfigurationManager.ConnectionStrings["TetApp"].ToString();
            SqlConnection sqlConnection1 = new SqlConnection(connect);
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;
            
try 
{






    cmd.CommandText = "ImportToExcel";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = sqlConnection1;

    sqlConnection1.Open();

    reader = cmd.ExecuteReader();
    // Data is accessible through the DataReader object here.

    sqlConnection1.Close();

}
catch (SqlException x)
{
Response.Write(x.Message.ToString());
}
    finally
  {
      sqlConnection1.Close();
      SqlConnection.ClearPool(sqlConnection1);
            }



            //Create Connection to Excel work book
            OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);
            //Create OleDbCommand to fetch data from Excel
            OleDbCommand cmd1 = new OleDbCommand("Select [DepartmentNum],[TypeValue],[EmployeeNum],[FirstName],[LastName],[Balance] from [Sheet1$]", excelConnection);
            excelConnection.Open();
            OleDbDataReader dReader;
            dReader = cmd1.ExecuteReader();
            SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
            //Give your Destination table name
            sqlBulk.DestinationTableName = "Ceredian";
            sqlBulk.WriteToServer(dReader);
            excelConnection.Close();
            RunCeredianSP();
            GridView1.DataBind();
        }
        public void RunCeredianSP()
        {
            string connect = ConfigurationManager.ConnectionStrings["App"].ToString();
            SqlConnection sqlConnection1 = new SqlConnection(connect);
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;

            try
            {






                cmd.CommandText = "CeredianUpdate";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = sqlConnection1;

                sqlConnection1.Open();

                reader = cmd.ExecuteReader();
                // Data is accessible through the DataReader object here.

                sqlConnection1.Close();

            }
            catch (SqlException x)
            {
                Response.Write(x.Message.ToString());
            }
            finally
            {
                sqlConnection1.Close();
                SqlConnection.ClearPool(sqlConnection1);
            }
        }    

Open in new window

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 45

Expert Comment

by:AndyAinscow
ID: 38354774
So the path is correct, the excel file exists, there is a sheet in the file with that name BUT you still get the error that the object couldn't be found.

Wierd - I've no other ideas.
0
 

Author Comment

by:pauledwardian
ID: 38354927
Can Someone PLEASE HELP!!!!!!
0
 
LVL 36

Expert Comment

by:Miguel Oz
ID: 38356440
Can you post Excel filename (path value)?

Please try the following changes:
line 4: I added mode and \".
if (!File.Exists(path))
   throw new Exception("File does not exist or not accessible: " + path);
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Mode=ReadWrite;Extended Properties=\"Excel 12.0;HDR=YES;\";Persist Security Info=False";

Open in new window

lines 45-46: Try * first and then your normal query once it is working
 excelConnection.Open();
 OleDbCommand cmd1 = new OleDbCommand("Select * from [Sheet1$]", excelConnection);
 

Open in new window


Note: If it fails please include sample xlsx file you are trying to read. and in which page context this code is being called.
0
 
LVL 16

Accepted Solution

by:
Vikram Singh Saini earned 1500 total points
ID: 38356448
Cause:

Whenever we open Microsoft Office file, a temporary file is always created there. Same happens in case of excel file too. The file is named with prefix ~$.

So in your scenario the actual error is something different. And I think this could be cause.

By default asp.net runs under context of IUSR_ComputerName (in case of IIS 5.0) or NETWORK SERVICE account (in case of IIS 6.0 or more). These accounts do not have sufficient rights to write files in system's folder.

Why it works from VS 2010?
Because in that case the running account is one by which you have logged on your system. And mostly that account have admin rights.

Solution:
To explain, I am assuming that you are using Default.aspx (in root folder of website) in which you have code to access the excel file. So you would need to enable impersonation for this page in web.config. userName and password should be of Administrative rights.

<location path="Default.aspx" allowOverride="false">
		<system.web>
			<identity impersonate="true" userName="AdminUser" password="AdminPass"/>
		</system.web>
</location>

Open in new window


Hope it would help.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question