I have excel sheet name Allprinting
contain to UserID,UserName,UserNameAR,Country,CountryAR,Member,MemberAR columns
I need to make update data in this excel sheet if client change any data in these columns
(UserID,UserName,UserNameAR,Country,CountryAR,Member,MemberAR)
but bellow code not working for me
so that what is the problem
my code
- var fileName = string.Format("{0}\\Book502", Directory.GetCurrentDirectory());
- var connection = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source={0};Mode=ReadWrite;Extended Properties=Excel 12.0 Xml;", fileName);
- try
- {
- System.Data.OleDb.OleDbConnection MyConnection;
- System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
- string sql = null;
- MyConnection = new System.Data.OleDb.OleDbConnection(connection);
- MyConnection.Open();
- myCommand.Connection = MyConnection;
- sql = "Update [AllPrinting$] SET UserName=@UserName and UserNameAR=@UserNameAR and Country=@Country and CountryAR=@CountryAR AND Member=@Member AND MemberAR=@MemberAR WHERE UserID=@UserID";
-
- myCommand.CommandText = sql;
- myCommand.Parameters.AddWithValue("@UserName", textBox2.Text);
- myCommand.Parameters.AddWithValue("@UserID", textBox1.Text);
- myCommand.Parameters.AddWithValue("@UserNameAR", textBox3.Text);
- myCommand.Parameters.AddWithValue("@Country", textBox4.Text);
- myCommand.Parameters.AddWithValue("@CountryAR", textBox5.Text);
- myCommand.Parameters.AddWithValue("@Member", textBox6.Text);
- myCommand.Parameters.AddWithValue("@MemberAR", textBox7.Text);
- myCommand.ExecuteNonQuery();
- MyConnection.Close();
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.ToString());
- }
I can update data in excel sheet if i make update to one column
- sql = "Update [AllPrinting$] SET UserName=@UserName and UserNameAR=@UserNameAR WHERE UserID=@UserID";
But update data to more column not working as following - sql = "Update [AllPrinting$] SET UserName=@UserName and UserNameAR=@UserNameAR and Country=@Country and CountryAR=@CountryAR AND Member=@Member AND MemberAR=@MemberAR WHERE UserID=@UserID";