CODEWITHSUNDEEP
c#asp.netms-accessdatetimeoledb
emre
emre

Reputation: 61

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

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.

Upvotes: 6

Views: 19832

Answers (6)

Cyril Gupta
Cyril Gupta

Reputation: 13723

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;

Upvotes: 1

Rinat Abdullin
Rinat Abdullin

Reputation: 23572

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

Upvotes: 5

DRapp
DRapp

Reputation: 48179

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.

Upvotes: 0

Antony Koch
Antony Koch

Reputation: 2063

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") + "'"

Upvotes: 0

Michael Niemand
Michael Niemand

Reputation: 1754

maybe try 

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

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

Upvotes: 0

James
James

Reputation: 82136

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.

Upvotes: 0

Related Questions