mark mark - 4 months ago 9
SQL Question

Sql Query Data Reader Returning True even it's False in C#

Good day!

It's taking me hours why my query returns a true even it's false.

Here's my code.

public SqlDataReader Check(BEL bel) {
SqlCommand cmd = new SqlCommand();
cmd.Connection = dbcon.getcon();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT SUM(Total) as OverallTotal FROM table WHERE Id=@Id AND DateFrom=@From AND DateTo=@To AND Status='Without'";
cmd.Parameters.AddWithValue("@Id",bel.CLEmpID);
cmd.Parameters.AddWithValue("@From", bel.CLPayrollFrom);
cmd.Parameters.AddWithValue("@To", bel.CLPayrollTo);

SqlDataReader dr = cmd.ExecuteReader();
return dr;
}


Suppose the parameterized values are:

@Id = 0001
@From = 1/28/2016
@To = 1/29/2016


Here's my method calling the datareader

SqlDataReader drCheck;

drCheck = bal.Check(bel);
if (drCheck.HasRows == true)
{
drCheck.Read();
// I'm inside the computation of OverallTotal
}else{
drCheck.Close();
// I'm out
}
drCheck.Close();


The problem is, when the value of my "To", for instance, is 1/30/2016, it suppose to go to the false which is out of the true condition but it is not.

Please help. Thanks in advance

Answer

In your case you don't need a Reader, you can use ExecuteScalar:

public decimal? Check(BEL bel)
{
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = dbcon.getcon();
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT SUM(Total) as OverallTotal FROM table WHERE Id=@Id " + 
                      "AND DateFrom=@From AND DateTo=@To AND Status='Without'";
    cmd.Parameters.AddWithValue("@Id", bel.CLEmpID);
    cmd.Parameters.AddWithValue("@From", bel.CLPayrollFrom);
    cmd.Parameters.AddWithValue("@To", bel.CLPayrollTo);

    object obj = cmd.ExecuteScalar();
    decimal? value = null;
    if (obj != DBNull.Value)
        value = Convert.ToDecimal(obj);

    return value;
}

decimal? total = bal.Check(bel);
if (total.HasValue)
{
    // do something with the total.Value
}
else
{

}
Comments