OleDbCommand 参数顺序和优先级 [英] OleDbCommand parameters order and priority

问题描述

过去 40 分钟我一直在调试这个查询，问题显然是参数的顺序.

SELECT * FROM tblSomeThing WHERE id = @id AND 首次亮相 = @dtDebut AND fin = @dtFin

然后我就这样加参数了，注意最后两个参数切换了，没有结果.

cmd.Parameters.Add("@id", OleDbType.Integer).Value = idSociete;cmd.Parameters.Add("@dtFin", OleDbType.Date).Value = dateTraitementFin;cmd.Parameters.Add("@dtDebut", OleDbType.Date).Value = dateTraitementDebut;

当我按照它们在查询中出现的方式声明参数时，一切正常.

我认为命名参数首先要解决这个问题！我在这里错过了什么?

谢谢

解决方案

根据 http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx OleDbCommand 不支持命名参数

<块引用>

当 CommandType 设置为 Text 时，OLE DB .NET 提供程序不支持将参数传递给 SQL 语句或由 OleDbCommand 调用的存储过程的命名参数.在这种情况下，必须使用问号 (?) 占位符.例如:

SELECT * FROM Customers WHERE CustomerID = ?

<块引用>

因此，OleDbParameter 对象添加到 OleDbParameterCollection 的顺序必须直接对应命令文本中参数的问号占位符的位置.

所以参数的顺序很重要.

I have been debugging this query for the last 40 minutes, and the problem apparently is the order of the parameters after all.

SELECT * FROM tblSomeThing WHERE id = @id AND debut = @dtDebut AND fin = @dtFin

Then I add the parameters this way, notice that the two last parameters are switched, I get no results.

cmd.Parameters.Add("@id", OleDbType.Integer).Value = idSociete;
cmd.Parameters.Add("@dtFin", OleDbType.Date).Value = dateTraitementFin;
cmd.Parameters.Add("@dtDebut", OleDbType.Date).Value = dateTraitementDebut;

When I declare the parameters the way they appear in the queury everything works perfectly.

I thought named parameters were at first place to address this problem! what am I missing here?

Thank you

解决方案

According to http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx OleDbCommand does not support named parameter

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:

SELECT * FROM Customers WHERE CustomerID = ?

Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.

So order of parameter is important.

这篇关于OleDbCommand 参数顺序和优先级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！