“Data type mismatch in criteria expression” error in Access SQL query from C#

2
votes

I am getting below Error, When I put Multiple Conditions with WHERE Clause. Error:- Data type mismatch in criteria expression.

My Query:-

this.query = "UPDATE [Attendance] SET [TimeOut]='" + DateTime.Now.ToShortTimeString() + "' WHERE [Emp_Id]='" + txtEmpId.Text + "'and[Date]='" + this.Date + "'";
1
c#ms-access
Do those date fields need # signs? Look into using parameterized queries. This might help: stackoverflow.com/questions/20242896/… - sgeddes

1 Answers

1
votes

Access SQL tends to be rather flexible when accepting Date/Time values as strings. However, since you really should be using a parameterized query anyway because

  • they're safer (by avoiding SQL Injection issues),
  • you don't have to mess with delimiters for date and text values,
  • you don't have to worry about escaping quotes within text values, and
  • they handle dates properly so your code doesn't mangle dates on machines set to dd-mm-yyyy format,

consider using the following approach

this.query = "UPDATE [Attendance] SET [TimeOut]=? WHERE [Emp_Id]=? AND [Date]=?";
cmd.CommandText = this.query;
cmd.Parameters.AddWithValue("?", DateTime.Now.ToString("H:mm:ss"));
cmd.Parameters.AddWithValue("?", txtEmpId.Text);
cmd.Parameters.AddWithValue("?", this.Date);
cmd.ExecuteNonQuery();