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!
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.