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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Here is how I solved the same problem using the Oracle DataAccess Client Namespace  using Oracle DataAccess Client    string strConnection   ConfigurationManager ConnectionStrings  quot oConnection quot   ConnectionString   dataConnection   new OracleConnectionStringBuilder strConnection    OracleConnection oConnection   new OracleConnection dataConnection ToString      oConnection Open     OracleCommand tmpCommand   oConnection CreateCommand    tmpCommand Parameters Add  quot user quot   OracleDbType Varchar2  txtUser Text  ParameterDirection Input   tmpCommand CommandText    quot SELECT USER  PASS FROM TB USERS WHERE USER    1 quot    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

User · Answer

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

[c#] OracleCommand SQL Parameters Binding

Examples related to c#

Examples related to oracle

Examples related to system.data.oracleclient