I am using Oledb to connect with an excel sheet. My connection is working fine. Insert and retrieve data are also working fine. But when I try to update a record in excel sheet with where clause then my code executes successfully without any error but excel row does not update.
My code is:
strQry = @"Update [Guests$] set [FirstName]=@FirstName,[LastName]=@LastName,[Address]=@Address,
[EmailId]=@EmailId,[TelNo]=@TelNo,[MobileNo]=@MobileNo,[FaxNo]=@FaxNo where [GuestId]=@GuestId";
using (OleDbConnection con = new OleDbConnection(clsConnection.conStr))
{
using (OleDbCommand cmd = new OleDbCommand(strQry,con))
{
cmd.Parameters.Add("@GuestId", OleDbType.Integer).Value = intId;
cmd.Parameters.Add("@FirstName", OleDbType.VarChar, 15).Value = txtFirstName.Text;
cmd.Parameters.Add("@LastName", OleDbType.VarChar, 15).Value = txtLastName.Text;
cmd.Parameters.Add("@Address", OleDbType.VarChar, 200).Value = txtAddress.Text;
cmd.Parameters.Add("@EmailId", OleDbType.VarChar, 50).Value = txtEmail.Text;
cmd.Parameters.Add("@TelNo", OleDbType.VarChar, 20).Value = txtTelNo.Text;
cmd.Parameters.Add("@MobileNo", OleDbType.VarChar, 15).Value = txtMobileNo.Text;
cmd.Parameters.Add("@FaxNo", OleDbType.VarChar, 20).Value = txtFaxNo.Text;
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.ExecuteNonQuery();
MessageBox.Show(strSuccessMsg);
BinddgvGuests();
ClearControls();
}
}
But when I remove the where clause then all the records are updated.
Is there something wrong with my where clause?
After searching a lot, finally I encounter my mistake. Actually parameters should be in same sequence as in the query. So I change my parameters to:
cmd.Parameters.Add("@FirstName", OleDbType.VarChar, 15).Value = txtFirstName.Text;
cmd.Parameters.Add("@LastName", OleDbType.VarChar, 15).Value = txtLastName.Text;
cmd.Parameters.Add("@Address", OleDbType.VarChar, 200).Value = txtAddress.Text;
cmd.Parameters.Add("@EmailId", OleDbType.VarChar, 50).Value = txtEmail.Text;
cmd.Parameters.Add("@TelNo", OleDbType.VarChar, 20).Value = txtTelNo.Text;
cmd.Parameters.Add("@MobileNo", OleDbType.VarChar, 15).Value = txtMobileNo.Text;
cmd.Parameters.Add("@FaxNo", OleDbType.VarChar, 20).Value = txtFaxNo.Text;
cmd.Parameters.Add("@GuestId", OleDbType.Integer).Value = intId;
Now this is working fine for me.