0

I am new at working with Oracle on .NET. I have worked with SQL in the past and have not encountered this problem. I have a Oracle server to which I connect with this connection string:

<add key="Test" value="Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=xxx))); User Id=xxxxxxx;Password=xxxxxx;"/>

   private OracleConnection Testconnection = new OracleConnection((string)new System.Configuration.AppSettingsReader().GetValue("Test", typeof(string)));

Then, I am opening the connection, check to see if it is opened(it is) and try to do the following oracle command.

  var accountOpenDate = new OracleCommand("select creationDate from tbl_user where id=:userid", this.Testconnection);
        getIsbAaccountOpenDate.Parameters.Add(":userid", this.IsbUserId);
        string isbAccountOpenDate = getIsbAaccountOpenDate.ExecuteNonQuery().ToString();

The Oracle command is proper and when I execute it in sql developer it works, but when I execute the code the isbAccountOpenDate has the value -1. I have tried everything , so don't know what to do anymore.

Thanks in advance

schumitza
  • 65
  • 7

2 Answers2

1

I'm not an ODP expert but I think this is your problem: that

getIsbAaccountOpenDate.Parameters.Add(":userid", this.IsbUserId); 
.                                      ^

The colon is used in the SQL statement to idenify a bind variable, so that the SQL compiler knows not to look for something called USERID in its scope. But it is not part of the name. If you remove the colon from the parameter assignment your code should work. Or at least fail for some other reason :)

Incidentally, you are issuing a SELECT statement, so why are you suing executeNonQuery()? Shouldn't that be executeScalar()? (It should be executeReader() for queries which return multiple records.)

APC
  • 144,005
  • 19
  • 170
  • 281
  • @Gerardo Lima, APC thanks for the answers but this does not solve my problem. Just Debugged the code wihouth the ":" and still the same result. – schumitza May 04 '12 at 09:44
  • There is no `ExecuteQuery` method for OracleCommand or any other command. This is the one that "executes an sql statement against a connection object" – schumitza May 04 '12 at 11:10
  • 2
    Like I said, I'm not an expert, but I still think executeNonQuery() is wrong for a select statement. – APC May 04 '12 at 11:26
0

Although you have to use colon to identify the parameters into the SQL script, it is NOT part of the parameter name. Besides, the right method to return a scalar value the database is ExecuteScalar. This can solve your problem:

var stm = "SELECT creationDate FROM tbl_user WHERE id=:userid;";
var cmd = new OracleCommand(stm, this.Testconnection);
cmd.Parameters.Add("userid", this.IsbUserId);
var result = (string) cmd.ExecuteScalar();

You can find the complete reference about System.Data.OracleClient in MSDN: OracleParameterCollection, OracleCommand.ExecuteScalar, OracleCommand.ExecuteNonQuery.

Gerardo Lima
  • 6,467
  • 3
  • 31
  • 47
  • sorry, that is not it. And i have even tried to add the value of ISBuserid in the statement and I still get the same result – schumitza May 04 '12 at 11:25
  • @APC is right, the `ExecuteNonQuery` method executes the command and returns the number of affected rows; it always returns `-1` if the statement is not `INSERT`, `UPDATE` or `DELETE`. You should use the `ExecuteScalar` method instead (I've already updated my proposed solution). I was worried about the parameter passing that I didn't noticed the use of the wrong method. – Gerardo Lima May 04 '12 at 14:03
  • Many thanks, I got stuck in my same repetitive logic and couldn't break it. – schumitza May 07 '12 at 08:08