C# Potential Date format issue - System.Data.SqlClient.SqlException: 'Incorrect syntax near 'x'.'

Question

Purpose of the application is to select data from Sql database to the windows form data grid. I implemented the custom format for the date time picker but am still receiving this error. Here's the properties and following code of the application

Date Time Picker Properties

        private void startTimePicker1_MouseDown(object sender, MouseEventArgs e)
    {
        startTimePicker1.CustomFormat = "yyyy-MM-dd hh:mm:ss";
        startTimePicker1.Format = DateTimePickerFormat.Custom;
    }

    private void endTimePicker1_MouseDown(object sender, MouseEventArgs e)
    {
        endTimePicker1.CustomFormat = "yyyy-MM-dd hh:mm:ss";
        endTimePicker1.Format = DateTimePickerFormat.Custom;
    }

    private void loadBtn_Click(object sender, EventArgs e)
    {
        startTimePicker1.CustomFormat = "yyyy-MM-dd hh:mm:ss";
        startTimePicker1.Format = DateTimePickerFormat.Custom;

        endTimePicker1.CustomFormat = "yyyy-MM-dd hh:mm:ss";
        endTimePicker1.Format = DateTimePickerFormat.Custom;

        using (IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["DBName"].ConnectionString))
        {
            if(db.State==ConnectionState.Closed)
            {
                db.Open();
                string query = "select z.ItemName, x.Quantity, x.CreateDate" +
                                 " from Invoice_Itemized x inner join Inventory z on x.ItemNum = z.ItemNum inner join Departments d on z.Dept_ID = d.Dept_ID" +
                                $"where x.CreateDate BETWEEN '{ startTimePicker1 }' and '{ endTimePicker1 }' and d.SubType = 'TYPE'";

                liquorBindingSource.DataSource = db.Query<Liquor>(query, commandType: CommandType.Text);
            }
        }

Query outputs following value at runtime

select z.ItemName, x.Quantity, x.CreateDate from Invoice_Itemized x inner join Inventory z on x.ItemNum = z.ItemNum inner join Departments d on z.Dept_ID = d.Dept_IDwhere x.CreateDate BETWEEN 'System.Windows.Forms.DateTimePicker, Value: 7/18/2018 2:44:00 AM' and 'System.Windows.Forms.DateTimePicker, Value: 7/18/2018 4:19:01 AM' and d.SubType = 'TYPE'

This query successfully executes in SSMS, I've ran into a huge wall after several hours of debugging this mess.

Possible duplicate of [How to pass datetime from c# to sql correctly?](https://stackoverflow.com/questions/6570621/how-to-pass-datetime-from-c-sharp-to-sql-correctly) — Liam, Jul 18 '18 at 09:26
missing space at `on z.Dept_ID = d.Dept_IDwhere x.CreateDate` and `'System.Windows.Forms.DateTimePicker, Value: 7/18/2018 2:44:00 AM'` isn't a valid SQL date — fubo, Jul 18 '18 at 09:26
You can also remove your `if` test. The chances that you'll receive something *other* than a closed connection from a call to `IDbConnection db = new SqlConnection(...` is 0. — Damien_The_Unbeliever, Jul 18 '18 at 10:14

Zohar Peled · Answer 1 · 2018-07-18T10:11:50.510

1

You have two different issues here.

One is that you are passing the reference of the DateTimePicker into the string interpulation, causing the string to contain the value returned from DateTimePicker.ToString() instead of the the value you wanted.

The second issue is that you shouldn't pass dates as strings to database in the first place. Instead, you should use SQL parameters to pass instances of DateTime. I'm not sure what ORM you are using so I can't give you a code sample for it.

Also, As fubo wrote in the comment to the question, there is a missing space before the Where keyword.

edited Jul 18 '18 at 10:11

answered Jul 18 '18 at 09:32

Zohar Peled

79,642
10
69
121

2

Looks a lot like Dapper but you're right, until the OP clarifies, it's not possible to give concrete corrections. They also need to fix the missing space issue that fubo identified in the comments. – Damien_The_Unbeliever Jul 18 '18 at 09:34
@Damien_The_Unbeliever I think so too, but I've learned that assuming things about questions usually leads to wrong answers. Didn't even see that missing space before, I'll edit that into my answer as well, thanks!. – Zohar Peled Jul 18 '18 at 10:10
I am using Dapper. Here are the properties I've declared, public class Liquor { public string ItemName { get; set; } public decimal ItemNum { get; set; } public DateTime CreateDate { get; set; } } – Gonzo Nick Jul 18 '18 at 17:43
In that case, go with Marc's answer. – Zohar Peled Jul 18 '18 at 17:44
Thanks for the feedback, as soon as I've compiled my solution I'll update my post – Gonzo Nick Jul 18 '18 at 17:45
It seems logical without a doubt, wouldn't it be best too create a stored procedure for this query? My only confusion is that the start and end dates are pulling from the same column. Which I've never set parameters for the same column in any application.... or is not possible.... – Gonzo Nick Jul 18 '18 at 17:47
Apologize in advance for my amateur debug/logical thought process in this case – Gonzo Nick Jul 18 '18 at 17:48
1

It doesn't matter that they both are used on the same column, they are different parameters... I personally like stored procedures. I use them all the time - but it shouldn't make that much difference for such a query. – Zohar Peled Jul 18 '18 at 18:02

score 1 · Accepted Answer · answered Jul 18 '18 at 09:52

string query = "select z.ItemName, x.Quantity, x.CreateDate" +
      " from Invoice_Itemized x inner join Inventory z on x.ItemNum = z.ItemNum inner join Departments d on z.Dept_ID = d.Dept_ID" +
      $"where x.CreateDate BETWEEN '{ startTimePicker1 }' and '{ endTimePicker1 }' and d.SubType = 'TYPE'";

liquorBindingSource.DataSource = db.Query<Liquor>(query, commandType: CommandType.Text);

This is incredibly harmful, and can lead to multiple problems, including formatting / culture issues (it working on some machines, but not others, depending on the locale) - and (more importantly) SQL injection risks. It also doesn't allow query-plan cache re-use.

Since it looks like you're using "dapper" here, dapper is designed to make it easy to parameterize:

liquorBindingSource.DataSource = db.Query<Liquor>(@"
        select z.ItemName, x.Quantity, x.CreateDate
        from Invoice_Itemized x
        inner join Inventory z on x.ItemNum = z.ItemNum
        inner join Departments d on z.Dept_ID = d.Dept_ID
        where x.CreateDate BETWEEN @start and @end and d.SubType = 'TYPE'",
    new { start = startTimePicker1.Value, end = endTimePicker1.Value });

This passes the values as typed parameters (assuming that .Value here is DateTime), avoiding all the local issues and removing the risk of SQL injection. If the .Value is not DateTime, but is just string, then I would use:

new { start = DateTime.Parse(startTimePicker1.Value),
        end = DateTime.Parse(endTimePicker1.Value) }

as the second argument.

If you're going to punt on it being Dapper, punt on it being the WinForms [`DateTimePicker`](https://learn.microsoft.com/en-us/dotnet/api/system.windows.forms.datetimepicker?view=netframework-4.7.2). :-) The screen shot of the controls properties seem like a good match up and other event handlers also suggest WinForms. — Damien_The_Unbeliever, Jul 18 '18 at 09:58

C# Potential Date format issue - System.Data.SqlClient.SqlException: 'Incorrect syntax near 'x'.'

2 Answers2