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); — Dmitrii Bychenko
– Dmitrii Bychenko, Commented Oct 27, 2017 at 9:48
You may wish to read stackoverflow.com/questions/14376473/… . — mjwills
– mjwills, Commented Oct 27, 2017 at 9:50
Microsoft MSDAORA provider is outdated for ages. Use the Oracle provider OraOLEDB.Oracle — Wernfried Domscheit
– Wernfried Domscheit, Commented Oct 27, 2017 at 9:53
@mjwills i am unable to get what are you trying to tell. can you please explain that.? — JAMSHAID
– JAMSHAID, Commented Oct 27, 2017 at 10:00
I'd suggest having a read of github.com/tmenier/AsyncPoco or github.com/schotime/NPoco . — mjwills
– mjwills, Commented Oct 27, 2017 at 10:41

Dmitrii Bychenko · Accepted Answer · 2021-02-19 14:30:14Z

2

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);

edited Feb 19, 2021 at 14:30

answered Oct 27, 2017 at 9:53

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul Alan Taylor · Accepted Answer · 2017-10-27 09:49:44Z

1

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"]);

answered Oct 27, 2017 at 9:49

Paul Alan Taylor

10.7k1 gold badge30 silver badges44 bronze badges

Collectives™ on Stack Overflow

Returning Int value from Oracle DB using C#

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related