CODEWITHSUNDEEP
c#ms-access
user1955810
user1955810

Reputation:

Inserting date into access database

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

Upvotes: 0

Views: 9622

Answers (3)

Mohammad abumazen
Mohammad abumazen

Reputation: 1286

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

Upvotes: 2

Johnny Bones
Johnny Bones

Reputation: 8404

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.

Upvotes: 0

Ken White
Ken White

Reputation: 125748

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

Upvotes: 0

Related Questions