为什么通过 OleDb 在 Access 数据库中调用 UPDATE 查询不起作用?
更新 2:我解决了这个问题,请参阅我的答案。
我正在使用 OleDb 从 C# 调用 Microsoft Access 数据库中的查询,但我无法使更新查询正常工作。
不会引发错误,但更新不会保留在数据库中。
任何人都可以阐明这一点吗?
数据库中的 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# 更新该表时关闭访问。
更新仍未执行。 我怀疑这是一个语法问题(也可能是权限问题?),但没有任何错误消息,很难调试!
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!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
我不是 Access 编码员,但听起来可能正在使用隐式事务。 这意味着当您执行数据修改命令时,会自动打开一个事务,必须在代码中显式提交/回滚该事务。
要么是这样,要么由于某种原因,没有行受到您的更新的影响。
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.
刚刚解决了问题 - 这是参数的命名 - 似乎您不能将查询中的参数命名为与任何字段相同。
将查询从: 更改
为:
...并使用参数名称更改更新调用 C#,使其写入数据库。
但是,还有另外一个小问题:按原样保留 C# 中的参数排序,会导致数据库中的 LastPolledDtg 字段更新为最小日期(1899 年或其他日期)。 对添加到 OleDbCommand 的参数重新排序以匹配它们在 SQL 中的出现修复了此问题。
所以 C# 应该是这样的:
我喜欢 Access。
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:
to:
...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:
Man I love Access.
您的更新查询有点混乱(您使用
LastPolledDtg
作为字段和作为其值)。如果您的意思是定义列的参数,那么它将不起作用。
否则,请尝试使用
PARAMETER
语法在 Access 中重写查询,例如:如果这不起作用,请尝试直接从 C# 使用 SQL 进行查询,而不是尝试执行存储的查询。
其他
重新排序您的参数,以便它们按照查询中定义的顺序传递,以便您在
ID< 之前传递
LastPolledDtArg
的值/代码>。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: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
beforeID
.在 ms-access 中,参数的顺序必须与 SQL 语句的顺序相同。
In ms-access, parameters must be in the same order as the SQL statement.