I need to insert form data from my VB.NET application to a Microsoft Access database.
I am getting the error "Syntax error in INSERT INTO statement"
when using the following syntax:
INSERT INTO bs1 (teacher, subject, date, period)
VALUES ('test', 'test', 'test', 'test')
I'll admit I'm used to the MySQL type syntax, any help on this matter would be greatly appreciated, thanks.
You can use the INSERT INTO statement to add a single record to a table using the single-record append query syntax as shown above. In this case, your code specifies the name and value for each field of the record.
You can also use INSERT INTO to append a set of records from another table or query by using the SELECT … FROM clause as shown above in the multiple-record append query syntax. In this case, the SELECT clause specifies the fields to append to the specified target table.
INSERT INTO SELECT Statement Syntax It first Selects records from a table ( Select statement) Next, it inserts into a table specified with INSERT INTO.
I believe date
is a reserved word. You need to encapsulate the reserved field names in square brackets:
INSERT INTO bs1 (teacher, subject, [date], period) VALUES ('test', 'test', 'test', 'test')
EDIT: See the following article for a complete list of reserved words in Access 2002 and greater: http://support.microsoft.com/kb/286335
~md5sum~
In Access the delimiter for literal values inserted into date fields is #
, for text fields is '
or "
and numeric field values do not have a delimiter, which suggests:
INSERT INTO bs1 (teacher, subject, [date], period)
VALUES ('test', 'test', #2009-12-31#, 0)
In Access Database Engine SQL code, when you need to specify that a literal value is of type DATETIME
, you can either explicitly cast the value to DATETIME
or use #
characters to delimit the value.
Using an explicit cast using the CDATE()
function:
INSERT INTO bs1 (teacher, subject, [date], period)
VALUES ('test', 'test', CDATE('2009-12-31 00:00:00'), 0);
Using a DATETIME
literal value:
INSERT INTO bs1 (teacher, subject, [date], period)
VALUES ('test', 'test', #2009-12-31 00:00:00#), 0);
When INSERT
ing a value into a column of type DATETIME
, if you do not specify an explicit DATETIME
value, the engine will implicitly attempt to coerce a value to DATETIME
. The literal value 'test' cannot be coerced to type DATETIME
and this would appear to be the source of your syntax error.
Note: none of the above applies to the NULL
value. In Access Database Engine SQL there is no way to cast the NULL
value to an explicit type e.g.
SELECT CDATE(NULL)
generates an error, "Invalid use of NULL". Therefore, to specify a NULL
DATETIME
literal, simply use the NULL
keyword.
It pays to remember that the Access Database Engine has but one temporal data type, being DATETIME
(its synonyms are DATE
, TIME
, DATETIME
, and TIMESTAMP
). Even if you don't explicitly specify a time element, the resulting value will still have a time element, albeit an implicit one. Therefore, it is best to always be explicit and always include the time element when using DATETIME
literal values.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With