Parse date to SQL when not in stored procedure

Question

I am trying to parse dates to SQL but the command appears to ignore parameters:

private void updateTasksDGV(DateTime? start, DateTime? end)
    {
        if (start == null)
        {
            string query = "select * from CRS_Diary where cast(EndDate as date) <= convert(date,GETDATE()) and Complete = 0";
            dgvTasks.DataSource = GetTable(query);
            return;
        }

        try
        {
            using (SqlConnection connection = getSQlCon())

            {

                using (SqlCommand cmd = new SqlCommand(@"Select * from crs_diary where enddate between @Start and @End and complete = 0", connection))
                {
                    MessageBox.Show("made it to conn " + start.ToString() + "  -  " + end.ToString());
                    cmd.CommandType = CommandType.Text;

                    SqlParameter parameter = new SqlParameter();
                    parameter.ParameterName = "@Start";
                    parameter.SqlDbType = SqlDbType.DateTime;
                    parameter.Direction = ParameterDirection.Input;
                    parameter.Value = start;

                    SqlParameter parameter2 = new SqlParameter();
                    parameter2.ParameterName = "@End";
                    parameter2.SqlDbType = SqlDbType.DateTime;
                    parameter2.Direction = ParameterDirection.Input;
                    parameter2.Value = end;



                    cmd.Parameters.Add(parameter);
                    cmd.Parameters.Add(parameter2);

                    //cmd.Parameters.AddWithValue("@Start", SqlDbType.DateTime).Value = start.Value;
                    //cmd.Parameters.AddWithValue("@End", SqlDbType.DateTime).Value = end.Value;

                    connection.Open();
                    cmd.ExecuteNonQuery();

                    System.Windows.Forms.Clipboard.SetText(cmd.CommandText.ToString());

                }
            }
        }
        catch (System.Data.DataException ex)
        {
            MessageBox.Show(ex.Message);

        }

    }

I can make it pass parameters using commandtype stored procedure but that is not what I would like to do in this case. The output/DGV is doing not doing anything.

Not sure what your asking - ExecuteNonQuery() is not the thing to use as you are using a SELECT query, you want ExecuteReader() & a SqlDataReader. — Alex K., Aug 02 '18 at 12:22
The CommandType doesn't affect how parameters are used. Why do you think they are ignored? They aren't btw - if you didn't specify a value for parameter you'd get an exception. — Panagiotis Kanavos, Aug 02 '18 at 12:22
cmd.Parameters.add("@Start",SqlDbType.DateTime).Value = start; and like Alex K writes. You need to use ExecuteReader if it is a select statement. — SqlKindaGuy, Aug 02 '18 at 12:25
If you mean that, even after `ExecuteNonQuery`, the `CommandText` still contains `@Start` and `@End` rather than specific values, that's entirely correct. Parameters are a concept understood and explicitly modelled by both ADO.Net and SQL Server. It's not doing some form of *string substitution*. — Damien_The_Unbeliever, Aug 02 '18 at 12:35
@PanagiotisKanavos I thought the command.tostring would show me the command being executed - I was wrong — Syed Abbas, Aug 02 '18 at 13:22
`start and end will always both be present - I just decided to test only on start` Your code will be much simpler if you change the nullable parameters to not-nullable then. — mjwills, Aug 02 '18 at 13:24
@SyedAbbas the command is what you typed. Parameters aren't placeholders for string replacement, they are the same thing as a function's parameters in another language. YOu can try the same query in SSMS by creating two variables named `@Start` and `@End` — Panagiotis Kanavos, Aug 02 '18 at 13:42
@mjwills thank you I have achieved the desired result by using: Select * from crs_diary where cast(enddate as date) >= cast(@Start as date) and cast(endDate as date) <= cast(@End as date) and complete = 0 — Syed Abbas, Aug 03 '18 at 08:21
I'd suggest using `Select * from crs_diary where enddate >= cast(@Start as date) and endDate < dateadd(dd, 1, cast(@End as date)) and complete = 0` since it will give the same results (as your current query) but perform a bit better. — mjwills, Aug 03 '18 at 08:33

Cetin Basoz · Accepted Answer · 2018-08-02T12:51:55.590

You should use ExecuteReader instead of ExecuteNonQuery. Also note that with SQL server you shouldn't use BETWEEN for date ranges, instead use >= and < . Edited your code a bit:

private void updateTasksDGV(DateTime? start, DateTime? end)
{
    if (!end.HasValue)
    {
      end = DateTime.MaxValue;
    }
    if (!start.HasValue)
    {
        string query = "select * 
            from CRS_Diary 
            where EndDate <= GETDATE() and Complete = 0";
        dgvTasks.DataSource = GetTable(query);
        return;
    }

    try
    {
        DataTable tbl = new DataTable();
        using (SqlConnection connection = getSQlCon())

        {

            using (SqlCommand cmd = new SqlCommand(@"Select * from crs_diary 
            where enddate >= @Start and endDate < @End and complete = 0", connection))
            {
                MessageBox.Show("made it to conn " + start.ToString() + "  -  " + end.ToString());

                cmd.Parameters.Add(new SqlParameter("@Start", SqlDbType.DateTime));
                cmd.Parameters.Add(new SqlParameter("@End", SqlDbType.DateTime));

                cmd.Parameters["@Start"].Value = start;
                cmd.Parameters["@End"].Value = end;

                connection.Open();
                tbl.Load(cmd.ExecuteReader());

                System.Windows.Forms.Clipboard.SetText(cmd.CommandText.ToString());
            }
        }
        dgvTasks.DataSource = null;
        dgvTasks.DataSource = tbl;
    }
    catch (System.Data.DataException ex)
    {
        MessageBox.Show(ex.Message);

    }
}

Note, if you are asking for data for say July 2018, start would be: 2018/7/1 and end would be 2018/8/1 (minimum datetime to exclude).

Thank you - I forgot to assign the table to the datagrid and use executereader - I am learning and people like you deserve to be appreciated — Syed Abbas, Aug 02 '18 at 13:20

Parse date to SQL when not in stored procedure

1 Answers1