Geeks With Blogs
Jeff Krebsbach

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 -




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.

Posted on Sunday, April 25, 2010 10:35 AM | Back to top

Comments on this post: .Net oracle parameter order

# re: .Net oracle parameter order
Requesting Gravatar...
It is possible to get Oracle to bind parameters by name:
objCmd.BindByName = true;
Left by Anonymous on Feb 02, 2012 7:42 AM

Your comment:
 (will show your gravatar)

Copyright © jkrebsbach | Powered by: | Join free