0

Getting operation in not valid error when doing ExecuteScalar() to return a date. No errors on compile and I'm not sure why this isn't working - message is not helping me understand. Any thoughts?

using (OracleConnection con = new OracleConnection(----------)
 {
     using (OracleCommand cmd = new OracleCommand())
     {
         con.Open();
         cmd.CommandText = "select end_date from calendar where :today >= begin_date and :today <= end_date";
         DateTime today = DateTime.Today;
         cmd.Parameters.Add(":today", OracleDbType.Date).Value = today;
         try
         {                         
             DateTime ppEndDate = (DateTime)cmd.ExecuteScalar();
         }
         catch (Exception ex)
         {
             Console.WriteLine(ex.Message);
         }
     }
 }
Dolphie97
  • 1
  • 1
  • Did you check if ex.InnerException is not null? If not what is the error message of the InnerException? – Steve Apr 10 '17 at 15:23
  • I would expect inner exception as well. You might also inspect the status of con before ExecuteScalar (con.State) – Jason W Apr 10 '17 at 15:25
  • Is `:today` a valid parameter name? I usually tend to use `@today` in such situations – Vikhram Apr 10 '17 at 16:26
  • ex.Message = "Operation is not valid due to current state of the object. – Dolphie97 Apr 11 '17 at 16:25
  • Please bear with me as I am very new at C# and debugging in general. To answer your questions: ex.Message = "Operation is not valid due to current state of the object., the con is working fine The today variable is returning the current date, but I don't see a way to validate the parameter. This is querying an Oracle database. I was previously using @ which works for SQL, but I was told by a colleague to use : for Oracle parameters. – Dolphie97 Apr 11 '17 at 19:22
  • ppEndDate is returning null. When I run a similar query on the database using Toad, I get the appropriate date returned 4/19/2017 (i.e., select end_date from calendar where sysdate >= begin_date and sysdate <= end_date) – Dolphie97 Apr 11 '17 at 19:22

0 Answers0