salam
i want to import data from excel to sql server but there is a condition if the data already axist i will make an update else i insert the new data and here is my code
string srvxl = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\Suivi\export1111.xlsx;Extended Properties="+"\"Excel 12.0 Xml;HDR=Yes;\"";
public void updateinsertsql(string path)
{
SqlConnection cn = new SqlConnection(srv);
OleDbConnection cnn=new OleDbConnection(srvxl);
cn.Open();
cnn.Open();
string cmdd = @"select v.Num_Doss from [Visit] v inner join OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;IMEX=1\;Data Source=d:\\Suivi\\export1111.xlsx;','SELECT Num_Doss FROM [sheet1$]')AS ExcelTable on d.Num_Doss=ExcelTable.Num_Doss";
SqlCommand verif = new SqlCommand(cmdd, cn);
SqlDataReader logread = verif.ExecuteReader();
if (logread.HasRows)
{
while (logread.Read())
{
string update = @"UPDATE [Visit]
SET [Visit].Date_visit_1mois_prevu=exl.Date_visit_1mois_prevu,
[Visit].Date_visit_1mois=exl.Date_visit_1mois,
[Visit].Observation_visite_1mois=exl.Observation_visite_1mois,
[Visit].Date_visit_3mois_prevu=exl.Date_visit_3mois_prevu,
[Visit].Date_visit_3mois=exl.Date_visit_3mois,
[Visit].Observation_visite_3mois=exl.Observation_visite_3mois,
[Visit].Date_visit_6mois_prevu=exl.Date_visit_6mois_prevu,
[Visit].Date_visit_6mois=exl.Date_visit_6mois,
[Visit].Observation_visite_6mois=exl.Observation_visite_6mois,
[Visit].Date_visit_1an_prevu=exl.Date_visit_1an_prevu,
[Visit].Date_visit_1an=exl.Date_visit_1an,
[Visit].Observation_visite_1an=exl.Observation_visite_1an,
FROM [Visit]
INNER JOIN OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Data Source=C:\Suivi\export.xls;','SELECT Date_visit_1mois_prevu,Date_visit_1mois,Observation_visite_1mois,Date_visit_3mois_prevu,Date_visit_3mois,Observation_visite_3mois,Date_visit_6mois_prevu,Date_visit_6mois,Observation_visite_6mois,Date_visit_1an_prevu,Date_visit_1an,Observation_visite_1an FROM [sheet1$]') AS exlON [Visit].Num_Doss = exl.Num_Doss
WHERE [Visit].Num_Doss=" + logread.GetString(0) + "";
SqlCommand up = new SqlCommand(update, cn);
up.ExecuteNonQuery();
MessageBox.Show("mise à jour éffectué avec succée");
}
}
else
{
string insrt = @"insert into [Visit] Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Data Source=D:\Suivi\export1111.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')";
SqlCommand ins = new SqlCommand(insrt, cn);
ins.ExecuteNonQuery();
MessageBox.Show("insertion éffectué avec succés");
}
}
but when i run my code i get this error:
The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.
here
SqlDataReader logread = verif.ExecuteReader();
help plz