c# ms access date input wrong format

Question

In my program i input a record to MS Access database like this

INSERT INTO Stock VALUES('"+ textCode.Text +"', '#"+ datetimepickerDate.Value +"#', somenumber)

With datetimepicker.Value says 09/06/2022 19:35:14

But for some reason when i open MS Access and checked the record, the date magicaly turns 06/09/2022 19:35:14

I didn't change the datetimepicker's format and it should use system date format, and now i can't select or update the record because of the day and the month switches place.

It works without a problem on my PC, but it wont do with my one specific friend's.

How do i fix this?

Thanks~

Heinzi · Answer 1 · 2022-06-09T12:05:25.553

Here: '#"+ datetimepickerDate.Value +"#' the following happens:

datetimepickerDate.Value returns a DateTime.
That DateTime is used in string concatenation, which causes an implicit conversion to string, using your system date format (09/06/2022, dd/mm/yyyy).
You add a hash in front and in the back (#09/06/2022#). Now it's a string containing an "MS Access date literal".

The problem is that MS Access date literals are always in the format mm/dd/yyyy. Thus, your day and month value may get "switched", depending on the user's system date format.

Solution: Don't use string concatenation for SQLs, use parameterized SQL instead:

How can I add user-supplied input to an SQL statement?

Example:

var sql = "INSERT INTO Stock VALUES (?, ?, ?)";

using (var cmd = new OleDbCommand(sql, myDbConnection))
{
    cmd.Parameters.AddWithValue("", textCode.Text);
    cmd.Parameters.AddWithValue("", datetimepickerDate.Value);
    cmd.Parameters.AddWithValue("", somenumber);
    cmd.ExecuteNonQuery();
}

c# ms access date input wrong format

1 Answers1