conversion of a varchar to a smalldatetime results in an out-of-range value

Question

The code:

strSql = "insert into table2 (transactiondate) values ('" & transactiondate & "')"

seems to be giving me the runtime error:

The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value

In the code, strSql is a String object and transactiondate is a Date object. In the SQL database however, transactiondate is a smalldatetime object.

I've tried changing the smalldatetime to a datetime (in the database) and I've tried transactiondate.toString() but with no success.

How can this be fixed?

Note: I know about the dangers of inline SQL. I am looking for a quick fix solution here and not a discussion about SQL injection.

Answer 1

Try adding single quotes to your insert statement.

strSql = "insert into table2 (transactiondate) values ('" & transactiondate & "')"