Inserting date into access database

Question

I'm having trouble inserting a date into access,in access the data type is Date/Time(general date). i want it to insert today's date so i can call it later and work out how many days have passed (i know how to do that using timespan). So can please tell me the correct way of saving the date to access. Thanx

ps. I dont need the time only the date

        DateTime dateNow = DateTime.Now;
        string connString = (@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|HorseDB.mdb");
        OleDbConnection conn = new OleDbConnection(connString);
        conn.Open();
        OleDbCommand cmd = conn.CreateCommand();
        OleDbCommand cmdSelect = conn.CreateCommand();
        cmd.CommandText = @"INSERT INTO [Users] (PaidDate) VALUES  (@PaidDate) WHERE [UserId] = @OrderId";
        cmd.Parameters.AddWithValue("@PaidDate", dateNow);
        cmd.Parameters.AddWithValue("@OrderId", orderId);
        cmd.ExecuteNonQuery();
        conn.Close();

What is the specific trouble you are having? Are you getting an error message? — Robert Harvey, Jul 09 '13 at 16:32
Im not getting an error, the date isnt there when i check in the database — , Jul 09 '13 at 16:33
Can you run the query manually in Access with that same format and data is stored? — Garrison Neely, Jul 09 '13 at 16:35
Check the following solution: http://stackoverflow.com/questions/9527958/trouble-inserting-datetime-into-access-with-oledb — Jesse Smith, Jul 09 '13 at 16:35
Try DateTime dateNow = DateTime.Now.Date; Alternatively you could set the Default Value of the field in Access itself to =Date() — Shane LeBlanc, Jul 09 '13 at 16:54
What is 'INSERT .. WHERE` supposed to do? An `INSERT` receives a list of columns you want to set values for, adds a new row to the table, and sets the content of the specified columns to the values you provide. It's adding a *new row*, so what is the `WHERE` supposed to do? — Ken White, Jul 09 '13 at 17:00
@KenWhite This is true. Possibly looking for an UPDATE maybe? — Shane LeBlanc, Jul 09 '13 at 17:02
@KenWhite That makes sense. Thanx for the help. Just tested it and it works. — , Jul 09 '13 at 17:04

score 2 · Answer 1 · answered Jul 10 '13 at 08:45

2

Sometimes your device DateTime format is not suitable for the DB , using the below DateTime format is always accepted by the DB.

Replace passing the date parameter as below:

cmd.Parameters.AddWithValue("@PaidDate", dateNow.ToString("yyyyMMdd"));

answered Jul 10 '13 at 08:45

Mohammad abumazen

1,286
1
11
24

score 0 · Answer 2 · answered Jul 09 '13 at 17:12

I suspect you're looking for an UPDATE instead of an INSERT, if what you're wanting to do is update the row for UserID with the current date:

cmd.CommandText = @"UPDATE [Users] SET PaidDate = @PaidDate WHERE [UserId] = @OrderId";
cmd.Parameters.AddWithValue("@PaidDate", dateNow);
cmd.Parameters.AddWithValue("@OrderId", orderId);
cmd.ExecuteNonQuery();

If you're actually looking to INSERT an entire new row, you need to use the proper syntax for INSERT:

cmd.CommandText = @"INSERT INTO [Users] (PaidDate, OrderID) VALUES (@PaidDate, @OrderId)";
cmd.Parameters.AddWithValue("@PaidDate", dateNow);
cmd.Parameters.AddWithValue("@OrderId", orderId);
cmd.ExecuteNonQuery();

score 0 · Answer 3 · answered Jul 09 '13 at 17:43

Try this:

string connString = (@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=|DataDirectory|HorseDB.mdb");
        OleDbConnection conn = new OleDbConnection(connString);
        conn.Open();
        OleDbCommand cmd = conn.CreateCommand();
        OleDbCommand cmdSelect = conn.CreateCommand();
        cmd.CommandText = @"INSERT INTO [Users] (PaidDate) VALUES  (@PaidDate) WHERE [UserId] = @OrderId";
        cmd.Parameters.AddWithValue("@PaidDate", Date());
        cmd.Parameters.AddWithValue("@OrderId", orderId);
        cmd.ExecuteNonQuery();
        conn.Close();

Because you're using the Jet connection string, it can read the MSAccess value of Date(), which will be the current system date.

Inserting date into access database

3 Answers3