Click here to Skip to main content
15,867,835 members
Articles / Web Development / IIS
Tip/Trick

The 'Microsoft.Jet.OLEDB.4.0' Provider is Not Registered on the Local Machine

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
11 Sep 2015CPOL1 min read 14.6K   3  
How to fix the "Microsoft.Jet.OLEDB.4.0 provider is not registered on the local machine" error.

Introduction

Generally, most of the time, we work on Excel import functionality in our web application. As per our requirement, we import data from an Excel (.xls or .xlsx) file and read the data from Excel file through OLEDB connection and assign in a datatable. So when I developed the application in my local machine and implemented the Excel import functionality, I did not get any issue in debug time. But when I hosted my application in IIS, I got an error message like "The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine". I Googled it, but I did not get the exact solution  and most of the solutions are related to changing the application build target platform from any CPU to 86. Yes, I also tried a lot with the above solutions, but does not work for me. But when I changed some IIS level configuration, the issue was resolved. Let me explain it briefly.

Using the Code

In the below code, I am just trying to upload one .xls file and trying to fetch the tabular data from Excel file and assigning to a datatable. Let me share my code here.

C#
try
{
       string  FileToConvert = Server.MapPath(".") + "MyImportFile.xls";
    // Checking the file has uploaded by the User or not
    if (fuUploadFile.HasFile)
    {
        fuUploadFile.SaveAs(FileToConvert);
        dtExcelData = new DataTable();
        HDR = hasHeaders ? "Yes" : "No";
       
        // Connection string as per the file type
        if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
        {
            strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
            filePath + ";Extended Properties=\"Excel 12.0;HDR=" + 
            HDR + ";IMEX=0\"";
        }
        else
        {
            strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
            filePath + ";Extended Properties=\"Excel 8.0;HDR=" + 
            HDR + ";IMEX=0\"";
        }
        
        using(OleDbConnection conn = new OleDbConnection(strCon))
        {
            conn.Open();
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 
            new object[] { null, null, null, "TABLE" });
            
            //Looping a first Sheet of Xl File
            DataRow schemaRow = schemaTable.Rows[0];
            string sheet = schemaRow["TABLE_NAME"].ToString();
            
            if (!sheet.EndsWith("_"))
            {
                string query = "SELECT  * FROM [" + sheet + "]";
                OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
                dtExcelData.Locale = CultureInfo.CurrentCulture;
                daexcel.Fill(dtExcelData);
            }
            //conn.Close();
            dtExcelData = dtExcelData.AsEnumerable().Where(row => 
            !row.ItemArray.All(f => f is System.DBNull || String.IsNullOrEmpty(f.ToString())))
                          .CopyToDataTable();
        }
    }
    else
    {
        Trace.Write("ReadFromExcelFile", "No file was uploaded");
    }
}
catch (Exception ex)
{
    // Handle the exception
}

Let Me Share My Error As Well

When I deployed my application in IIS and tried to import a .xls file, I got the below error:

Unhandled Exception in /MyWeb/ExportWeb:
        Type: InvalidOperationException
        Message: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.
Exception stack trace(s):
   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, 
   Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection
   (DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection
   (DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection
   (DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()
Event information:
        Event code: 106660
        Event message: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.
        Event time: 09/11/2015 10:25:07
        Event ID: 7eba2342sdfdf3423423

So Here is the Solution

  1. Just go to the IIS and check your application pool.
  2. Select the application pool and click on the "Advanced Settings" of the selected application pool.
  3. Then change the property "Enable 32-Bit Applications" to True.

Image 1

So after changing it, my import functionality works fine for me.

License

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


Written By
Software Developer Mindfire Solutions
India India
Software engineer in Mindfire Solutions.

Comments and Discussions

 
-- There are no messages in this forum --