Insert DateTime into Access

Question

The problem: I'm trying to insert a date time into an access database using the Oledb interface in C#.

Hacking solution: Generate my on insert string without using command.Properties

I can insert text into the database with no problem, but when trying datetime, I end up with this error: System.Data.OleDb.OleDbException {"Data type mismatch in criteria expression."}

There are several posts similar to this but alas with no working solution.

Here is my code:

void TransferData()
{
    string instCmd = Get_InsertCommand(0); // hard coded table 0 for testing

    Fill_ProductTable_ToInsert();

    con.Open();


    // It would be nice not to have to separate the date indexes
    int[] textIndex = { 0, 1, 2, 3, 4, 7 };
    int[] dateIndex = { 5, 6 };
    try
    {
        foreach (DataRow row in DataToStore.Tables[0].Rows)
        {
            OleDbCommand command = new OleDbCommand();
            command.Connection = con;

            command.CommandText = instCmd;

            foreach(int j in textIndex)
                command.Parameters.AddWithValue("@" + j, row[j]);
            foreach (int j in dateIndex)
            {

                // TESTING CODE
                ///////////////////////////////////////////////////////////////////////////

                string input = "#\'" +DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") +"\'#";
                command.Parameters.AddWithValue("@" + j,    input.ToString());
                Program.WriteLine(input.ToString());

                ///////////////////////////////////////////////////////////////////////////
            }


            command.ExecuteNonQuery();
        }
    }
    finally
    {
        con.Close();
    }
}

string Get_InsertCommand(int i)
{
    string sqlIns = "INSERT INTO " + DataToStore.Tables[0].TableName + " (";
    string temp = "VALUES (";
    for (int j = 0; j < expected_header[i].Length - 1; j++)
    {
        sqlIns += expected_header[i][j] + ", ";
        temp += "@" + j + ", ";
    }

    int lastIndex = expected_header[i].Length -1;
    sqlIns += expected_header[i][lastIndex] + ") ";
    temp += "@" + lastIndex + ")";
    sqlIns += temp;

    return sqlIns;
}

Inside the area labeled testing code, I have tried every permutation of date time I could think of. I tried every format with # and ' I tried these formats: yyyy-MM-dd, yyyyMMdd, yyyy\MM\dd, yyyy/MM/dd I also tried ToOADate() And ToString(), ToShortDateString()

I also tried setting the database to accept ANSI-92 Sql

I'm running out of ideas.

Note: This code is set up to deal with multiple tables from multiple databases, mind the loops...

Solution

Use parameters properly, and don't worry about the format of the datetime value that you concatenate in your query. I don't understand why you want to convert the datetime value to a string value ?

DateTime theDate = new DateTime(2012,10,16);
var cmd = new OleDbCommand();
cmd.CommandText = "INSERT INTO sometable (column) VALUES (@p_bar)";
cmd.Parameters.Add ("@p_bar", OleDbType.DateTime).Value = theDate;

Comments

[user1642357]

Same error, using OleDbType.Date and OleDbType.DBDate with many variations of the datetime input, Including DateTime.Now, DateTime.Now.Date, new DateTime(2012,2,3), all to the same end.

[Frederik Gheysels]

print the sql statement that you've generated to the console before executing, and verify if it is correct

[user1642357]

How do I print the full command from an OleDbCommand object? the command format is correct, it works for string values, and If I switch the field in the database to string, I can place the date into the field, but I want to put the date in as an actual date.

[JNF]

It would take #dd-MM-yy# as well. #dd-MM-yy hh:mm:ss# and #hh:mm:ss dd-MM-yy# too.