Inserting current date into access database in C#

Question

I am having a problem inserting the current date in access database. I have tried every method possible.

This is what i have right now

OleDbConnection vcon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=abcd.accdb");

private void Form1_Load(object sender, EventArgs e)
{
    vcon.Open();
    string vsql = string.Format("insert into pending (val1, val2, val3, val4, val5, date) values('{0}', '{1}', '{2}', '{3}', '{4}', @date)", v1.ToString(), v2.ToString(), v3.ToString(), v4.ToString(), v5.ToString());
    OleDbCommand vcom = new OleDbCommand(vsql, vcon);
    vcom.Parameters.AddWithValue("@date", DateTime.Now);
    vcom.ExecuteNonQuery();
    vcom.Dispose();
}

it works if i take out the date part. I tried everything, not using a parameter, using ', # , using different formats. What am i doing wrong?

OleDB does not support named parameters. (msdn.microsoft.com/en-us/library/…) — Preston Guillot

Joshua Honig Joshua Honig · Accepted Answer · 2013-10-23T01:28:17

Please, don't ever construct SQL inserts from string literals. Use real parameters. As Preston Guillot noted, OleDb doesn't support named parameters. The @-prefixed syntax you used is specific to SQL Server, anyways.

Instead you just use question marks, and supply your parameters in order. You can still give a name to your parameter object to help clarify your code, but the OleDb provider will simply ignore the names.

Another problem: You're using "date" as the name of a column, and that's a reserved word in Access SQL (and most other SQL variants, too). To safely name that column, you should enclose it in square brackets: [date].

All together, here's what you should be doing:

private string connectionString = "...";

using(var con = new OleDbConnection(connectionString)) {
    con.Open();
    using(var cmd = con.CreateCommand()) {
        cmd.CommandText = @"
            insert into [pending] ( [val1], [val2], [val3], [val4], [val5], [date] )
            values ( ?, ?, ?, ?, ?, ? )";

        // Parameter names are just to clarify what we're doing
        cmd.Parameters.AddWithValue("val1", v1);
        cmd.Parameters.AddWithValue("val2", v2);
        cmd.Parameters.AddWithValue("val3", v3);
        cmd.Parameters.AddWithValue("val4", v4);
        cmd.Parameters.AddWithValue("val5", v5);
        cmd.Parameters.AddWithValue("date", DateTime.Now);
        cmd.ExecuteNonQuery();
    }
    con.Close();
}

Inserting current date into access database in C#

2 Answers