How to insert current date/time in MS Access using C#

Question

I have tried almost everything to insert the current date and time in to a MS Access database using C# and nothing seems to work.

The table structure looks like

ID (primary key) 
Est_ID (number)  
saveName (text) 
userName (text) 
timestamp (date/time)

The code looks like this:

OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();

DateTime today = DateTime.Today;
DateTime time = DateTime.Now;
string name = Request.Form["saveName"];

string my_query = "INSERT INTO TABLENAME (userName, Est_id, saveName, timestamp) VALUES (@userName, @Est_id, @saveName, @timestamp)";

OleDbCommand cmd = new OleDbCommand(my_query, conn);

cmd.Parameters.AddWithValue("@userName", userName);
cmd.Parameters.AddWithValue("@Est_id", est_index);
cmd.Parameters.AddWithValue("@saveName", name);
cmd.Parameters.AddWithValue("@timestamp", time);

cmd.ExecuteNonQuery();

I have tried formatting. I added # but nothing seems to work. This is really frustrating since I have spent more than a day now getting this to work. What am I doing wrong?

Does it not give you any errors? Normally failed inserts/updates result in errors explaining whats wrong. At the moment the code you have would complain that timestamp has not been supplied. — BugFinder
– BugFinder, Commented May 2, 2016 at 23:12
did not work. It gives an error saying select into statement invalid — user3342812
– user3342812, Commented May 2, 2016 at 23:16
@user3342812 You might want to rethink the use of "AddWithValue". See this: blogs.msmvps.com/jcoehoorn/blog/2014/05/12/… — Tibrogargan
– Tibrogargan, Commented May 2, 2016 at 23:19

Jonathan Allen · Accepted Answer · 2016-05-03 04:57:51Z

1

OleDB/Access requires you to explicitly set the data type for date/time values.

var param = cmd.Parameters.AddWithValue("@timestamp",time);
param.OleDbType = OleDbType.Date;

answered May 3, 2016 at 4:57

Jonathan Allen

70.7k77 gold badges276 silver badges461 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2024-08-08 12:53:59Z

1

OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();

string name = Request.Form["saveName"];

string my_query = "INSERT INTO TABLENAME (userName, Est_id,saveName,timestamp) VALUES(@userName,@Est_id,@saveName,Now())";

OleDbCommand cmd = new OleDbCommand(my_query, conn);

cmd.Parameters.AddWithValue("@userName", userName);
cmd.Parameters.AddWithValue("@Est_id", est_index);
cmd.Parameters.AddWithValue("@saveName", name);

cmd.ExecuteNonQuery();

edited Aug 8, 2024 at 12:53

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered May 3, 2016 at 5:20

Sushil Mate

5836 silver badges15 bronze badges

Comments

Nagato · Accepted Answer · 2016-05-03 02:58:11Z

0

According from this link https://support.office.com/en-us/article/Format-the-date-and-time-field-in-Access-47fbbdc1-52fa-416a-b8d5-ba24d881b698

You may need to check on your column setting in database that It's already set Format of timestamp column to Genaral Date to match with DateTime.Now in C#.

Also, You need to check that output of System.DateTime and your database time format is 12Hours system or 24Hours system.

Hope this information can help you.

answered May 3, 2016 at 2:58

Nagato

92 bronze badges

Comments

Jonathan Allen · Accepted Answer · 2016-05-03 05:03:53Z

0

If you are using Tortuga Chain:

static AccessDataSource s_DataSource = new AccessDataSource(connectionString); //one per application

DateTime today = DateTime.Today;
DateTime time= DateTime.Now;
string name = Request.Form["saveName"];

s_DataSource.Insert("TABLENAME", new {@username = userName, @Est_id = est_index,@saveName = name,@timestamp = time}).Execute();

answered May 3, 2016 at 5:03

Jonathan Allen

70.7k77 gold badges276 silver badges461 bronze badges

Comments

Cosmic_Cowboy · Accepted Answer · 2024-08-08 14:41:41Z

This doesn't have a selected answer yet. I ran into a similar issue and have solved it so I will post this here in hopes of helping others. First let's fix @user3342812 code a little

OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();

DateTime time = DateTime.Today;
string name = Request.Form["saveName"];

string editTableName = "name of table to edit"

string my_query = $"INSERT INTO [{editTableName}] ([userName], [Est_id], [saveName],[timestamp]) VALUES(?,?,?,?)";

OleDbCommand cmd = new OleDbCommand(my_query, conn);

cmd.Parameters.AddWithValue("@userName", userName);
cmd.Parameters.AddWithValue("@Est_id", est_index);
cmd.Parameters.AddWithValue("@saveName", name);
cmd.Parameters.AddWithValue("@timestamp",time);

cmd.ExecuteNonQuery();

Note: you don't always have to use "[]" around your variables but in the event your MS Access (MSA) field is a reserved word you will get an error, so I have been using them.

While OP is able to use

VALUES (@userName, @Est_id, @saveName, @timestamp)

I find it simpler and more secure to use VALUES(?,?,?,?)

Final Note: the final issue I see here, and what I struggled to figure out for an entire day is entering a DateTime into MSA. MSA has a DateTime format like so 8/8/2024 but C# DateTime can have multiple formats one of which is DateOnly - this does NOT work as MSA does not have a data type DateOnly. The way I was able to structure it, so MSA accepted it was:

public class TestModel
{
   public TestModel() 
   {
       _opened = DateTime.Today;
   }

   private readonly DateTime _opened;

   public DateTime Opened
   {
       get => _opened;
   }
}

Icepickle · Accepted Answer · 2017-12-11 16:22:35Z

-1

I know this post is a little old but it could help someone. Since this morning I am confronted with this problem. And I finally found the solution.

The value that DateTime.Now sends to the database is in the form {dd / mm / yyyy hh: mm: ss} and what happens, I think, is that access does not recognize { }.

So to solve the problem just convert the date to string before assigning it to the parameters:

cmd.Parameters.AddWithvalue("@timestamp", time.ToString());

Or

cmd.Parameters.AddWithvalue("@timestamp", oleDbType.Date).Value=time.ToString());

That work very well for me

edited Dec 11, 2017 at 16:22

Icepickle

12.8k3 gold badges38 silver badges51 bronze badges

answered Dec 11, 2017 at 15:39

Gatoussan Olivier AKAKPO

12 bronze badges

Collectives™ on Stack Overflow

How to insert current date/time in MS Access using C#

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related