将参数绑定到 OLEDB 命令会引发错误
我正在将 AS 400 OLEDB 与 .NET 一起使用。它使用“?”而不是 '@param 将参数与命令绑定 现在有一种情况,命令就像
SELECT ...
FROM
(SELECT ...
ROW_NUMBER() OVER(ORDER BY ColumnName) as RowNum
FROM Employees e
) as DerivedTableName
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
我的命令变成
SELECT ...
FROM
(SELECT ...
ROW_NUMBER() OVER(ORDER BY ColumnName) as RowNum
FROM Employees e
) as DerivedTableName
WHERE RowNum BETWEEN ? AND (? + ?) - 1
现在,当我绑定参数时,
myCommand.Parameters.Add(new OleDbParameter("?",startRowIndex));
myCommand.Parameters.Add(new OleDbParameter("?", startRowIndex));
myCommand.Parameters.Add(new OleDbParameter("?", MaximumRows));
现在,它会抛出错误,
SQL0417: Combination of parameter markers not valid.
Cause . . . . . : The statement string specified as the object of a PREPARE statement contains a predicate or expression where parameter markers have been used as operands of the same operator. The following restrictions apply to the use of parameter markers: -- Both the operands in a predicate cannot be parameter markers. For example, specifying predicates of the form: ? = ? or ? = ( SELECT ? FROM x ) are not valid.
在这种情况下如何绑定参数?我想避免sql注入:)
I am using AS 400 OLEDB with .NET. It uses '?' instead of '@param to bind parameters with a command
Now there is a situation where the command is like
SELECT ...
FROM
(SELECT ...
ROW_NUMBER() OVER(ORDER BY ColumnName) as RowNum
FROM Employees e
) as DerivedTableName
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
Now my command becomes
SELECT ...
FROM
(SELECT ...
ROW_NUMBER() OVER(ORDER BY ColumnName) as RowNum
FROM Employees e
) as DerivedTableName
WHERE RowNum BETWEEN ? AND (? + ?) - 1
Now when I bind parameters like
myCommand.Parameters.Add(new OleDbParameter("?",startRowIndex));
myCommand.Parameters.Add(new OleDbParameter("?", startRowIndex));
myCommand.Parameters.Add(new OleDbParameter("?", MaximumRows));
It throws error
SQL0417: Combination of parameter markers not valid.
Cause . . . . . : The statement string specified as the object of a PREPARE statement contains a predicate or expression where parameter markers have been used as operands of the same operator. The following restrictions apply to the use of parameter markers: -- Both the operands in a predicate cannot be parameter markers. For example, specifying predicates of the form: ? = ? or ? = ( SELECT ? FROM x ) are not valid.
How do I bind parameters in this situation ? I want to avoid sql injection :)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
尝试更改参数名称
,但保持 SQL 不变。
Try changing the names of the parameters
but leave the SQL as is.