我到处找了,找不到答案。
这是我使用ColdFusion和MsSQL时遇到的错误
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Conversion failed when converting date and/or time from character string.
The error occurred in SQL
INSERT INTO Requests (
RequestName,
RequestEmail,
RequestPhone,
RequestType,
RequestSiteSection,
RequestDue,
RequestUrgent,
RequestTitle,
RequestURL,
RequestMessage,
Attachment1,
Attachment2,
Attachment3,
Attachment4,
Attachment5,
RequestCreated,
RequestModified,
RequestPriority,
RequestAssignedTo,
RequestStatus
)
VALUES (
'',
'Joshua >Davis',
'onebigcelt@gmail.com',
'8046789',
'Correction',
'Natural Heritage',
'09/11/2013',
'This is the Title of the REQUEST!',
'http://www.dcr.virginia.com',
'rewas',
'/opt/coldfusion8/runtime/servers/coldfusion/SERVER-INF/temp/wwwroot-tmp/neotmp47128.tmp',
'',
'',
'',
'',
(param 1),
(param 2),
'0',
'Webmaster',
'Pending'
)
每个参数都是cfqueryparam cfsqltype='CF_SQL_timestamp' value='#CreateODBCDateTime(now())#'
当我尝试使用以下任何一种插入时,会出现此错误
- cfqueryparam cfsqltype='CF_SQL_timestamp' value='#CreateODBCDateTime(now())#'
- 现在()
- CreateODBCDateTime(now())
- SQL getDate()
- 其他变体
尝试将时间和日期连接在一起,投射并转换为日期时间,格式化日期和时间,但没有运气我卡住了。
我感谢您的帮助。
这是查询
INSERT INTO Requests (
RequestName,
RequestEmail,
RequestPhone,
RequestType,
RequestSiteSection,
RequestDue,
RequestUrgent,
RequestTitle,
RequestURL,
RequestMessage,
Attachment1,
Attachment2,
Attachment3,
Attachment4,
Attachment5,
RequestCreated,
RequestModified,
RequestPriority,
RequestAssignedTo,
RequestStatus
)
VALUES(
'<cfif IsDefined('form.requestUrgent')>#form.requestUrgent#</cfif>',
'#form.requestName#',
'#form.requestEmail#',
'#form.requestPhone#',
'#form.requestType#',
'#form.requestSection#',
'#form.requestDueDate#',
'#form.requestTitle#',
'#form.requestURL#',
'#form.requestMessage#',
'#form.attachment1#',
'#form.attachment2#',
'#form.attachment3#',
'#form.attachment4#',
'#form.attachment5#',
getdate(),
getdate(),
'0',
'Webmaster',
'Pending'
)
您可以使用上述任何选项替换getDate()
#var#
转换投射、参数等
来自评论
插入查询中的列和值在示例代码中似乎处于关闭状态。插入的内容以列RequestName
开头,但 VALUES 子句中的第一个变量是 form.requestUrgent
,然后是 form.requestName
等。因此,您的查询尝试将form.requestSection
(示例中为"自然遗产")插入到日期列RequestDue
中。
示例摘录:
INSERT INTO Requests (
RequestName, /* column 1 */
RequestEmail, /* column 2 */
RequestPhone, /* column 3 */
RequestType, /* column 4 */
RequestSiteSection, /* column 5 */
RequestDue, /* column 6 */
RequestUrgent, /* column 7 */
RequestTitle, /* column 8 */
....
VALUES (
'<cfif IsDefined('form.requestUrgent')>#form.requestUrgent#</cfif>', /* value 1 */
'#form.requestName#', /* value 2 */
'#form.requestEmail#', /* value 3 */
'#form.requestPhone#', /* value 4 */
'#form.requestType#', /* value 5 */
'#form.requestSection#', /* value 6 */
'#form.requestDueDate#', /* value 7 */
'#form.requestTitle#', /* value 8 */
....
而且我怎么强调都不为过,您应该为所有变量值使用<cfqueryparam>
标签。它有助于防止 SQL 注入,还可以提高查询性能。