Inserting a Date Time into Microsoft Access in Microsoft .NET (C#)

I just had an issue, in a project that I've inherited, where dates are are formatted for insertion into a query by calling .ToString() on the DateTime structure. This worked fine until I changed the culture, which caused the date to be formatted in a way that Microsoft Access didn't like... which resulted in me finding out that this was going on. 

I'm going to show two ways, the quick way, and the parameterized query way.

Query Contatenation Method

Please note that I (and probably a lot of other people) would not generally recommend this as a solution. If you concatenate strings together to form your queries you are opening yourself up to Sql injection attacks if you don't know or aren't careful with what you are doing.

Say we have a query like this, which is a simplified example of what was causing the error mentioned:

string sql = "INSERT INTO TEST (MyDateTime) VALUES(#" 
    + DateTime.Now + "#)"

By default ToString() will be called on the DateTime.Now value (which returns a DateTime structured) and the ToString() method will format the DateTime based on the current culture of the thread that you are working on. This is not what we want because Microsoft Access might either 1. Not be able to parse the date resulting in an error or 2. Could pass the date wrongly resulting in odd dates when you retrieve them from the database later.

The quickest/dirties/simplest way to fix this is to do something like this instead of DateTime.Now:

DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

So as as simple example the query could be:

string sql = "INSERT INTO TEST (MyDateTime) VALUES(#" 
    + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "#)";

Parameterized Query Method
A better way to structure the code is to create a Parameterized Query which will mean

1. No need to format the date, we just pass it as a parameter to the query
2. We don't need to worry about correctly quoting the date when it is converted to a string (In the above example I've used "#" characters each side of the date string. You can use single quotes also though.)
3. We don't need to do any concatenation as the query is considered essentially a template string
4. This method avoids places where you might accidently or ignorantly open yourself up to Sql injections attacks.

The code would be something like the following. (Note that this code doesn't include error handling code, I just made as simple an example as possible)

OleDbConnection connection = null;
string sql= "INSERT INTO TEST (MyDateTime) VALUES(?)";
OleDbCommand command = new OleDbCommand(sql, connection);
CmdCheck.Parameters.Add( new OleDbParameter("MyDateTime"
    , DateTime.Now));
connection.Dispose();

In the above example the date will be converted to a correctly formatted string and the ? inside the VALUES() clause of the query will be replaced with this date (with correct escaping etc.)