.Net oracle parameter order
- by jkrebsbach
Using the ODAC (Oracle Data Access Components) downloaded from Oracle to talk to a handfull of Oracle DBs -
Was putting together my DAL to update the DB, and things weren't working as I hoped -
UPDATE foo SET bar = :P_BAR WHERE bap = :P_BAP
I assign my parameters -
objCmd.Parameters.Add(objBap);
objCmd.Parameters.Add(objBar);
Execute update command -
int result = objCmd.ExecuteNonQuery()
and result is zero!
... Is my filter incorrect?
SELECT count(*) FROM foo WHERE bap = :P_BAP
...result is one...
Is my new value incorrect? Am I using Char instead of Varchar somewhere and need an RTRIM? Is there a transaction getting involved? An error thrown and not caught?
The answer: Order of parameters.
The order parameters are added to the Oracle Command object must match the order the parameters are referenced in the SQL statement.
I was adding the parameters for the WHERE clause before adding the SET value parameters, and for that reason although no error was being thrown, no value was updated either.
Flip parameter collection around to match order of params in the SQL statement, and ExecuteNonQuery() is back to returning the number of rows affected.
