CODEWITHSUNDEEP
c#sql
Kgabo
Kgabo

Reputation: 11

Check if SqlDataReader has rows

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;
     }
}

Upvotes: 0

Views: 1164

Answers (2)

James
James

Reputation: 82136

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;
}

Upvotes: 2

LINQ2Vodka
LINQ2Vodka

Reputation: 3036

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

Upvotes: 2

Related Questions