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";
What I have tried:
when update data in excel sheet to more columns not working why