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.
在代码中,strSql是一个String对象,transactiondate是一个Date对象。但是,在SQL数据库中,transactiondate是一个smalldatetime对象。
I've tried changing the smalldatetime to a datetime (in the database) and I've tried transactiondate.toString() but with no success.
我已经尝试将smalldatetime更改为datetime(在数据库中),我尝试了transactiondate.toString()但没有成功。
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.
注意:我知道内联SQL的危险。我在这里寻找快速修复解决方案,而不是关于SQL注入的讨论。
4 个解决方案
#1
Try adding single quotes to your insert statement.
尝试在insert语句中添加单引号。
strSql = "insert into table2 (transactiondate) values ('" & transactiondate & "')"
#2
Two possible answers:
两个可能的答案:
- You need to encapsulate your string in an apostrophe:
您需要将字符串封装在撇号中:
strSql = "insert into table2 (transactiondate) values ('" & transactiondate & "')"
strSql =“insert into table2(transactiondate)values('”&transactiondate&“')”
- You're using a date before 1/1/1900 or after 6/6/2079 (the limits of
smalldatetime)
您使用的是1900年1月1日之前或6/6/2079之后的日期(smalldatetime的限制)
#3
Use transactiondate.ToString("yyyy/MM/dd HH:mm:ss") or your prefered date format. If it remains, change 'sa' user default language, or whatever user you use, to your prefered language.
使用transactiondate.ToString(“yyyy / MM / dd HH:mm:ss”)或您的首选日期格式。如果仍然存在,请将“sa”用户默认语言或您使用的任何用户更改为您喜欢的语言。
#4
Smalldatetime can only accept values for years > 1900 and < 2079 as well. I had a similar error to the one you posted where the solution was to filter out invalid years like such
Smalldatetime只能接受年份> 1900和<2079的值。我有一个类似的错误,你发布的解决方案是过滤掉这样的无效年份
where year(datecolumn) < 2079
#1
Try adding single quotes to your insert statement.
尝试在insert语句中添加单引号。
strSql = "insert into table2 (transactiondate) values ('" & transactiondate & "')"
#2
Two possible answers:
两个可能的答案:
- You need to encapsulate your string in an apostrophe:
您需要将字符串封装在撇号中:
strSql = "insert into table2 (transactiondate) values ('" & transactiondate & "')"
strSql =“insert into table2(transactiondate)values('”&transactiondate&“')”
- You're using a date before 1/1/1900 or after 6/6/2079 (the limits of
smalldatetime)
您使用的是1900年1月1日之前或6/6/2079之后的日期(smalldatetime的限制)
#3
Use transactiondate.ToString("yyyy/MM/dd HH:mm:ss") or your prefered date format. If it remains, change 'sa' user default language, or whatever user you use, to your prefered language.
使用transactiondate.ToString(“yyyy / MM / dd HH:mm:ss”)或您的首选日期格式。如果仍然存在,请将“sa”用户默认语言或您使用的任何用户更改为您喜欢的语言。
#4
Smalldatetime can only accept values for years > 1900 and < 2079 as well. I had a similar error to the one you posted where the solution was to filter out invalid years like such
Smalldatetime只能接受年份> 1900和<2079的值。我有一个类似的错误,你发布的解决方案是过滤掉这样的无效年份
where year(datecolumn) < 2079