Check if SqlDataReader has rows

Question

I want to check if the staff member does not already exists in the database before I add. In the below code, the if(dr.HasRows) statement executes even if the select statement returned no results. I don't know what I'm doing wrong. Please help.

using (SqlCommand sqlCommand = new SqlCommand("select COUNT(*) from 
       [CLIENT_PROCESSING_CLIENT_INFORMATION] where Client_NB_Number 
       = @StaffNo", sqlConn))
{
    sqlCommand.Parameters.AddWithValue("@StaffNo", strStaffNumber);
    SqlDataReader dr = sqlCommand.ExecuteReader();
    string strMsg = "";
    if(dr.HasRows)
    {
       strMsg = "NB" + strStaffNumber + " already exists.";
       String Script = "<Script language=\"javascript\">alert('" + strMsg + " ') 
          </script>";
       if (!Page.ClientScript.IsClientScriptBlockRegistered("OpenAlert"))
          Page.ClientScript.RegisterClientScriptBlock(this.GetType(), 
                "OpenAlert", Script);
       return;
     }
}

James · Accepted Answer · 2013-11-04 12:24:45Z

2

You are returning Count(*) which is always going to return a single row with the result.

You don't need a reader here, you can use ExecuteScalar which returns the first column from the first row by default e.g.

if ((int)sqlCommand.ExecuteScalar() > 0)
{
    strMsg = "NB" + strStaffNumber + " already exists.";
    String Script = "<Script language=\"javascript\">alert('" + strMsg + " ') </script>";
    if (!Page.ClientScript.IsClientScriptBlockRegistered("OpenAlert"))
        Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "OpenAlert", Script);
    return;
}

edited Nov 4, 2013 at 12:24

answered Nov 4, 2013 at 12:19

James

82.4k19 gold badges172 silver badges245 bronze badges

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

Comments

LINQ2Vodka · Accepted Answer · 2013-11-04 12:19:31Z

2

SELECT COUNT(*) will always return 1 result meaning rows count.
You can also use ExecuteScalar instead of ExecuteReader to get this value.

answered Nov 4, 2013 at 12:19

LINQ2Vodka

3,0362 gold badges31 silver badges49 bronze badges

Collectives™ on Stack Overflow

Check if SqlDataReader has rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related