CODEWITHSUNDEEP
c#ms-access
user6648485
user6648485

Reputation: 77

Passing DateTime.Now into Access database

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 + "')";

Upvotes: 1

Views: 2146

Answers (4)

Joel Coehoorn
Joel Coehoorn

Reputation: 415620

Three issues:

  1. Access OleDb uses positional parameters rather than named parameters.
  2. Don't let ADO.NET infer the parameter type. Set the type (and size, if needed) manually. This alone would probably fix the original issue.
  3. You don't need to send a parameter for the current timestamp. Let access figure that out for you in the SQL.

Put it all together to get this:

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

// guessing a column type and length here. Use the real type from your database
oleDBCommand.Parameters.Add("?", OleDbType.VarWChar, 20).Value = combobox1.Text;

But so you can see how send an arbitrary DateTime value, I will also show both values as a parameter:

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

oleDBCommand.Parameters.Add("?", OleDbType.VarWChar, 20).Value = combobox1.Text;
oleDBCommand.Parameters.Add("?", OleDbType.Date).Value = DateTime.Now;

Upvotes: 0

John
John

Reputation: 333

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.

Upvotes: 1

Shift 'n Tab
Shift 'n Tab

Reputation: 9443

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

Upvotes: 2

Prasad Dhananjaya
Prasad Dhananjaya

Reputation: 141

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;

Upvotes: 0

Related Questions