4

I want to insert a date record to an Access database. Here is my code:

cmd.CommandText = "INSERT INTO AlarmHistory(Date) VALUES ('6/8/2012')"; 
cmd.ExecuteNonQuery();

It gives Syntax error in INSERT INTO statement. error at second row.

The screenshot that show my cell data type on db is below.

Cell data type at db

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Ned
  • 1,055
  • 9
  • 34
  • 58
  • I have searched a lot and tried many things: http://stackoverflow.com/questions/10353541/syntax-error-in-insert-into-statement-while-using-datetime http://stackoverflow.com/questions/7522924/insert-datetime-parameter-in-access-with-oledb http://stackoverflow.com/questions/6449284/problem-with-inserting-date-to-ms-access-database http://stackoverflow.com/questions/9527958/trouble-inserting-datetime-into-access-with-oledb I don't know why this process is so hard! – Ned Jun 14 '12 at 22:47
  • 1
    Try wrapping square brackets around Date - it's a keyword! So your statement would be INSERT INTO AlarmHistory ([Date]) VALUES (#6/8/2012#) – dash Jun 14 '12 at 22:57
  • Finally it worked! Thanks @dash. So the problem was at column name (Date), not value – Ned Jun 14 '12 at 23:03
  • 1
    Yes! The database engine could be a lot better at telling people this. It leads to all sorts of confusion. I would also take @Steve advice as well though. Parameterising your queries is good practice, and boosts performance in many database systems. – dash Jun 14 '12 at 23:04
  • @dash, good catch, You got it a few minutes before – Steve Jun 14 '12 at 23:10
  • Guys, how about the time? It doesn't work: "INSERT INTO AlarmHistory([Time]) VALUES ('17:55')". I am using ShortTime type in Access – Ned Jun 14 '12 at 23:57

5 Answers5

6

Use parameters

cmd.CommandText = "INSERT INTO AlarmHistory([Date]) VALUES (?)";  
cmd.Parameters.AddWithValue("@date", new DateTime(2012,06,8));
cmd.ExecuteNonQuery();

This will preserve your code from SqlInjection and you could stop to worry about quoting your values-

Just tried creating a dummy database. It's the Date Field. You should enclose in square brackets because Date is a reserved keyword

Here the list of the reserved keywords for Jet 4.0

Steve
  • 213,761
  • 22
  • 232
  • 286
2

Perhaps date is a reserved word. If so, it should be delimited (maybe with brackets):

INSERT INTO AlarmHistory([Date]) VALUES ('6/8/2012')
Mark Wilkins
  • 40,729
  • 5
  • 57
  • 110
1

Try use

"INSERT INTO AlarmHistory(Date) VALUES ('#6/8/2012#')"

davit_gri
  • 130
  • 1
  • 8
1

You should add a # sign around the date that should solve your problem.

Jason De Oliveira
  • 1,732
  • 9
  • 16
0

please remember before creating database table avoid to give fields names DATE and NAME

it will throws error while inserting. eg..give field password ,date instead of give like that ,password_user ,bill_date

Note:date and password not acceptable field name in MS Access table .bcoz it is a keywords.

IT SHOULD SOLVE YOUR PROBLEM

Thanks. Rathina.

user2918395
  • 1
  • 1