3
Reply

when update data in excel sheet to more columns not working

ahmed salah

ahmed salah

Jan 31 2017 11:22 PM
200

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

  1. var fileName = string.Format("{0}\\Book502", Directory.GetCurrentDirectory());  
  2. var connection = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source={0};Mode=ReadWrite;Extended Properties=Excel 12.0 Xml;", fileName);  
  3. try  
  4. {  
  5. System.Data.OleDb.OleDbConnection MyConnection;  
  6. System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();  
  7. string sql = null;  
  8. MyConnection = new System.Data.OleDb.OleDbConnection(connection);  
  9.                 MyConnection.Open();  
  10.                 myCommand.Connection = MyConnection;  
  11.             sql = "Update [AllPrinting$] SET UserName=@UserName and UserNameAR=@UserNameAR and Country=@Country and CountryAR=@CountryAR AND Member=@Member AND MemberAR=@MemberAR WHERE UserID=@UserID";  
  12.              
  13.             myCommand.CommandText = sql;  
  14.                 myCommand.Parameters.AddWithValue("@UserName", textBox2.Text);  
  15.                 myCommand.Parameters.AddWithValue("@UserID", textBox1.Text);  
  16.                myCommand.Parameters.AddWithValue("@UserNameAR", textBox3.Text);  
  17.             myCommand.Parameters.AddWithValue("@Country", textBox4.Text);  
  18.             myCommand.Parameters.AddWithValue("@CountryAR", textBox5.Text);  
  19.             myCommand.Parameters.AddWithValue("@Member", textBox6.Text);  
  20.             myCommand.Parameters.AddWithValue("@MemberAR", textBox7.Text);  
  21.             myCommand.ExecuteNonQuery();  
  22.                 MyConnection.Close();  
  23.             }  
  24.             catch (Exception ex)  
  25.             {  
  26.                 MessageBox.Show(ex.ToString());  
  27.             }  
I can update data in excel sheet if i make update to one column
  1. sql = "Update [AllPrinting$] SET UserName=@UserName and UserNameAR=@UserNameAR WHERE UserID=@UserID";  
But update data to more column not working as following 
  1. sql = "Update [AllPrinting$] SET UserName=@UserName and UserNameAR=@UserNameAR and Country=@Country and CountryAR=@CountryAR AND Member=@Member AND MemberAR=@MemberAR WHERE UserID=@UserID";  
 
 

Answers (3)