I am getting the following error: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
while I am trying to read an excel file and than to populate it in GridView
, within a VisualWebPart
I have found some solutions while googling my error, one of these was to install 2007 Office System Driver: Data Connectivity Components.
I installed this but still not working also I installed this one: Microsoft Access Database Engine 2010 Redistributable
but again it is not working.
I saw some posts on some forums that I have to change the application platform from x64
to x86
but I cannot do this because sharepoint runs only x64
apps.
I'm using 32bit Office Professional Plus
installed on my E drive
while system is in C drive
because of space.
I have the following code:
protected void btnUpload_Click(object sender, EventArgs e)
{
string connectionString = "";
if (FileUpload1.HasFile)
{
string fileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string fileExtension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string fileLocation = System.Web.HttpContext.Current.Server.MapPath("~/App_GlobalResources/" + fileName);
FileUpload1.SaveAs(fileLocation);
//Check whether file extension is xls or xslx
if (fileExtension == ".xls")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (fileExtension == ".xlsx")
{
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create OleDB Connection and OleDb Command
OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = con;
OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
DataTable dtExcelRecords = new DataTable();
con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
dAdapter.SelectCommand = cmd;
dAdapter.Fill(dtExcelRecords);
con.Close();
GridView1.DataSource = dtExcelRecords;
GridView1.DataBind();
}
}
Please someone help me.