sql parameter changing Error

Question

User-2012801151 posted

Hii

This is my working Code

 strQuery = @"SELECT FROM IBK_CO_USERS where upper(user_id)= upper(:userid) AND co_id= :Com_Id";
 {

                    ocommand = new OracleCommand();
                    if (db.GetConnection().State == ConnectionState.Open)
                    {
                        ocommand.CommandText = strQuery;
                        ocommand.Connection = db.GetConnection();
                        
                        ocommand.Parameters.Add("userid", OracleDbType.Varchar2);
                        ocommand.Parameters["userid"].Value = userID;
                        ocommand.Parameters.Add("Com_Id", OracleDbType.Varchar2);
                        ocommand.Parameters["Com_Id"].Value = Comid;
                       
                        odatareader = ocommand.ExecuteReader();
                        odatareader.Read();

but when i change parameter like this 

 ocommand.Parameters.Add("Com_Id", OracleDbType.Varchar2);
 ocommand.Parameters["Com_Id"].Value = Comid;
 ocommand.Parameters.Add("userid", OracleDbType.Varchar2);
 ocommand.Parameters["userid"].Value = userID;

But it is not working should i set parameter according to query  parameter .why is ti not working changing paramter ??????

Answer 1

User753101303 posted

Hi,

Not using Oracle but my first though would be that Oracle uses the parameters position rather than parameter names ?

You mean that it doesn't return the value or that you have an error of some kind.

What if you try :

 ocommand.Parameters.Add("Com_Id", OracleDbType.Varchar2);
 ocommand.Parameters["Com_Id"].Value = userID;//Comid;
 ocommand.Parameters.Add("userid", OracleDbType.Varchar2);
 ocommand.Parameters["userid"].Value = Comid;//userID;

If it works it would proove that only the position is taken into account.

 Edit: see http://stackoverflow.com/questions/2177733/is-there-a-way-to-force-oraclecommand-bindbyname-to-be-true-by-default-for-odp-n 

It seems you have to explicitely tell that you are binding parameters based on the name. Note also that System.Data.OracleClient is deprecated if this is what you are using.

Answer 2

User269602965 posted

Oracle parameters have to be in the order they are used as per ODP.NET documentation