locked
sql parameter changing Error RRS feed

  • 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 ??????

    Wednesday, June 4, 2014 2:37 AM

All replies

  • 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.

    Tuesday, September 30, 2014 12:21 PM
  • User269602965 posted

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

    Wednesday, October 1, 2014 2:07 PM