We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >
Can not import excel data after deploying ASP.NET website[Answered]
7 replies
Last post Dec 08, 2016 07:35 AM by Mikesdotnetting
Last post Dec 08, 2016 07:35 AM by Mikesdotnetting
Member
1 Points
5 Posts
Can not import excel data after deploying ASP.NET website
Nov 30, 2016 11:01 AM|souidentities|LINK
Hi
I am facing wired problem but so far common in ASP.NET web page. When I run through localhost then it successfuly import data from excel file. But after deploying in a windows server 2012 in IIS then it does not work. All other data come nicely such as all query and data access is fine. But the import does not work.
I have google this problem but their solution is to install Microsoft Access Database Engine 2010 Redistributable. I have installed this and restart but not working. I can not find the problem. Please help me to solve this problem.
I have installed MS Office 2013 andWIndows server 2012 iin my machine. Below I have past my codes.
Thanks in advance
protected void btnImport_Click(object sender, EventArgs e) { try { if (FileUpload1.HasFile) { string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName); string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName); string FolderPath = ConfigurationManager.AppSettings["FolderPath"]; string FilePath = Server.MapPath(FolderPath + FileName); FileUpload1.SaveAs(FilePath); Import_To_Grid(FilePath, Extension); } } catch (Exception ex) { lblError.Text = ("Error:" + "Select appropritae criteria"); } finally { Label1.Text = "Database import Successfully"; Create_DB(); UpdateTime(); Fn.CloseConn(); } } private void Import_To_Grid(string FilePath, string Extension) { int totalRow = 0, index = 0; string conStr = "", Col1, Col2, Col3, Col4, Col5; SqlCommand cmdSqlUpdate; switch (Extension) { case ".xls": //Excel 97-03 conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"] .ConnectionString; break; case ".xlsx": //Excel 07 conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"] .ConnectionString; break; } conStr = String.Format(conStr, FilePath, "Yes"); OleDbConnection connExcel = new OleDbConnection(conStr); OleDbCommand cmdExcel = new OleDbCommand(); OleDbDataAdapter oda = new OleDbDataAdapter(); DataTable dt = new DataTable(); cmdExcel.Connection = connExcel; //Get the name of First Sheet connExcel.Open(); DataTable dtExcelSchema; dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString(); connExcel.Close(); //Read Data from First Sheet connExcel.Open(); cmdExcel.CommandText = "SELECT * From [" + SheetName + "]"; oda.SelectCommand = cmdExcel; oda.Fill(dt); totalRow = dt.Rows.Count; Col1 = dt.Columns[1].ColumnName; Col2 = dt.Columns[2].ColumnName; Col3 = dt.Columns[3].ColumnName; Col4 = dt.Columns[4].ColumnName; Col5 = dt.Columns[5].ColumnName; Col6 = dt.Columns[6].ColumnName; Fn.CreateConn(); foreach (DataRow row in dt.Rows) { index = dt.Rows.IndexOf(row); if (index >= 2) { cmdSqlUpdate = new SqlCommand(@"INSERT INTO TrackReport(EmpID, Name, MobileNumber, Department, Address, Time) VALUES('" + row[Col1].ToString() + "','" + row[Col2].ToString() + "'," + row[Col3].ToString() + ",'" + row[Col4].ToString() + "','" + row[Col5].ToString().Replace("'", "") + "','" + row[Col6].ToString() + "')", Fn.cnDB); cmdSqlUpdate.ExecuteNonQuery(); } } connExcel.Close(); Fn.CloseConn(); }All-Star
194881 Points
28101 Posts
Moderator
Re: Can not import excel data after deploying ASP.NET website
Nov 30, 2016 11:06 AM|Mikesdotnetting|LINK
Problem is that you have hidden any information about exceptions in a try-catch block and replaced it with your own error message, which won't always be accurate.
There are two versions of the 2010 engine. One is 32 bit and the other is 64 bit. If you have installed the 32 bit version, you need to enable 32 bit applications for your application pool.
http://www.mikesdotnetting.com/article/280/solved-the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine
Member
1 Points
5 Posts
Re: Can not import excel data after deploying ASP.NET website
Dec 04, 2016 06:32 AM|souidentities|LINK
I got the error. I have searched for this and got the recommended solution. But Fail. I have installed Office 2013 64bit and AccessDatabaseEngine 2010 64 bit and windows server 2008 64 bit. But still same error. The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.
Please help me .thanks.
Error:
Error:System.InvalidOperationException: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine. at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper) at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at Import.Import_To_Grid(String FilePath, String Extension) in e:\ROSC II\ROSC\ROSC Soft\LGED-Attendance\Import.aspx.cs:line 130
My code is below
protected void btnImport_Click(object sender, EventArgs e) { cleare_DB(); try { if (FileUpload1.HasFile) { string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName); string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName); string FolderPath = ConfigurationManager.AppSettings["FolderPath"]; string FilePath = Server.MapPath(FolderPath + FileName); FileUpload1.SaveAs(FilePath); Import_To_Grid(FilePath, Extension); } } catch (Exception ex) { lblError.Text = ("Error:" + ex); } finally { Label1.Text = "Database import Successfully"; Create_DB(); UpdateTime(); Fn.CloseConn(); } } private void Import_To_Grid(string FilePath, string Extension) { int totalRow = 0, index = 0; string conStr = "", Col1, Col2, Col3, Col4, Col5; SqlCommand cmdSqlUpdate; switch (Extension) { case ".xls": //Excel 97-03 conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"] .ConnectionString; break; case ".xlsx": //Excel 07 conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"] .ConnectionString; break; } conStr = String.Format(conStr, FilePath, "Yes"); OleDbConnection connExcel = new OleDbConnection(conStr); OleDbCommand cmdExcel = new OleDbCommand(); OleDbDataAdapter oda = new OleDbDataAdapter(); DataTable dt = new DataTable(); cmdExcel.Connection = connExcel; //Get the name of First Sheet connExcel.Open(); DataTable dtExcelSchema; dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString(); connExcel.Close(); //Read Data from First Sheet connExcel.Open(); cmdExcel.CommandText = "SELECT * From [" + SheetName + "]"; oda.SelectCommand = cmdExcel; oda.Fill(dt); totalRow = dt.Rows.Count; Col1 = dt.Columns[1].ColumnName; Col2 = dt.Columns[2].ColumnName; Col3 = dt.Columns[3].ColumnName; Col4 = dt.Columns[4].ColumnName; Col5 = dt.Columns[5].ColumnName; Col6 = dt.Columns[6].ColumnName; Fn.CreateConn(); foreach (DataRow row in dt.Rows) { index = dt.Rows.IndexOf(row); if (index >= 2) { cmdSqlUpdate = new SqlCommand(@"INSERT INTO TrackReport(EmpID, Name, MobileNumber, Department, Address, Time) VALUES('" + row[Col1].ToString() + "','" + row[Col2].ToString() + "'," + row[Col3].ToString() + ",'" + row[Col4].ToString() + "','" + row[Col5].ToString().Replace("'", "") + "','" + row[Col6].ToString() + "')", Fn.cnDB); cmdSqlUpdate.ExecuteNonQuery(); } } connExcel.Close(); Fn.CloseConn(); }All-Star
194881 Points
28101 Posts
Moderator
Re: Can not import excel data after deploying ASP.NET website
Dec 05, 2016 07:28 AM|Mikesdotnetting|LINK
Your connection string is trying to use the Jet provider. You should change that to the ACE provider:
Provider=Microsoft.Ace.Oledb.12.0;Data Source=...
Member
1 Points
5 Posts
Re: Can not import excel data after deploying ASP.NET website
Dec 06, 2016 05:20 AM|souidentities|LINK
I have used for two types of Excel version. Most of the time I will be using Excel version 97-03 Old version. Extension is .xls. But still it shows the Error--The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine
My web.config page is below
<!-- For more information on how to configure your ASP.NET application, please visit http://go.microsoft.com/fwlink/?LinkId=169433 --> <configuration> <configSections> <sectionGroup name="devExpress"> <section name="themes" type="DevExpress.Web.ASPxClasses.ThemesConfigurationSection, DevExpress.Web.v12.1, Version=12.1.12.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" requirePermission="false"/> <section name="compression" type="DevExpress.Web.ASPxClasses.CompressionConfigurationSection, DevExpress.Web.v12.1, Version=12.1.12.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" requirePermission="false"/> <section name="settings" type="DevExpress.Web.ASPxClasses.SettingsConfigurationSection, DevExpress.Web.v12.1, Version=12.1.12.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" requirePermission="false"/> <section name="errors" type="DevExpress.Web.ASPxClasses.ErrorsConfigurationSection, DevExpress.Web.v12.1, Version=12.1.12.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" requirePermission="false"/> </sectionGroup> </configSections> <appSettings> <add key="strBaseDir" value="E:\ROSC II\ROSC\Images"/> <add key="strRootDir" value="D:\Omni\Rosc\Rosc"/> <add key="strACFDir" value="D:\Omni\Rosc\RoscACF"/> <add key="strAcfSumDir" value="E:\ROSC Summary\ACF Summary"/> <add key="ClientSettingsProvider.ServiceUri" value=""/> <add key ="FolderPath" value ="Excel File/"/> </appSettings> <connectionStrings> <add name="ApplicationServices" connectionString="Data Source=172.16.111.205;Initial Catalog=Rosc;User ID=sa;Password=------" providerName="System.Data.SqlClient"/> <add name="cnDB" connectionString="Data Source=172.16.111.205;Initial Catalog=LGED-Attendance;User ID=rmc;Password=-----" providerName="System.Data.SqlClient"/> <add name="cnDB1" connectionString="Data Source=172.16.111.205;Initial Catalog=ROSC;User ID=sa;Password=-----" providerName="System.Data.SqlClient"/> <add name="cnDB2" connectionString="Data Source=172.16.111.205;Initial Catalog=M-Reporting;User ID=sa;Password=----" providerName="System.Data.SqlClient"/> <add name="M-Reporting" connectionString="Data Source=172.16.111.205;Initial Catalog=M-Reporting;User ID=sa;Password=----" providerName="System.Data.SqlClient"/> <!--Excell 97-03--> <add name ="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/><!--Excel 2007--> <add name ="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/> </connectionStrings> <system.webServer> <modules runAllManagedModulesForAllRequests="true"> <add type="DevExpress.Web.ASPxClasses.ASPxHttpHandlerModule, DevExpress.Web.v12.1, Version=12.1.12.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" name="ASPxHttpHandlerModule"/> </modules> <validation validateIntegratedModeConfiguration="false"/> <handlers> <add type="DevExpress.Web.ASPxClasses.ASPxHttpHandlerModule, DevExpress.Web.v12.1, Version=12.1.12.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" verb="GET" path="DX.ashx" name="ASPxHttpHandlerModule" preCondition="integratedMode"/> </handlers> </system.webServer> <system.web> <compilation debug="true" targetFramework="4.0"> <assemblies> <add assembly="DevExpress.Web.ASPxGridView.v12.1, Version=12.1.12.0, Culture=neutral, PublicKeyToken=B88D1754D700E49A"/> <add assembly="DevExpress.Web.ASPxEditors.v12.1, Version=12.1.12.0, Culture=neutral, PublicKeyToken=B88D1754D700E49A"/> <add assembly="DevExpress.Web.v12.1, Version=12.1.12.0, Culture=neutral, PublicKeyToken=B88D1754D700E49A"/> <add assembly="DevExpress.Data.v12.1, Version=12.1.12.0, Culture=neutral, PublicKeyToken=B88D1754D700E49A"/> <add assembly="System.Windows.Forms, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/> <add assembly="System.Design, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/> <add assembly="System.Data.Linq, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/> <add assembly="mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089"/></assemblies> </compilation> <httpModules> <add type="DevExpress.Web.ASPxClasses.ASPxHttpHandlerModule, DevExpress.Web.v12.1, Version=12.1.12.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" name="ASPxHttpHandlerModule"/> </httpModules> <httpHandlers> <add type="DevExpress.Web.ASPxClasses.ASPxHttpHandlerModule, DevExpress.Web.v12.1, Version=12.1.12.0, Culture=neutral, PublicKeyToken=b88d1754d700e49a" verb="GET" path="DX.ashx" validate="false"/> </httpHandlers> </system.web> <devExpress> <themes enableThemesAssembly="true" styleSheetTheme="" theme=""/> <compression enableHtmlCompression="false" enableCallbackCompression="true" enableResourceCompression="true" enableResourceMerging="true"/> <settings rightToLeft="false"/> <errors callbackErrorRedirectUrl=""/> </devExpress> </configuration>
All-Star
194881 Points
28101 Posts
Moderator
Re: Can not import excel data after deploying ASP.NET website
Dec 06, 2016 07:34 AM|Mikesdotnetting|LINK
Member
1 Points
5 Posts
Re: Can not import excel data after deploying ASP.NET website
Dec 08, 2016 04:17 AM|souidentities|LINK
Thanks Mikesdotnet. It works.
But I also needed to change the IIS App pool setting to allow 32 bit excel files.
All-Star
194881 Points
28101 Posts
Moderator
Re: Can not import excel data after deploying ASP.NET website
Dec 08, 2016 07:35 AM|Mikesdotnetting|LINK