为什么通过OleDb的不行调用在Access数据库我UPDATE查询？ [英] Why does invoking my UPDATE query in an Access database via OleDb not work?

问题描述

更新2：我解决了这个，看看我的答案

---

我使用的OleDb调用在Microsoft Access数据库查询从C＃，但我不能让我的更新查询工作。

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.

任何人都可以阐明这？

---

在数据库中的SQL查询：

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();
}

连接字符串：

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

---

更新1：

我试图通过创建包含单个表和单个查询一个新的数据库和确保被关闭，当我运行C＃来更新表来缩小的可能性。

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.

更改从查询：

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

到

to:

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

...和更新调用C＃与参数名称更改了它写入数据库。

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

<强>但，有一个其他的小讨厌：离开参数排序在C＃原样，引起在数据库中LastPolledDtg字段进行更新用最小的日期（1899年或东西）。 。重新排序中加入参数到的OleDbCommand的，以满足他们在固定的这个SQL发生

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.

所以，C＃应该是这样的：

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.

这篇关于为什么通过OleDb的不行调用在Access数据库我UPDATE查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！