CODEWITHSUNDEEP
c#sqloracle-database
Aruna Madushan
Aruna Madushan

Reputation: 15

sql parameter changing error

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();
  }

Again i change like that 

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

Now it is not working .should i set parameter value according asending order

Upvotes: 1

Views: 50

Answers (1)

apomene
apomene

Reputation: 14389

I have noticed the following parts where you need to consider:

  1. your select statement doesnt include any fields to retrieve, either try:

    SELECT  * FROM IBK_CO_USERS where .....

    or specify the exact fields you want to fetch

  2. You never open the connection: you have to call db.Open() (you just check if it is open)

  3. Generally the normal way to fetch an sqlreader results is using the snippet:

     odatareader = ocommand.ExecuteReader();
 while (odatareader.Read())
 {
  /// Fetch your data
 }

In general, As long as you set a parameter name and value and you pair these 2 together the order of instatination doesn't plays any role, eg:

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

is equivalent to:

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

The only way you get a different result is if you change the name-value pairing, eg:

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

results different from the previous 2 examples

However in Oracle ODP.Net documentation it is state that parameters are bound by postition by default. If you want to set it to be bound by name set:

ocommand.BindByName = true;

Upvotes: 1

Related Questions