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

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.