System.Data.SqlClient.SqlException: 'Incorrect syntax near '9'

Question

I have two DateTimePickers (dateStart and dateStop) in a winform. I've set dateStart and dateSop's format like this:

    dateStart.Format = DateTimePickerFormat.Custom;
    dateStart.CustomFormat = "yyyy:MM:dd HH:mm:ss";
    dateStop.Format = DateTimePickerFormat.Custom;
    dateStop.CustomFormat = "yyyy:MM:dd HH:mm:ss";

I want to count number of rows from my datebase's table between dateStart and dateStop. My select string:

string stmt = "SELECT COUNT(*) FROM [dbo].[tbl_TagLogging] WHERE DateTime BETWEEN  " + Convert.ToString(dateStart.Value) + " AND " + Convert.ToString(dateStop.Value);

And it's result:

This is how link DateStart and DateStop paramaters of my datebase with DateTimePickers.

 cmd.Parameters.Add("@DataStart", SqlDbType.DateTime).Value = dateStart.Value;// new DateTime(dateStart.Value);
 cmd.Parameters.Add("@DataStop", SqlDbType.DateTime).Value = dateStop.Value;// new DateTime(2020, 02, 05, 13, 12, 25, 703);//2020, 02, 05, 13, 12, 25, 703   //2020, 02, 05, 13, 06, 50, 700

The row counting works perfectly without WHERE clause.

count = (int)cmdCount.ExecuteScalar();//this is how I count the rows

I'm getting here a 'System.Data.SqlClient.SqlException: Incorect syntax near 9'.

I kinda don't understand why if dateStart's format is "yyyy:MM:dd HH:mm:ss", dateStart.value is returned in another format. On my UI, dateStart is displayed in the right format (the one from the code above). But I might have done something faulty in my stmt string. Any thoughts on what is wrong?

EDIT 1 (my entire code):

 private void button_Click(object sender, EventArgs e)
    {
        dateStart.Format = DateTimePickerFormat.Custom;
        dateStart.CustomFormat = "yyyy:MM:dd HH:mm:ss";
        dateStop.Format = DateTimePickerFormat.Custom;
        dateStop.CustomFormat = "yyyy:MM:dd HH:mm:ss";
        string stmt = "SELECT COUNT(*) FROM [dbo].[tbl_TagLogging] WHERE DateTime BETWEEN  @DataStart AND @DataStop";         // WHERE DateTime BETWEEN  " + Convert.ToString(dateStart.Value) + " AND " + Convert.ToString(dateStop.Value)
       // string stmt = "SELECT COUNT(*) FROM [dbo].[tbl_TagLogging] WHERE DateTime BETWEEN  '" + Convert.ToString(dateStart.Value) + "' AND '" + Convert.ToString(dateStop.Value) + "'";
        int count = 0;

        using (SqlConnection connection = new SqlConnection(@"Data Source=DESKTOP-JQSJAF8\SQLEXPRESS;Initial Catalog=TRTF_TagLogging;Integrated Security=True; MultipleActiveResultSets = true")) 
        {
            using (SqlCommand cmd = new SqlCommand("PS_TagLogging", connection))// used for stored proc
            {
                using (SqlCommand cmdCount = new SqlCommand(stmt, connection))//used to count number of rows of my table
                {
                    //yyyy:MM:dd hh:mm:ss
                    connection.Open();
                    cmd.CommandType = CommandType.StoredProcedure;                        
                    cmd.Parameters.Add("@DataStart", SqlDbType.DateTime).Value = dateStart.Value;
                    cmd.Parameters.Add("@DataStop", SqlDbType.DateTime).Value = dateStop.Value;
                    var values = new List<double>();
                    var valData = new List<DateTime>();
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        // values.Add(reader.GetInt64(0));//reads the values of first column (ID) from DB
                        values.Add(reader.GetFloat(1));//reads the values of second column (Tag1) from DB 
                        valData.Add(reader.GetDateTime(2));//reads the values of third column (DateTime) from DB 
                    }

                    arrDate = valData.ToArray();
                    arrVal = values.ToArray();

                    count = (int)cmdCount.ExecuteScalar();
                    plot1.XAxes[0].ScaleDisplay.TextFormatting.Style = Iocomp.Types.TextFormatDoubleStyle.DateTime;
                    plot1.XAxes[0].ScaleRange.Span = 1.0 / 120.0 ; //30sec Span;



                    for (int i=0; i<count; i++)
                    {
                        plot1.Channels.Trace[0].AddXY(arrDate[i], arrVal[i]);
                    }


                    connection.Close();
                }
            }
        }
        MessageBox.Show("Nr. lines: " + count);
         Random().NextDouble() * 100);
    }

Answer 1

As already mentioned in the comments and proofed by your other command, you should use parameters. And here is how it should look in your case:

string stmt = "SELECT COUNT(*) FROM [dbo].[tbl_TagLogging] WHERE DateTime BETWEEN @DataStart AND @DataEnd";
using (var cmd = new SqlCommand(stmt))
{
    cmd.Parameters.Add("@DataStart", SqlDbType.DateTime).Value = dateStart.Value;
    cmd.Parameters.Add("@DataStop", SqlDbType.DateTime).Value = dateStop.Value;
    var reader = cmd.ExecuteReader();

    // Process the data from the reader.
}

But maybe you should think about to completely drop these things and use Entity Framework (Core) and model your DBContext and just work with LINQ-to-SQL to retrieve data from your database.

Update

After you updated your question and posted the whole function, the problem is quite easy. You simply missed to add the parameters to your second query and added it only to the first one. So please update accordingly to something like this:

cmdCount.Parameters.Add("@DataStart", SqlDbType.DateTime).Value = dateStart.Value;
cmdCount.Parameters.Add("@DataStop", SqlDbType.DateTime).Value = dateStop.Value;
count = (int)cmdCount.ExecuteScalar();

Nevertheless I hope this is not really your whole function, cause in that case requesting the count through this second query is completely useless. You use that value to iterate over the array you extracted from the first query. So my assumption would be that the variables arrDate.Length and arrVal.Length already contain the value that you need. So you should really check if these values (the length property of the array and the count result of your query) are the same and if not, why they differ and find the root cause for this issue. If they are the same, just use your local existing value and don't bother asking the SQL serverb for something you already know.

Answer 2

Everything people told is ok, but i think there are missing quotes. Try:

string stmt = "SELECT COUNT(*) FROM [dbo].[tbl_TagLogging] WHERE DateTime BETWEEN  '" + Convert.ToString(dateStart.Value) + "' AND '" + Convert.ToString(dateStop.Value) + "'";