Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-08-29
3
Medium Priority
?
643 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????

This is my 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
Comment
Question by:pauledwardian
3 Comments
 
LVL 20

Expert Comment

by:informaniac
ID: 38347395
Have you ensured that your excel file contains the sheet named Sheet1 when running from client side?
0
 
LVL 16

Accepted Solution

by:
Vikram Singh Saini earned 1500 total points
ID: 38348683
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????


Reason:
By default IIS worker process runs under IUSR_ComputerName account (in case of IIS 5.0) or NETWORK SERVICE account (in case of IIS 6.0 or more).

When excel files are opened a temporary file with prefix ~$ is created in same folder where excel file exists. And the above mentioned accounts do not have rights to create any temporary files there.

Solution:
You would need to impersonate some admin account which have read and write permission for that folder.

For e.g., if your hosting system has administrator user named "x" and its password is "y". You are opening the excel file from page Default.aspx in root folder. So for this scenario impersonation would be implemented in web.config file as:

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

Open in new window

0
 

Author Comment

by:pauledwardian
ID: 38351128
didnt work!

<?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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

810 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