Problem with inserting date to Ms-Access database

Question

I have in my Ms-Access database table, a date field in Short Date format.

I have in my C# program DateTimePicker control in dd/MM/yyyy short date format

I try to insert data using C# code like this:

SQL = "insert into MyTbl(D_from,D_to) values (@MyFrom,@MyTo)";
OleDbCommand Cmd = Conn.CreateCommand();
OleDbParameter dateparam1 = Cmd.Parameters.AddWithValue("@MyFrom", DbType.DateTime);
dateparam1.Value = dt_From.Value; 

OleDbParameter dateparam2 = Cmd.Parameters.AddWithValue("@MyTo", DbType.DateTime);
dateparam2.Value = dt_To.Value;

Cmd.CommandText = SQL;
Cmd.ExecuteNonQuery();

and I got the error: Data type mismatch in criteria expression.

Binil · Accepted Answer · 2011-06-23 04:40:05Z

2

change Parameters.AddWithValue to Parameters.Add

Cmd.Parameters.Add("@MyFrom", DbType.DateTime);

if you use Parameters.AddWithValue then you need to pass the Value as Second Parameter, not the DataType

Cmd.Parameters.AddWithValue("@MyFrom", dt_From.Value);

and also you need to set the CommandType as Text

edited Jun 23, 2011 at 4:40

answered Jun 23, 2011 at 4:24

Binil

6,6033 gold badges34 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Binil Over a year ago

OleDbCommand Cmd = Conn.CreateCommand(); Cmd.Parameters.AddWithValue("@MyFrom", dt_From.Value); Cmd.Parameters.AddWithValue("@MyTo", dt_To.Value); Cmd.CommandText = SQL; Cmd.ExecuteNonQuery();

Community · Accepted Answer · 2017-05-23 10:27:56Z

0

Take a look at this question

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

edited May 23, 2017 at 10:27

CommunityBot

11 silver badge

answered Jun 23, 2011 at 4:22

Jude Cooray

19.9k21 gold badges85 silver badges128 bronze badges

Comments

Bibhu · Accepted Answer · 2011-06-23 06:04:29Z

Try this

dateparam1.Value = dt_From.Value.ToShortDateString();
dateparam2.Value = dt_To.Value.ToShortDateString();

Otherwise have look at this :

Date values must be either delimited according to the ODBC canonical date format or delimited by the datetime delimiter ("#"). Otherwise, Microsoft Access will treat the value as an arithmetic expression and will not raise a warning or error.

For example, the date "March 5, 1996" must be represented as {d '1996-03-05'} or #03/05/1996#; otherwise, if only 03/05/1993 is submitted, Microsoft Access will evaluate this as 3 divided by 5 divided by 1996. This value rounds up to the integer 0, and since the zero day maps to 1899-12-31, this is the date used.

A pipe character (|) cannot be used in a date value, even if enclosed in back quotes.

Collectives™ on Stack Overflow

Problem with inserting date to Ms-Access database

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related