Binding parameters to OLEDB command throws error

Question

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

Chris Diver · Accepted Answer · 2010-07-09 06:55:24Z

3

Try changing the names of the parameters

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

but leave the SQL as is.

answered Jul 9, 2010 at 6:55

Chris Diver

19.9k4 gold badges50 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Zo Has Over a year ago

Thanks for the reply Chris. In OLEDB the parameters go with index and symbol '?' and not with name '@param'. That works on SQLServer. Any clue?

Zo Has Over a year ago

He is, but problem is still there because of two parameters inside a single block (?+?). What do you do in access to get away with that ? Check my error description above.

Zo Has Over a year ago

Bump, anybody with OLEDB experience ?

Chris Diver Over a year ago

"Both the operands in a expression cannot be parameter markers. For example, specifying an expression of the form: ? + ? is not valid." Can't you add the two values instead of doing it in SQL?

Zo Has Over a year ago

hey I can, but that would require me to concatenate the values with query like where '+(parameter1+parameter2)+' Problem being, I want to bind the parameters in form of parametrized query.

Collectives™ on Stack Overflow

Binding parameters to OLEDB command throws error

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related