Click here to Skip to main content
15,894,539 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 where v.Num_Doss="+logread.GetString(0)+"";
                            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:
C#
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
Posted

1 solution

 
Share this answer
 
v2
Comments
sidali.kadi 30-Nov-15 4:53am    
you mean i add the dll file as reference in my project?
Mehdi Gholam 30-Nov-15 5:00am    
Yes, and change the excel code to use epplus, the code samples are on the site.
sidali.kadi 30-Nov-15 5:18am    
sorry but i didn't found somthing helpe there hope that you understood what i wanted is to import from excel file data with an update for anexisting data
Mehdi Gholam 30-Nov-15 5:22am    
See the updated solution.
sidali.kadi 30-Nov-15 5:33am    
sorry man but can you give me link.bcz i searched all the web site i didn't found what can help me...sorry again

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900