I am getting the following issue while importing the excel into database.
https://ibb.co/c4CL2H
An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll
Additional information: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.
private void uploadUsingExcel()
{
int excelRows;
OleDbConnection ExcelConnection;
OleDbDataAdapter da;
DataSet ds;
string filename = txtUploadFlNm.Text.Trim();
string fileExtension = filename.Substring(filename.Length - 4, 4);
try
{
//string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=Excel 5.0";
//ExcelConnection = new OleDbConnection(ConnectionString);
string excelConnectionString=null;
if (fileExtension == ".xls")
{
excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=Excel 5.0";
}
//connection String for xlsx file format.
else if (fileExtension == "xlsx")
{
excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
filename + ";Extended Properties=Excel 12.0";
}
ExcelConnection = new OleDbConnection(excelConnectionString);
// if (ExcelConnection.State == ConnectionState.Closed)
//{
// ExcelConnection.Open();
//}
string ExcelQuery = "Select * from [Sheet1$] where [companyname] IS NOT NULL or [website] IS NOT NULL or [country] IS NOT NULL "; // from Sheet1";
ds = new DataSet();
da = new OleDbDataAdapter(ExcelQuery, ExcelConnection);
da.Fill(ds, "TEMP");
DataTable tblWithBadEml = ds.Tables["TEMP"];
DataTable cleanedEmailTbl = new DataTable();
DataTable onlyBadEmailTbl = new DataTable();
cleanedEmailTbl = ds.Tables["TEMP"].Clone();
onlyBadEmailTbl = ds.Tables["TEMP"].Clone();
Boolean email1Exist = tblWithBadEml.Columns.Contains("email1");
Boolean email2Exist = tblWithBadEml.Columns.Contains("email2");
string email1 = null;
string email2 = null;
int e1 = 0;
int e2 = 0;
for (int i = 0; i < tblWithBadEml.Rows.Count; i++)
{
string email = Convert.ToString(tblWithBadEml.Rows[i]["email"]).Trim();
if (email1Exist)
{
email1 = Convert.ToString(tblWithBadEml.Rows[i]["email1"]).Trim();
if (email1 != "")
{
e1++;
}
}
if (email2Exist)
{
email2 = Convert.ToString(tblWithBadEml.Rows[i]["email2"]).Trim();
if (email2 != "")
{
e2++;
}
}
if ((validEmail(email) == true) && (email1Exist && ((validEmail(email1) && email1 != "") || email1 == "")) && (email2Exist && ((validEmail(email2) && email2 != "") || email2 == "")))
{
DataRow dr = tblWithBadEml.Rows[i];
DataRow newRow = cleanedEmailTbl.NewRow();
newRow.ItemArray = dr.ItemArray;
cleanedEmailTbl.Rows.Add(newRow);
}
else
{
DataRow dr1 = tblWithBadEml.Rows[i];
DataRow newRow1 = onlyBadEmailTbl.NewRow();
newRow1.ItemArray = dr1.ItemArray;
onlyBadEmailTbl.Rows.Add(newRow1);
}
}
excelRows = Convert.ToInt32(cleanedEmailTbl.Rows.Count);
int ExcelColumnCount = Convert.ToInt32(cleanedEmailTbl.Columns.Count);
try
{
MyConnect myCnn = new MyConnect();
String connString = myCnn.getConnect().ToString(); //call from class
SqlDataAdapter adapter;
SqlTransaction transaction;
DataSet ds1 = new DataSet();
SqlConnection conn=null;
conn = new SqlConnection(connString);
conn.Open();
transaction = conn.BeginTransaction();
SqlCommand command = new SqlCommand();
command.Transaction = transaction;
StringBuilder XML_TRANSACTIONDETAILS = new StringBuilder();
string guid = Guid.NewGuid().ToString().Replace("-", "");
string dataID;
string today = DateTime.Now.ToString();
int m = 1;
XML_TRANSACTIONDETAILS.Append("<master>");
for (int i = 0; i < excelRows; i++)
{
m = m + 1;
dataID = guid + "-" + today+m.ToString();
XML_TRANSACTIONDETAILS.Append("<TRANSACTION>");
XML_TRANSACTIONDETAILS.Append("<maxid>" + SecurityElement.Escape(dataID) + "</maxid>");
for (int j = 0; j < ExcelColumnCount; j++)
{
string xmlTag = cleanedEmailTbl.Columns[j].ToString();
XML_TRANSACTIONDETAILS.Append("<" + xmlTag + ">" + SecurityElement.Escape(cleanedEmailTbl.Rows[i][j].ToString().Trim()) + "</" + xmlTag + ">");
}
XML_TRANSACTIONDETAILS.Append("</TRANSACTION>");
}
XML_TRANSACTIONDETAILS.Append("</master>");
ExpoBusy.Visible = true;
ExpoBusy.Focus();
panel2.Visible = true;
// panel2.Enabled = true;
ExpoBusy.ForeColor = Color.Red;
// ExpoBuisy.Enabled = true;
panel1.Enabled = false;
this.Cursor = Cursors.WaitCursor;
SqlParameter prm = new SqlParameter("@XML_TRANSACTIONDETAILS", SqlDbType.NText);
SqlParameter prm1 = new SqlParameter("@status", cmbAllStatus.SelectedValue);
SqlParameter prm2 = new SqlParameter("@username", currentLoggedUserId);
SqlParameter prm3 = new SqlParameter("@urlorcatalog", tboxUrlCat.Text.Trim());
SqlParameter prm4 = new SqlParameter("@dataentrytype", cmbDataEntryType.Text.Trim());
SqlParameter prm5 = new SqlParameter("@exhibitor", txtExibitorList.Text.Trim());
prm.Value = XML_TRANSACTIONDETAILS.ToString();
try
{
command.Connection = conn;
command.CommandText = "InsertExcelUsingXmlExhibitorListNew";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(prm);
command.Parameters.Add(prm1);
command.Parameters.Add(prm2);
command.Parameters.Add(prm3);
command.Parameters.Add(prm4);
command.Parameters.Add(prm5);
adapter = new SqlDataAdapter(command);
adapter.Fill(ds1, "rowsinserted");
transaction.Commit();
this.Cursor = Cursors.Default;
panel1.Enabled = true;
ExpoBusy.Visible = false;
// string duplicateemails = Convert.ToString(ds1.Tables["rowsinserted"].Rows[0][0]);
int duplicateemails = Convert.ToInt32(ds1.Tables["rowsinserted"].Rows[0][0]);
int totalemails=Convert.ToInt32(Convert.ToInt32(tblWithBadEml.Rows.Count)+(e1+e2));
DialogResult result;
if (onlyBadEmailTbl.Rows.Count == 0)
{
int totalNewCompanies = totalemails - Convert.ToInt32(ds1.Tables["rowsinserted"].Rows[0][0]);
saveindatalog(totalemails, totalNewCompanies);
MessageBox.Show("Total Companies(emails): " + totalemails + "\nNew Companies(emails): " + totalNewCompanies + "\nDuplicates :" + duplicateemails + "", "", MessageBoxButtons.OKCancel);
}
else
{
int totalNewCompanies =totalemails - Convert.ToInt32(ds1.Tables["rowsinserted"].Rows[0][0]) - Convert.ToInt32(onlyBadEmailTbl.Rows.Count);
saveindatalog(totalemails, totalNewCompanies);
int id = CustomMessageBox.ShowBox("Total Companies(emails): " + totalemails + "\nDuplicates :" + duplicateemails + "\nBad Emails: " + onlyBadEmailTbl.Rows.Count.ToString() + "\nclick view to save/view bad Email", "Expogroup");
if (id==2)
{
ExportDTToExcel(onlyBadEmailTbl);
}
}
conn.Close();
}
catch (Exception ex1)
{
transaction.Rollback();
MessageBox.Show("Unable to add " + ex1.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
this.Cursor = Cursors.Default;
panel1.Enabled = true;
ExpoBusy.Visible = false;
}
}
catch (Exception)
{ }
}
finally
{
}
}