Returning Int value from Oracle DB using C#

Question

I am trying to return INTEGER value from Oracle Database using C# but it is giving me following Exception

An unhandled exception of type 'System.InvalidCastException' occurred in System.Data.dll

Additional information: Specified cast is not valid.

Can anyone help me to resolve this?

Here is my code:

OleDbConnection con = new OleDbConnection(
  "Provider=MSDAORA;Data Source=xe;User ID=hr; password=123");
   con.Open();

String cmd1 = 
  @"select retail_price,purchase_price from 
    productdetails where item_name='" + textBox1.Text + "'";

OleDbCommand a1 = new OleDbCommand(cmd1, con);
OleDbDataReader myReader = a1.ExecuteReader();

if (myReader.Read())
{
    int m = myReader.GetInt32(0);
    MessageBox.Show("" +m);
}

try `int m = Convert.ToInt32(myReader[0]);` istead of `int m = myReader.GetInt32(0);` — Dmitry Bychenko, Oct 27 '17 at 09:48
You may wish to read https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection . — mjwills, Oct 27 '17 at 09:50
Microsoft `MSDAORA` provider is outdated for ages. Use the Oracle provider `OraOLEDB.Oracle` — Wernfried Domscheit, Oct 27 '17 at 09:53
@mjwills i am unable to get what are you trying to tell. can you please explain that.? — JAMSHAID, Oct 27 '17 at 10:00
I'd suggest having a read of https://github.com/tmenier/AsyncPoco or https://github.com/schotime/NPoco . — mjwills, Oct 27 '17 at 10:41

Dmitry Bychenko · Accepted Answer · 2021-02-19T14:30:14.783

First of all, according to the query

    select retail_price,
           purchase_price
      from productdetails 
     where item_name = :item_name

the actual type of retail_price as well as purchase_price can be some floating point value type (double or decimal; say, 8.95$ == 8.95):

   decimal retail_price = Convert.ToDecimal(myReader[0]);
   decimal purchase_price = Convert.ToDecimal(myReader[1]);

If you insist on integers, round them up (8.95$ == 8.95 -> 9); assuming prices are non-negative:

   int retail_price = (int) (Convert.ToDouble(myReader[0]) + 0.5);
   int purchase_price = (int) (Convert.ToDouble(myReader[1]) + 0.5);

score 1 · Answer 2 · answered Oct 27 '17 at 09:49

Happening for a couple of reasons.

First, your price fields are likely to be decimal. Next, you probably need to be more explicit when accessing fields on your reader.

Try:-

int purchasePriceint = Convert.ToInt32(myReader["purchase_price"]);
int retailPriceint = Convert.ToInt32(myReader["retail_price"]);

Returning Int value from Oracle DB using C#

2 Answers2