Reputation: 11
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
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
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