Specified cast is not valid. Is it because I am attempting to cast an object?

Question

I have the following C# code that I am using to attempt to query an oracle database. I am simply trying to get the numeric result so that I can output it to the console and eventually hold it in a variable.

I keep getting the following error though:

System.InvalidCastException: Specified cast is not valid at Oracle.DataAccess.Client.OracleDataReader.GetInt32(Int32 i)

I am using VS 2012 on a windows professional machine. Please let me know if I need to add additional info. Any help greatly appreciated.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Configuration;
using System.Data.SqlClient;

namespace OB_837_File_Reconciliation_Rpt_Automation
{
  class Program
  {
    static void Main(string[] args)
   {
      OracleConnection dbConnection;
      string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=             (PROTOCOL=TCP)(HOST=*******)(PORT=******))(CONNECT_DATA=(SERVICE_NAME=******)));User ID=*****;Password=*********";
        try
        {
            dbConnection = new OracleConnection(connectionString);                
            string query = "select count (*) AS AMG_Prof from wellmed_owner.claim c inner join WELLMED_OWNER.payment_detail pd on c.claim_id = pd.claim_id where c.claim_status in ('6','8','C') and c.insured_group_id in ('ASA','AEP') and c.form_type = '1' and trunc(pd.paid_date) = trunc(sysdate -4)";
            dbConnection.Open();
            OracleCommand comm = new OracleCommand(query, dbConnection);               
            OracleDataReader rdr = comm.ExecuteReader();
            rdr.Read();                
            int count = (int)comm.ExecuteScalar();
            Console.WriteLine(count);
            //int num = rdr.GetInt32(0);
            //Console.WriteLine(count);              
            Console.WriteLine("Connecting Okay");

       }
     catch (Exception e)
     {
       Console.WriteLine(e);
     }

        }
    }
}

Many Thanks to both of you. The following worked. `Object result = comm.ExecuteScalar(); int count = Convert.ToInt32(result);` can either of you recommend a good learning source. Don't know where to start. Internet sources I've tried are very thrown together. Thanks Again. — jjones150, Jun 14 '15 at 21:35

Hamid Pourjam · Accepted Answer · 2015-12-06T13:08:50.620

3

use ExecuteScalar

dbConnection.Open();
OracleCommand comm = new OracleCommand(query, dbConnection);               
decimal count = (decimal)comm.ExecuteScalar();
Console.WriteLine(count);              
Console.WriteLine("Connecting Okay");

edited Dec 06 '15 at 13:08

answered Jun 14 '15 at 21:08

Hamid Pourjam

20,441
9
58
74

Thanks dotctor. I am still getting the same message however. Anything else you can think of? – jjones150 Jun 14 '15 at 21:12
On which line you get the exception? can you update your question with updated code and full exception trace? – Hamid Pourjam Jun 14 '15 at 21:13
The suggestion of `ExecuteScalar()` should have been a comment, if it should have been posted at all. This clearly cannot fix the actual problem, so should not have been posted as an answer. – Jun 14 '15 at 21:14
I get the exception on line 27: "int count = (int)comm.ExecuteScalar();" – jjones150 Jun 14 '15 at 21:22
It should be `decimal` not `int` – Hamid Pourjam Jun 14 '15 at 21:24
@dotctor I know that the value will be a whole number though. – jjones150 Jun 14 '15 at 21:26
1

Now that looks like a decent answer, even if I can't verify it. Downvote removed. @jjones150 If this answer is correct that Oracle returns the count as if it's a decimal, but *you* know that it'll really be a whole number (and small enough that it fits in `int`), you can then use regular C# code to convert it to an integer. Even a simple extra cast should work: `int count = (int)(decimal)comm.ExecuteScalar();`. – Jun 14 '15 at 21:28
@dotctor yep solves. Thanks so much for the help! Trying to mark as solved but don't see where. can either of you recommend a good learning source. Don't know where to start. Internet sources I've tried are very thrown together. Thanks Again – jjones150 Jun 14 '15 at 21:43

Atilla Ozgur · Answer 2 · 2015-06-17T08:01:21.780

0

Your query returns only one value, therefore use ExecuteScalar instead of ExecuteReader.

Object result =  comm.ExecuteScalar ();
if(result != null)
{
     decimal count = (decimal) result;
}

edited Jun 17 '15 at 08:01

answered Jun 14 '15 at 21:10

Atilla Ozgur

14,339
3
49
69

So instead of OracleDataReader rdr = comm.ExecuteReader(); I would have OracleDataReader rdr = comm.ExecuteScalar();? What would I change OracleDataReader to? – jjones150 Jun 14 '15 at 21:15
The suggestion of `ExecuteScalar()` should have been a comment, if it should have been posted at all. This clearly cannot fix the actual problem, so should not have been posted as an answer. – Jun 14 '15 at 21:15
Actual problem is use of ExecuteReader. – Atilla Ozgur Jun 14 '15 at 21:17
2

@AtillaOzgur No, it's not. While `ExecuteScalar()` is a simpler approach to getting the results when you have one row, one column, `ExecuteReader()` works for any number of rows, any number of columns, even if they happen to both be 1. And both with `ExecuteScalar()` and with `ExecuteReader()`, you're required to pay attention to the type of the column(s). – Jun 14 '15 at 21:19

Specified cast is not valid. Is it because I am attempting to cast an object?

2 Answers2