Insert date into Access database gives syntax error

Question

Trying to insert the current date into my Access database throws a syntax error for the INSERT INTO statement.

My database contains a field named Day which has the data type Date/Time.

My insert statement is:

Try
    dbConnection.Open()
    sqlString = "INSERT INTO table1 (Day) VALUES (@Day)"
    dbCommand.CommandText = sqlString
    dbCommand.Parameters.AddWithValue("@Day", FormatDateTime(Now, 2))
    dbCommand.ExecuteNonQuery()
Finally
    dbConnection.Close()
End Try

When I do Response.Write(FormatDateTime(Now, 2)) it displays 10/29/2013, which should be an acceptable record.

Doing these also throw a syntax error in the INSERT INTO statement:

Try
    dbConnection.Open()
    sqlString = "INSERT INTO table1 (Day) VALUES (Now())"
    dbCommand.CommandText = sqlString
    dbCommand.ExecuteNonQuery()
Finally
    dbConnection.Close()
End Try

Try
    dbConnection.Open()
    sqlString = "INSERT INTO table1 (Day) VALUES (#" & FormatDateTime(Now, 2) & "#)"
    dbCommand.CommandText = sqlString
    dbCommand.ExecuteNonQuery()
Finally
    dbConnection.Close()
End Try

Are the dates not in an acceptable format for the field?

HansUp · Accepted Answer · 2013-10-29 19:01:29Z

1

Day is a reserved word. Enclose that name in square brackets to avoid confusing the db engine.

sqlString = "INSERT INTO table1 ([Day]) VALUES (Now())"

answered Oct 29, 2013 at 19:01

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

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

3 Comments

Joseph Webber Over a year ago

I wasn't aware Day was a reserved keyword as well. Thank you Hans for providing the link and answering my question!

HansUp Over a year ago

Both Date and Day are functions. I included a link to Allen Browne's page about reserved and other problem names. That page also include a link to a free utility you can download and use to examine your Access databases for problem names.

HansUp Over a year ago

Hmmm. I was certain I was replying to a different comment. :-) Cheers.

Collectives™ on Stack Overflow

Insert date into Access database gives syntax error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related