How to check if SQLDataReader has no rows

Question

I am trying to figure out how to check if my SqlDataReader is null or has no rows (meaning the reservation does not exist) and then display a messagebox. For some reason when I debug once it hits the While dr.Read()) code it steps out if it does not have a return result.

I've tried putting this code in a few different locations but none seem to fire off the messagebox if no records are returned

if (dr.GetValue(0) == DBNull.Value || !dr.HasRows)
{
    MessageBox.Show("Reservation Number Does Not Exist","Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else
{
    (read records)
}

My code...

try
{
   using (SqlConnection con = new SqlConnection(connectionString))
   {
      using (SqlCommand cmd = con.CreateCommand())
      {
         con.Open();
         cmd.CommandText = "usp_StoredProcedureName";
         cmd.CommandType = CommandType.StoredProcedure;
         cmd.Parameters.AddWithValue("@regnum", regnumber);

         using (SqlDataReader dr = cmd.ExecuteReader())
         {
             //Loop through all the rows, retrieving the columns you need.
             while (dr.Read())
             {
                 lblConf.Text = dr.GetValue(0).ToString();
                 lblName.Text = dr.GetValue(1).ToString() + "," + dr.GetValue(2);
                 lblCompany.Text = dr.GetValue(3).ToString();
                 lblStatus.Text = dr.GetValue(4).ToString();
             }
         }
      }
   }
}
catch (Exception ex)
{
    MessageBox.Show("Can not open connection! ");
}

Keep your loop the way it is but wrap an If Statement around it to check if (dr.HasRows){} — MethodMan
– MethodMan, Commented Sep 26, 2012 at 20:42
Nope, my SqlDataReader has "HasRows" set to true... but no rows were actually loaded in. — Mike Gledhill
– Mike Gledhill, Commented Nov 15, 2021 at 14:45

Tim Schmelter · Accepted Answer · 2012-09-26 20:37:05Z

57

if(dr.HasRows)
{
    // ....
}
else
{
    MessageBox.Show("Reservation Number Does Not Exist","Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}

SqlDataReader.HasRows Property

answered Sep 26, 2012 at 20:37

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

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

1 Comment

Tim Over a year ago

Thanks this worked I was using the hasrows property just using it wrong I guess.

Moslem7026 · Accepted Answer · 2016-04-09 14:23:43Z

5

Add this to your code to check:

sqlCommand cmd = new sqlCommand();
SqlDataReader dr = cmd.ExecuteReader();

if(dr.HasRows)
{
    while(dr.Read())
    {
        //code
    }
}

edited Apr 9, 2016 at 14:23

user1134181

answered Sep 26, 2012 at 20:40

Moslem7026

3,3386 gold badges42 silver badges53 bronze badges

Comments

David B · Accepted Answer · 2012-09-26 20:37:10Z

2

The HasRows property may help you.

Property Value

Type: System.Boolean true if the SqlDataReader contains one or more rows; otherwise false.

answered Sep 26, 2012 at 20:37

David B

2,69821 silver badges26 bronze badges

Comments

Bob Kaufman · Accepted Answer · 2012-09-26 20:55:56Z

1

For some reason when I debug once it hits the while dr.Read() Code it steps out if it does not have a return result

I think what you're seeing here is that SQLDataReader.Read() returns false if there is not a next, or in this case a first record to read.

As others have responded, use the HasRows property to determine if you have any rows in the result set. Depending on what you need to accomplish, you may want to take advantage of the fact that Read() indeed returns false the first time its called for an empty result set.

edited Sep 26, 2012 at 20:55

answered Sep 26, 2012 at 20:50

Bob Kaufman

12.9k16 gold badges83 silver badges112 bronze badges

Collectives™ on Stack Overflow

How to check if SQLDataReader has no rows

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related