i got error in insert to database - access

Question

hi

i have val MyDate in my C# program that contain today-date or null.

i have date field in my access 2007 - TdateOpen

i try to insert to database like this:

SQL = "insert into MyCount(TdateOpen) values ('" + MyDate +"')";

and i got this error:

Data type mismatch in criteria expression

what can be the problem ?

thank's in advance

Answer 1

Coz in your SQL statement you are entering date as String . Instead of String it should be a date/date format. Try to surround by # .

Answer 2

You will need to ensure that the date is in US order (mm/dd/yyyy) or ANSI/ISO order, whether you use dash or slash is not important, ANSI/ISO is to be preferred.

Then as, Madhu CM said, the delimiter for dates in Access is hash (#), however, your date can be null and null cannot be delimited, so you will either have to add the delimiter to a date string, if a date is returned, or use two sql statements, one for null and one for date.

Answer 3

You could SQL parameters instead of dynamically embedding the date value into the statement.

SQL = "insert into MyCount(TdateOpen) values (?)";
var parameter = yourCommand.CreateParameter();
parameter.Value = yourDateTime;
yourCommand.Parameters.Add(parameter);

(DISCLAIMER: The code was not compiled nor tested, but it should give you a hint)