1

I am facing a problem on passing the DateTime.Now into Access database:

oleDBCommand.CommandText =
  "INSERT INTO tblData "([PIC], [Sampling Date]) "VALUES (@PIC, @SamplingDate)";

oleDBCommand.Parameters.Add(new OleDbParameter("@PIC", combobox1.Text));
oleDBCommand.Parameters.Add(new OleDbParameter("@SamplingDate", DateTime.Now));

I tried a lot of methods from the internet like using oleDBType.Date, DateTime.Now.ToString(), using AddWithValue..... And none of it is working.

Note 1: Database setting [Sampling Date] = Data Type: Date/Time (Format - Long Time), database was

Note 2: Below code was working but I prefer to using .parameters as it look much more organize and easy to manage.

oleDBCommand.CommandText =
 "INSERT INTO tblData ([PIC], [Sampling Date]) " VALUES ('" + combobox1.Text + "', '" + DateTime.Now + "')";
Shift 'n Tab
  • 8,808
  • 12
  • 73
  • 117
user6648485
  • 77
  • 10
  • 1
    Related : http://www.codeproject.com/Questions/660282/Date-Format-in-Select-Query-With-Csharp-To-MS-Acce –  Aug 09 '16 at 03:11
  • Tested the solution on suggested webpage, it is not working as well – user6648485 Aug 09 '16 at 03:28
  • `DateTime.Now` gets "local" time (problematic for a couple of reasons), are you sure you don't want to explicitly get UTC (`DateTime.UtcNow`)? Also, what do you mean by "not working"? Throws an exception (which one)? Puts in the wrong data (what data)? – Clockwork-Muse Aug 09 '16 at 04:01
  • Try with DateTime.UtcNow and it is not working. Not Work with description (Data type mismatch in criteria expression). In my code, my intention of the [Sampling Date] was whenever person-in-charge collect the sample, they will key in their name and the [Sampling Date] will auto generate base on that day. – user6648485 Aug 09 '16 at 04:07
  • Possible duplicate of [Inserting a date/time value in Access using an OleDbParameter](http://stackoverflow.com/questions/7522924/inserting-a-date-time-value-in-access-using-an-oledbparameter) – Clockwork-Muse Aug 09 '16 at 04:36

3 Answers3

1

You dont need to pass parameter when specifying current date.

Let the ms access sql query handle it, you need to replace @SamplingDate parameter to Date() for example

cmd.CommandText = "INSERT INTO tblData ([PIC], [Sampling Date]) VALUES (@PIC, Date())";

Here is the best explanation Insert today's date

Shift 'n Tab
  • 8,808
  • 12
  • 73
  • 117
  • Maybe. If the database has a different setting than the client, this may produce wrong results. – Clockwork-Muse Aug 09 '16 at 03:59
  • No, I mean if the database is set to a different timezone (perhaps if the database is on another machine, somewhere else) – Clockwork-Muse Aug 09 '16 at 04:30
  • ok that's probably will result a different time input? i will consider that – Shift 'n Tab Aug 09 '16 at 04:43
  • @user6648485 i found another solution, http://stackoverflow.com/questions/16217464/trying-to-insert-datetime-now-into-date-time-field-gives-data-type-mismatch-er this will clearly explain the error. – Shift 'n Tab Aug 09 '16 at 04:51
1

I was struggling with this this week and the accepted answer really did not help me. I found that if I did the assignment of the date+time as an ODBC canonical string (yyyy-mm-dd hh:mi:ss), it worked just fine. So, my C# code looked something like:

InsertCommand.Parameters.Add("@" + column.ColumnName, OleDbType.DBTimeStamp).Value = DateTime.Now.ToString("u");

for the first row and then

InsertCommand.Parameters.Add("@" + column.ColumnName).Value = DateTime.Now.ToString("u")

for the rest.

John
  • 223
  • 3
  • 13
0

Try This,

cmd.CommandText = "INSERT INTO tblData ([PIC], [Sampling Date]) VALUES (@PIC, @SamplingDate)";
cmd.Parameters.Add("@PIC",OleDbType.VarChar).Value = combobox1.Text;
cmd.Parameters.Add("@PIC", OleDbType.Date).Value = DateTime.Now;

Prasad Dhananjaya
  • 141
  • 4