Select Query between two dates not working

Question

I have this block of code in one form of mine,

using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.ConString))
{
    query = "select SUM(TotalMinutes) as TotalMin, SUM(OTMinutes) as OTMin from EmpLog where EmpID = @EmpID and LogDate >= @empdate1 and LogDate <= @empdate2";
    conn.Open();
    using (SqlCommand cmd2 = new SqlCommand(query, conn))
    {
        cmd2.Parameters.AddWithValue("@empdate1", dtp_from.Value.ToString("yyyy-MM-dd"));
        cmd2.Parameters.AddWithValue("@empdate2", dtp_to.Value.ToString("yyyy-MM-dd"));
        cmd2.Parameters.Add(new SqlParameter() { ParameterName = "@EmpID", Value = listBox1.SelectedValue.ToString() });
        var da = new SqlDataAdapter(cmd2);
        DataTable dt = new DataTable();
        da.Fill(dt);             

        decimal decminutes = Convert.ToDecimal(dt.Rows[0]["TotalMin]"].ToString());
        decimal decOTminutes = Convert.ToDecimal(dt.Rows[0]["OTMin"].ToString());

I have made a similar code work on different forms but somehow this won't work. I've tried SqlDataReader but it won't even go inside a while reader.read loop.

also upon trying out the query via ssms, it works as expected. but if i try other methods such as the sqldatareader, it goes into null exception.

EDIT: I've tried removing the WHERE clause from my query, and i'm able to return the rows.

for some odd reason, the datetimepicker values isn't properly being matched with the values in the database. even if when I casted it to string, it exactly matches, but then again, its still returning dbnull.

If the query works in SSMS then why dont you write a stored procedure that will return the dataset. You can just use SqlDataReader to harvest the values. This practice is much preferred as well. — Yawar Murtaza, Feb 02 '17 at 20:50
Maybe not an exact duplicate question, but see the answers to this question: http://stackoverflow.com/questions/425870/using-datetime-in-a-sqlparameter-for-stored-procedure-format-error — Peter B, Feb 02 '17 at 20:51
Did you check if your parameters are correct: `@empdate2 >= @empdate1`? It's a common error when using `BETWEEN`. — dnoeth, Feb 02 '17 at 21:08
@dnoeth Yes, it is working on other forms of mine. that's why i'm puzzled as to why it isn't working on this one. — Seiren, Feb 02 '17 at 21:27
@PeterB Thank you for taking the time to assist me in this! I will check it out :) — Seiren, Feb 02 '17 at 21:28
@YawarMurtaza Thank you for the suggestion, I am still learning SQL and haven't explored stored procedures yet, but I will look into it! :) — Seiren, Feb 02 '17 at 21:29

score 4 · Accepted Answer · edited Sep 18 '17 at 07:54

The BETWEEN Keyword

You may want to consider using the BETWEEN keyword within SQL for checking if a value exists in a given range as demonstrated in this SQL example :

SELECT SUM(TotalMinutes) AS TotalMin, 
       SUM(OTMinutes) AS OTMin 
  FROM EmpLog 
 WHERE EmpID = @EmpID 
   AND LogDate BETWEEN @empdate1 AND @empdate2"

Avoid Unnecessary Casting

Additionally, you may want to avoid explicitly casting your DateTime values as strings. The SQL provider should have no trouble translating those values into their appropriate SQL equivalents.

So applying both those changes, should give you something like this :

using (var conn = new SqlConnection(Properties.Settings.Default.ConString))
{
     conn.Open();
     var query = "SELECT SUM(TotalMinutes) AS TotalMin, SUM(OTMinutes) AS OTMin FROM EmpLog WHERE EmpID = @EmpID AND LogDate BETWEEN @empdate1 AND @empdate2";
     using (var cmd = new SqlCommand(query, conn))
     {
           cmd.Parameters.AddWithValue("@empdate1", dtp_from.Value);
           cmd.Parameters.AddWithValue("@empdate2", dtp_to.Value);
           cmd.Parameters.Add("@EmpID", listBox1.SelectedValue);
           var da = new SqlDataAdapter(cmd2);
           DataTable dt = new DataTable();
           da.Fill(dt);     

           // Other code omitted for brevity
     }        
}

Example Using DataReader

using (var conn = new SqlConnection(Properties.Settings.Default.ConString))
{
     conn.Open();
     var query = "SELECT SUM(TotalMinutes) AS TotalMin, SUM(OTMinutes) AS OTMin FROM EmpLog WHERE EmpID = @EmpID AND LogDate BETWEEN @empdate1 AND @empdate2";
     using (var cmd = new SqlCommand(query, conn))
     {
           cmd.Parameters.AddWithValue("@empdate1", dtp_from.Value);
           cmd.Parameters.AddWithValue("@empdate2", dtp_to.Value);
           cmd.Parameters.Add("@EmpID", listBox1.SelectedValue);
           using(var reader = cmd.ExecuteReader())
           {
                while(reader.Read())
                {
                     var minutes = Convert.ToDecimal(reader["TotalMin"]);
                     var otMinutes = Convert.ToDecimal(reader["OTMin"]);

                     // Do something here
                }
           }
     }        
}

Thank you so much for taking the time to help me. However, I tried your suggestions above, I'm getting the error, Object cannot be cast from DBNull to other types. — Seiren, Feb 02 '17 at 21:26

Select Query between two dates not working

1 Answers1