Inserting date into access database

0
votes

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();
3
c#ms-access
What is the specific trouble you are having? Are you getting an error message? – Robert Harvey
What error are you receiving? – Garrison Neely
Im not getting an error, the date isnt there when i check in the database – user1955810
Check the following solution: stackoverflow.com/questions/9527958/… – Jesse Smith
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

3 Answers

2
votes

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"));
0
votes

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();
0
votes

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.