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, Sep 26 '12 at 20:42
Nope, my SqlDataReader has "HasRows" set to true... but no rows were actually loaded in. — Mike Gledhill, Nov 15 '21 at 14:45

score 55 · Accepted Answer · answered Sep 26 '12 at 20:37

55

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

SqlDataReader.HasRows Property

answered Sep 26 '12 at 20:37

Tim Schmelter

450,073
74
686
939

1

Thanks this worked I was using the hasrows property just using it wrong I guess. – Tim Sep 26 '12 at 20:44

score 5 · Answer 2 · edited Apr 09 '16 at 14:23

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 09 '16 at 14:23

answered Sep 26 '12 at 20:40

Moslem7026

3,290
6
40
51

score 2 · Answer 3 · answered Sep 26 '12 at 20:37

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 '12 at 20:37

David B

2,688
18
25

Bob Kaufman · Answer 4 · 2012-09-26T20:55:56.610

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.

How to check if SQLDataReader has no rows

4 Answers4

Linked

Related