MS Access has a problem with date/time fields when the Milliseconds aren’t 0.

I was trying to add a record with a date/time field in an MS Access database like this:

const string UpdateContactPersonSQL =
    "UPDATE ContactPerson set Firstname = ?" +

   […]

        ", ModificationDate = ? " +
    "WHERE ContactpersonID = ?;";

int affected = 0;
Database db = DatabaseFactory.CreateDatabase();

using (DbCommand cmd = db.GetSqlStringCommand(UpdateContactPersonSQL))
{
    Contact.ModifiedBy = UserID;
    db.AddInParameter(cmd, "Firstname", DbType.String, nz(Contact.Firstname));

[…]

     db.AddInParameter(cmd, "ModificationDate", DbType.DateTime, DateTime.Now);
    db.AddInParameter(cmd, "ContactpersonID", DbType.Int32, Contact.ContactpersonID);

    affected = db.ExecuteNonQuery(cmd);

}

Access doesn’t like the use of DateTime.Now, because it can’t handle the Milliseconds field when it’s not 0. So I wrote (copied) a little helper function to return Now with the Milliseconds set to 0:

protected DateTime Now()
{
    DateTime dtmDate = DateTime.Now;
    return new DateTime(dtmDate.Year,
                        dtmDate.Month,
                        dtmDate.Day,
                        dtmDate.Hour,
                        dtmDate.Minute,
                        dtmDate.Second);
}

and of course I pass Now( ) instead of DateTime.Now. It’s working 🙂

Ref: http://forums.asp.net/t/1079456.aspx

Tags van Technorati: ,,,

About Gaston

MCT, MCSD, MCDBA, MCSE, MS Specialist
This entry was posted in Development. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s