OledbDataReader function to return integer

Question

Working in C#, I'm having trouble with a function. Function runs a query and the query is supposed to return one integer value but I'm having trouble returning it. I keep getting errors like:

Unable to cast object of type oleDbDataReader to type Int32
Specified cast is not valid

Not sure how to do this with C# and OleDbDataReader. My code is below

     public static int FifthQuery()
     {
         int _value = 0;
         OleDbConnection _connectMe = Utilities.OledbConnect();

         OleDbCommand _query1 = new OleDbCommand();
         _query1.Connection = _connectMe;
         _query1.CommandText = "SELECT count(*) FROM GIS.PERSONS where Name_Prefix = 'Dr.'";
         OleDbDataReader _reader = _query1.ExecuteReader();

         _reader.Read();

              //_value = Convert.ToInt32(_reader);
              _value = _reader.GetInt32(0);


         return _value;
     }

Trying to return an integer but I keep getting the errors about cast and type — user1898629
– user1898629, Commented May 6, 2014 at 19:58
Exactly what error? If you want people to help you, tell them what the problem is! — John Saunders
– John Saunders, Commented May 6, 2014 at 19:58

Soner Gönül · Accepted Answer · 2014-05-06 19:18:51Z

3

Since you using COUNT(*), using ExecuteScalar would be better approach.

Executes the query, and returns the first column of the first row in the result set returned by the query.

int _value = (int)_query1.ExecuteScalar();

Also use using statement to dispose your OleDbConnection and OleDbCommand.

using(OleDbConnection _connectMe = Utilities.OledbConnect())
using(OleDbCommand _query1 = _connectMe.CreateCommand())
{
    _query1.CommandText = "SELECT count(*) FROM GIS.PERSONS where Name_Prefix = 'Dr.'";
    _connectMe.Open();
    int _value = (int)_query1.ExecuteScalar();
}

edited May 6, 2014 at 19:18

answered May 6, 2014 at 19:13

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

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

4 Comments

user1898629 Over a year ago

Thanks for the post. I have tried adjusting my funciton but on the line: int _value = (int)_query1.ExecuteScalar(); I keep getting an error saying that the specified cast is not valid. I'm not sure if I'm missing some library or why this is happening. I also placed "Return _value" before the last closing bracket of the second 'Using' statement. Not sure if that poses any issues as well.

Soner Gönül Over a year ago

Are you sure you get this error or int _value = (int)_query1.ExecuteScalar(); line? I don't think so because since your query returns COUNT(*) it will be a valid integer in my opinion.

user1898629 Over a year ago

There was some kind of type error. I can't remember what it was exaclty. But I did end up changing th type to an object rather than an integer ----> object _myObj = _query1.ExecuteScalar(); I then added another line to conver the object to an integer and this seemed to work out fine ---> int _value = Convert.ToInt32(_myObj); Not the prettiest way but it works. I still am not sure what could have been the problem before with the specified cast not being valid. I do appreciate your feedback and concern. THanks for everything.

user1898629 Over a year ago

The value is 23. But I agree, your line should have worked but I was getting the error: "Specified cast is not valid". This came from my exception error message when I put the code in a Try-Catch block. Again, it may be some wierd libraries or references or config files that aren't set up correctly, or something is missing I'm just not sure. But regardless, things are working right now. Thanks again

Collectives™ on Stack Overflow

OledbDataReader function to return integer

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related