Why does invoking my UPDATE query in an Access database via OleDb not work?

Question

Update 2: I solved this, see my answer.

---

I am invoking queries in a Microsoft Access database from C# using OleDb, but I can't get my update queries to work.

No error is thrown, but updates are not persisted in the database.

Can anyone shed any light on this?

---

SQL query in the database:

UPDATE tableName SET tableName.LastPolledDtg = LastPolledDtg
WHERE tableName.key = ID;

C#:

OleDbCommand command = new OleDbCommand();
SetCommandType(command, CommandType.StoredProcedure, "NameOfQueryInAccessDatabase");
AddParamToSQLCmd(command, "@ID", OleDbType.Integer, 4, ParameterDirection.Input, id);
AddParamToSQLCmd(command, "@LastPolledDtg", OleDbType.Date, 4, ParameterDirection.Input, DateTime.Now);
using (OleDbConnection connection = new OleDbConnection("connectionString"))
{
command.Connection = connection;
connection.Open();
result = command.ExecuteNonQuery();
}

Connection String:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Documents and Settings\\Administrator\\Desktop\\dev\\src\\Website\\App_Data\\tracking.mdb"

---

Update 1:

I tried to narrow down the possibilities by creating a new database containing a single table and a single query and ensuring access is closed when I run the C# to update the table.

The update is still not performed. I suspect it's a syntax issue (could also be a permissions issue?), but without any error messages it's pretty hard to debug!

Answer 1

I'm not an Access coder, but it sounds like implicit transactions may be being used. This would mean that when you execute a data modification command, a transaction is automatically opened which must be committed/rolled back explicitly in your code.

Either that or for some reason no rows are being affected by your update.

Answer 2

Your update query is a bit confusing (you're using LastPolledDtg as a field and as value for it). If you mean the parameter to define the column, then it won't work.

Otherwise, try to rewrite your query in Access using the PARAMETER syntax, for instance:

PARAMETERS LastPolledDtgArg Text ( 255 ), ID Long;
UPDATE tableName SET tableName.LastPolledDtg = [LastPolledDtgArg]
WHERE tableName.key = [ID];

If that doesn't work, try using the SQL for the query directly from C# instead of trying to execute the stored query.

Something else

Re-order your parameters so that they are passed in the same order as they are defined in the Query so you pass the value for LastPolledDtArg before ID.

Answer 3

Just solved the issue - it was the naming of the parameters - it seems you cannot name the parameters in a query the same as any of the fields.

Changing the query from:

UPDATE tableName SET tableName.LastPolledDtg = LastPolledDtg
WHERE tableName.key = ID;

to:

UPDATE tableName SET tableName.LastPolledDtg = LastPolledDtgArg
WHERE tableName.key = ID;

...and updating the invoking C# with the parameter name change got it writing to the database.

BUT, there was one other little nasty: leaving the parameter ordering in the C# as is, caused the LastPolledDtg field in the database to be updated with the minimum date (1899 or something). Re-ordering the addition of the parameters to the OleDbCommand to match their occurrence in the SQL fixed this.

So the C# should look like:

OleDbCommand command = new OleDbCommand();
SetCommandType(command, CommandType.StoredProcedure, "NameOfQueryInAccessDatabase");
AddParamToSQLCmd(command, "@LastPolledDtgArg", OleDbType.Date, 4, ParameterDirection.Input, DateTime.Now);
AddParamToSQLCmd(command, "@ID", OleDbType.Integer, 4, ParameterDirection.Input, id);
using (OleDbConnection connection = new OleDbConnection("connectionString"))
{
command.Connection = connection;
connection.Open();
result = command.ExecuteNonQuery();
}

Man I love Access.