OracleCommand SQL Parameters Binding

Question

I have a problem with the binding of the below parameter. The connection works because I had tested it without using parameters. However, the value of the query before being executed is still using '@userName' instead of 'jsmith' for example.

What is the problem? Is this not the right way to go around binding?

public static String GetFullName(String domainUser)
{
    DataTable dT;
    String fullName = "";

    OracleConnection db = DatabaseAdapter.GetConn();
    db.Open();

    OracleCommand oraCommand = new OracleCommand("SELECT fullname FROM user_profile WHERE domain_user_name = '@userName'", db);
    oraCommand.BindByName = true;
    oraCommand.Parameters.Add(new OracleParameter("@userName", domainUser));

    OracleDataReader oraReader = null;
    oraReader = oraCommand.ExecuteReader();

    if (oraReader.HasRows)
    {
        while (oraReader.Read())
        {
            fullName = oraReader.GetString(0);
        }
    }
    else
    {
        return "No Rows Found";
    }

    oraReader.Close();
    db.Close();
    db.Dispose();

    return fullName;
}

EDIT: I added @ to the parameter field name, but it still does not fix it.

Habib · Accepted Answer · 2015-01-06 14:05:06Z

93

Remove single quotes around @username, and with respect to oracle use : with parameter name instead of @, like:

OracleCommand oraCommand = new OracleCommand("SELECT fullname FROM sup_sys.user_profile
                           WHERE domain_user_name = :userName", db);
oraCommand.Parameters.Add(new OracleParameter("userName", domainUser));

Source: Using Parameters

edited Jan 6, 2015 at 14:05

answered Jun 15, 2012 at 10:28

Habib

224k30 gold badges420 silver badges446 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ryan S Over a year ago

That returns a missing expression error ORA-00936. That is a varchar in the database, so it should have ' ', I assume.

Habib Over a year ago

@RyanSammut, check my updated answer, and the link I have posted

Arun Singh Over a year ago

May be it will help: stackoverflow.com/questions/7316850/….

Cleptus · Accepted Answer · 2019-09-27 07:17:31Z

Oracle has a different syntax for parameters than Sql-Server. So use : instead of @

using(var con=new OracleConnection(connectionString))
{
   con.open();
   var sql = "insert into users values (:id,:name,:surname,:username)";

   using(var cmd = new OracleCommand(sql,con)
   {
      OracleParameter[] parameters = new OracleParameter[] {
             new OracleParameter("id",1234),
             new OracleParameter("name","John"),
             new OracleParameter("surname","Doe"),
             new OracleParameter("username","johnd")
      };

      cmd.Parameters.AddRange(parameters);
      cmd.ExecuteNonQuery();
   }
}

When using named parameters in an OracleCommand you must precede the parameter name with a colon (:).

http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oraclecommand.parameters.aspx

Xcalibur37 · Accepted Answer · 2012-10-11 13:34:26Z

1

You need to use something like this:

 OracleCommand oraCommand = new OracleCommand("SELECT fullname FROM sup_sys.user_profile
                       WHERE domain_user_name = :userName", db);

More can be found in this MSDN article: http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oraclecommand.parameters%28v=vs.100%29.aspx

It is advised you use the : character instead of @ for Oracle.

answered Oct 11, 2012 at 13:34

Xcalibur37

2,3191 gold badge17 silver badges20 bronze badges

Comments

Andrew Grinder · Accepted Answer · 2014-09-11 15:12:53Z

string strConn = "Data Source=ORCL134; User ID=user; Password=psd;";

System.Data.OracleClient.OracleConnection con = newSystem.Data.OracleClient.OracleConnection(strConn);
    con.Open();

    System.Data.OracleClient.OracleCommand Cmd = 
        new System.Data.OracleClient.OracleCommand(
            "SELECT * FROM TBLE_Name WHERE ColumnName_year= :year", con);

//for oracle..it is :object_name and for sql it s @object_name
    Cmd.Parameters.Add(new System.Data.OracleClient.OracleParameter("year", (txtFinYear.Text).ToString()));

    System.Data.OracleClient.OracleDataAdapter da = new System.Data.OracleClient.OracleDataAdapter(Cmd);
    DataSet myDS = new DataSet();
    da.Fill(myDS);
    try
    {
        lblBatch.Text = "Batch Number is : " + Convert.ToString(myDS.Tables[0].Rows[0][19]);
        lblBatch.ForeColor = System.Drawing.Color.Green;
        lblBatch.Visible = true;
    }
    catch 
    {
        lblBatch.Text = "No Data Found for the Year : " + txtFinYear.Text;
        lblBatch.ForeColor = System.Drawing.Color.Red;
        lblBatch.Visible = true;   
    }
    da.Dispose();
    con.Close();

Dharman · Accepted Answer · 2020-07-26 14:40:33Z

0

Here is how I solved the same problem using the Oracle.DataAccess.Client Namespace.

using Oracle.DataAccess.Client;


string strConnection = ConfigurationManager.ConnectionStrings["oConnection"].ConnectionString;

dataConnection = new OracleConnectionStringBuilder(strConnection);

OracleConnection oConnection = new OracleConnection(dataConnection.ToString());

oConnection.Open();

OracleCommand tmpCommand = oConnection.CreateCommand();
tmpCommand.Parameters.Add("user", OracleDbType.Varchar2, txtUser.Text, ParameterDirection.Input);
tmpCommand.CommandText = "SELECT USER, PASS FROM TB_USERS WHERE USER = :1";

try
{
    OracleDataReader tmpReader = tmpCommand.ExecuteReader(CommandBehavior.SingleRow);
    
    if (tmpReader.HasRows)
    {
        // PT: IMPLEMENTE SEU CÓDIGO    
        // ES: IMPLEMENTAR EL CÓDIGO
        // EN: IMPLEMENT YOUR CODE
    }
}
catch(Exception e)
{
        // PT: IMPLEMENTE SEU CÓDIGO    
        // ES: IMPLEMENTAR EL CÓDIGO
        // EN: IMPLEMENT YOUR CODE
}

edited Jul 26, 2020 at 14:40

Dharman♦

33.9k27 gold badges106 silver badges157 bronze badges

answered Jan 30, 2017 at 16:08

Dominus Farib

11 bronze badge

1 Comment

Ishikawa Over a year ago

I've seen this parameter binding notation several times, but when I try to use it ({COLUMN_NAME} = :1), it throws me an exception telling me that the binding is invalid (parameter name). Can you tell me what could be wrong in this case ?

Collectives™ on Stack Overflow

OracleCommand SQL Parameters Binding

5 Answers 5

3 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related