How to check if OleDbDataReader is empty?

Question

This is the code i am using to select Maximum RollNo based on Class field value. But when there is no data about Class Field in Table. Then Error is generated.

using (var conn = new OleDbConnection(DatabaseObjects.ConnectionString))
            {
                conn.Open();
                command = new OleDbCommand("select max(RollNo) as Roll from Students where Class = '"+cmbClass.Text+"'", conn);
                OleDbDataReader dr = command.ExecuteReader();
                if (!dr.IsDBNull(0))
                {
                    while (dr.Read())
                    {
                        i = Convert.ToInt32(dr["Roll"]);
                    }
                }

InvalidOperation Exception is occurring. I want to get value of RollNo if data is available in Table. If data is not available in Table then what should I do?

You’re trying to access the row before you read it, with `if (!dr.IsDBNull(0))` — stuartd, Jun 06 '20 at 19:11
Please, for your own sake... Use parameters and not string concatenation — pinkfloydx33, Jun 06 '20 at 19:31

Abdessamad Azzouzi · Accepted Answer · 2020-06-07T22:50:31.957

2

you are inversing the steps :

open the connection;
check is there is comming data;
check if the value is not null;
read the data;

try this :

while (dr.Read())
   {
       if (!dr.IsDBNull(0))
          {
            i = Convert.ToInt32(dr["Roll"]);
          }
   }

while your are attending just single value, use executeScalar to get the value ;

  conn.Open();          
  command = new OleDbCommand("select isnull(max(RollNo),-1) as Roll from Students where Class = '"+cmbClass.Text+"'", conn);
  int rollNo = (int) command.ExecuteScallar();
  if(rollno !=-1)
  {
     // TODO :
  }

edited Jun 07 '20 at 22:50

answered Jun 06 '20 at 19:13

Abdessamad Azzouzi

122
1
9

if(rollno !=-2) why you have use -2? – Muhammad Rizwan Jun 07 '20 at 03:15
when i executed your given code, it gives OleDbException. – Muhammad Rizwan Jun 07 '20 at 04:03
Additional information: Wrong number of arguments used with function in query expression 'isnull(max(RollNo),-1'. – Muhammad Rizwan Jun 07 '20 at 04:16
Sorry bro, i want to write {-1}, and i forgot the ')' in the query, sorry – Abdessamad Azzouzi Jun 07 '20 at 22:49
it gives error at this line [ int rollNo = (int) command.ExecuteScallar(); ] specified cast is not valid. – Muhammad Rizwan Jun 17 '20 at 17:52
if you are using access, try this `select IIF(Isnull(max(RollNo)),-1,max(RollNo)) from ..... where class = value_that_you_want group by Class, RollNo` – Abdessamad Azzouzi Jun 22 '20 at 12:26

How to check if OleDbDataReader is empty?

1 Answers1