Simple SQL Update statements occassionally working in MS Access?

Question

Problem I have is when I run a SQL UPDATE on different fields, but the same WHERE criteria in my SQL statement one produces a change, and the other does not.

This produces no rows affected:

 DateTime now = DateTime.Now;
 OleDbCommand cmd = new OleDbCommand("UPDATE shifts SET end_log=@end_log WHERE profile_id=@profile_id;");
 cmd.Parameters.AddWithValue("@profile_id", profileID);  // profileID is a string
 cmd.Parameters.AddWithValue("@end_log", now.ToString());

Whereas if I ran this, one row is affected:

 OleDbCommand cmd = new OleDbCommand("UPDATE shifts SET closing=true WHERE profile_id=@profile_id;");
 cmd.Parameters.AddWithValue("@profile_id", profileID);

My shifts table has the following fields:

profile_id - Short Text
end_log - Date/Time
closed - Yes/No

You can assume the tables hold the same data in both instances (this is automatically loaded and only contains one record).

Anyone spot any errors?

end_log=@end_log is almost certainly the issue. print out the error and then you will likely need to fuss with the date formatting. — Randy, Aug 01 '14 at 12:28
Your first query doesn't update in your database manager? I don't feel I understand your question clearly. — Soner Gönül, Aug 01 '14 at 12:30
Soner my first query doesn't update, whereas my second does. Randy I use the exact same "now.ToString()" in a INSERT INTO statement that works fine. In fact, I just tried inserting into end_notes field a string, since the field is defined as long text, but no dice as well. At a loss here. Overmind it has something to do with this: http://stackoverflow.com/questions/7522924/inserting-a-date-time-value-in-access-using-an-oledbparameter — YouAreSalty, Aug 01 '14 at 12:36
Can you specify that the datatype is of DateTime instead of string? Something like this: cmd.AddParameter("ExpiryDate", DbType.DateTime, 0, ParameterDirection.Input, now); — Patrick Developer, Aug 01 '14 at 14:19

score 0 · Accepted Answer · answered Aug 01 '14 at 13:18

While using OLEDB provider order of parameters is important.

Instead of

OleDbCommand cmd = new OleDbCommand("UPDATE shifts SET end_log=@end_log WHERE profile_id=@profile_id;");
cmd.Parameters.AddWithValue("@profile_id", profileID);  // profileID is a string
cmd.Parameters.AddWithValue("@end_log", now.ToString());

try

OleDbCommand cmd = new OleDbCommand("UPDATE shifts SET end_log=@end_log WHERE profile_id=@profile_id;");
cmd.Parameters.AddWithValue("@end_log", now.ToString());  
cmd.Parameters.AddWithValue("@profile_id", profileID);  // profileID is a string

Order in which parameters are added to Parameters collection should match order in which parameters appear in the query.

I feel like a total idiot right now. It works! I know that for parameters using the `?` notation required a set order, but assumed the `@` notation circumvented that, because it is named. Thanks I appreciate the help! — YouAreSalty, Aug 02 '14 at 11:09

Simple SQL Update statements occassionally working in MS Access?

1 Answers1