Data type mismatch in criteria expression | Access, OleDb, C#

Question

I read/update data from MS Access using C#. My code is:

public static void UpdateLastLogin(int userid, DateTime logintime) ///logintime = DateTime.Now
{
    string sql = @"UPDATE [Customers] SET [LastLogin]=?";
    OleDbParameter[] prms = new OleDbParameter[] { 
     new OleDbParameter("@LastLogin",logintime)
    };
    using (DAL dal = new DAL())
    {
        dal.UpdateRow(sql, false, prms);
    }
}

When it comes to Dates, I having trouble. This throws a "Data type mismatch in criteria expression." error. (I removed WHERE clause for keeping it simpler) Am I suuposed to enclose [LastLogin]=? question mark with single quotes, # signs .. does not help. Any leads on how to handle DateTime objects with Access and OleDb provider will be greatly appreciated.

Thanks in advance.

Eckmann

The code bit would be easier to read. if you format it as code by indenting it 4 spaces – Tonjatonjes

Answer 1

There is a known issue with OleDb and dates. Try doing something like:

OleDbParameter p = parameter as OleDbParameter;
if (null == p)
  parameter.DbType = DbType.DateTime;
else
  p.OleDbType = OleDbType.Date;

Or use explicit format string:

value.ToString("yyyy-MM-dd hh:mm:ss")

Hallucinosis

Answer 2

I solved this using the following code

OleDbCommand cmd = new OleDbCommand(qry, cnn);
cmd.Parameters.Add("datenow", OleDbType.Date);
cmd.Parameters["datenow"].Value = DateTime.Now;

Hefter

Answer 3

Firstly, no your SQL statement should be:

"UPDATE Customers SET LastLogin=@LastLogin"

Secondly, the reason you are receiving the date mismatch error will probably be your passing '?' as your date time into the LastLogin field instead of the actual logintime parameter.

Bilge

But this is access .. I tought oleDb does not support named parameter :( – Eckmann

The above statement should be ok assuming you are using an OleDbCommand – Bilge

Answer 4

maybe try

DateTime.Now.ToShortDateString() + ' ' + DateTime.Now.ToShortTimeString()

instead, pass it as String (and maybe enclose with # then)

Tonjatonjes

Maybe a format string passed to ToString() of DateTime will help – Eckmann

edited my post ... should work. There maybe more elegant ways to do it, since this could possibly depend on your localization options though ... – Tonjatonjes

Answer 5

Should it not be

"UPDATE Customers SET LastLogin='@LastLogin'"

And @LastLogin should be

logintime.ToString("yyyy-MM-dd hh:mm:ss")

edit Could you not just inline the whole thing?

"UPDATE Customers SET LastLogin='" + logintime.ToString("yyyy-MM-dd hh:mm:ss") + "'"

Maleate

You could inline the whole thing, yes, but creating dynamic sql is a bad habit to get into. Using a parameter is better design and just as easy to read. – Beta

Answer 6

Try setting the "DBTYPE" property of the parameter to identify it as a date, datetime or datetime2 as appropriate...

prms[0].DbType = DbType.DateTime;

There are 7 signatures to the new OleDbParameter() call, so you may change the signature instance, or just do explicitly as I sampled above since you only had 1 parameter in this case.

Nepheline