Query from C# code outputting differently from SQL Server query

Question

Working with:

• ASP.Net web-forms application
• C# code not vb.net
• SQL Server with hard coded test data

Note: this issue doesn't cause any errors or cause any disruption in the code, however it outputs differently from expected.

What I am trying to do is populate a Gridview using code behind file, which can be updated by the user on button click.

Code to populate:

protected void PopulateReport()
{
    // create connection  and add commands
    SqlConnection con = new SqlConnection(GetConnectionString());
    con.Open();

    if(RP_SelectEmp.Text == "ALL")
    {
         string query1 = "SELECT RequestID, empName, RequestType, RequestDesc, RequestStartDate FROM TOR WHERE (RequestStartDate > @StartDate)" +
                                    " AND (RequestEndDate < @EndDate) AND (granted = @State)";

         SqlCommand cmd = new SqlCommand(query1, con);

         // needed conversions
         DateTime startD = Convert.ToDateTime(RP_FromDateSelect.Text);
         DateTime endD = Convert.ToDateTime(RP_EndDateSelect.Text);
         Boolean state = Convert.ToBoolean("True");

         // needed parameters
         cmd.Parameters.AddWithValue("@State", state);
         cmd.Parameters.AddWithValue("@StartDate", startD);
         cmd.Parameters.AddWithValue("@EndDate", endD);

         // import into gridview
         using (SqlDataReader reader = cmd.ExecuteReader())
         {
             if (reader.Read())
             {
                 GridView1.DataSource = reader;
                 GridView1.DataBind();
             }
             else
             {
                 RP_ErroField.Text = "failed to bind data (reader not read) check C# code";
             }
         }

         con.Close();
    }
}

This compiles and returns no errors but outputs:

The database table includes all the correct data types and column names:

What I have tried:

1. creating a static data Source and passing in the same select string from the above code (this returns the hard coded event, with the same exact input from the fields seen in the picture) - which tells me the query isn't wrong AddingDataSource,InputingData,Correct event Grabbed

2. I have tried changing the conversions in the code, DateTime.Parse and Convert.ToDateTime had the same result. Same can be said for bool and Boolean

3. I have tried the each where clause separately and got the same no data to display result.

4. I have debugged this if statement for 2 hrs and all the variable data is doing exactly what it should (going to the if, converting, setting the values, running the reader, and databinding)

I don't know what else to try. I would like help on an action plan to fix this; maybe I am missing something, or my approach is wrong/outdated.

Answer 1

This is really just a debugging exercise.

First, double-check that you haven't simply named the two date-picker controls backwards! That happens a lot.

Next: go to SSMS, and take your existing query:

SELECT RequestID, empName, RequestType, RequestDesc, RequestStartDate
FROM TOR 
WHERE (RequestStartDate > @StartDate)
AND (RequestEndDate < @EndDate) AND (granted = @State)

Now; we know that you've used Convert.ToDateTime to parse the dates, and that's great. You might want to check the cultures that it is parsing to what you expect it to parse to (is 1/2/2018 the first of Feb? or the 2nd of Jan?), and when you're 100% sure what the actual date of startD and endD are, prepend these to your query using an unambiguous format (just to help us debug); do the same thing with state; for example:

DECLARE @StartDate datetime = '01 Jan 2018';
DECLARE @EndDate datetime = '03 Jan 2018';
DECLARE @State bit = 1;

or are they?

DECLARE @StartDate datetime = '01 Jan 2018';
DECLARE @EndDate datetime = '01 March 2018';
DECLARE @State bit = 1;

So now we have spoofed the parameters and you have the exact same query: run it. 99% of the time, doing this will show you what is wrong with the query. I would expect that the query in SSMS now behaves like the query from your application does. So; now go fix it!