CODEWITHSUNDEEP
c#oracle11g
Gauls
Gauls

Reputation: 1965

Query using Oracle Parameter returns no result 11g

A simple select statement using variable binding gives no rows??

  OracleConnection con = new OracleConnection(constr);
  con.Open();
 OracleCommand cmd = new OracleCommand();
  cmd.Connection = con;
  cmd.CommandText = "select country_name from hr.countries where country_id = :country_id;

  OracleParameter p_country_id = new OracleParameter();
  p_country_id.OracleDbType = OracleDbType.Varchar2;

  p_country_id.Value = "UK";

  cmd.Parameters.Add(p_country_id);

  OracleDataReader dr = cmd.ExecuteReader();

  if (dr.Read())
  {} ---> no rows

tried adding parameterName ,direction,size still result is 0???

Any help??

Upvotes: 1

Views: 1902

Answers (3)

Gene S
Gene S

Reputation: 2773

I believe this is all correct but it is written in NotePad. At the very least it should get you on the right track.

using (OracleConnection con = new OracleConnection(constr))
{
    con.Open();
    using (OracleCommand cmd = con.CreateCommand())
    {
        cmd.CommandText = "select country_name from hr.countries where country_id = :country_id";
        cmd.Parameters.Add("country_id", "UK")

        OracleDataReader dr = cmd.ExecuteReader();

        if (dr.Read()) 
       { 
            // You code here
       }
    }
}

NOTE: I put the using statements in there because this is always recommended when executing database queries. If an exception occurs the using statement will guarantee your database connection is still closed.

Upvotes: 1

Gauls
Gauls

Reputation: 1965

Thanks for responding guys, My unit test project app.config was pointing to wrong DB schema :(

Grrrrrrrrrrrrr! can't get anything stupid then this :) i will blame on my flu :)

Upvotes: 0

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174299

You forgot to give your parameter a name. Do so and it should work:

p_country_id.ParameterName = "country_id";

Upvotes: 0

Related Questions