when update data in excel sheet to more columns not working

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";

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke9y ago
If my reply has helped resolve it, please accept it as an answer. Thanks!
ahmed salah9y ago
Thank you it solved
Nitin Sontakke9y ago
I think there is a syntax error in your update statement. It should have been as follows, I guess:

sql = "Update [AllPrinting$] SET UserName = @UserName, UserNameAR = @UserNameAR, Country = @Country, CountryAR = @CountryAR, Member = @Member, MemberAR = @MemberAR WHERE UserID = @UserID";

Hope it helps!