将参数绑定到 OLEDB 命令会引发错误

Binding parameters to OLEDB command throws error(将参数绑定到 OLEDB 命令会引发错误)

问题描述

我正在使用带有 .NET 的 AS 400 OLEDB.它用 '?'而不是 '@param 用命令绑定参数现在有一种情况，命令是这样的

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

现在我的命令变成了

    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注入:)

How do I bind parameters in this situation ? I want to avoid sql injection :)

推荐答案

尝试更改参数名称

myCommand.Parameters.Add(new OleDbParameter("@startRowIndex",startRowIndex));
myCommand.Parameters.Add(new OleDbParameter("@startRowIndex2", startRowIndex));
myCommand.Parameters.Add(new OleDbParameter("@MaximumRows", MaximumRows));

但保持 SQL 不变.

but leave the SQL as is.

这篇关于将参数绑定到 OLEDB 命令会引发错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！