我正试图使用参数化的insert语句将数据插入到SQL Server表中。
但是,我在尝试执行命令时收到错误消息,
-2147217913从字符串转换日期和/或时间时转换失败
我不明白,因为我已经指定了dtdate是addbdate。传递给它的值是一个日期,即2018-09-24。我错过了什么?
Dim sSQL As String
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(shsName)
OpenDbConnection
Dim sInd As String
Dim dtDatePrice As Date
Dim dScoreWgt As Double
Dim sScoreRat As String
Dim dScoreHlt As Double
Dim dScoreSup As Double
sSQL = "insert into " & sDB & ".dbo.MYTABLE(Ndate, Industry, ScoreOvrWgt, ScoreOvrRat, ScoreHealth, ScoreSupply) " & _
"values('dtDate', 'sInd', 'dScoreWgt', 'sScoreRat', 'dScoreHlt', 'dScoreSup')"
Dim cmd As New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandType = adCmdText
cmd.CommandText = sSQL
cmd.Parameters.Append cmd.CreateParameter("dtDate", adDBDate, adParamInput)
cmd.Parameters.Append cmd.CreateParameter("sInd", adVarChar, adParamInput, 100)
cmd.Parameters.Append cmd.CreateParameter("dScoreWgt", adDouble, adParamInput)
cmd.Parameters.Append cmd.CreateParameter("sScoreRat", adVarChar, adParamInput, 10)
cmd.Parameters.Append cmd.CreateParameter("dScoreHlt", adDouble, adParamInput)
cmd.Parameters.Append cmd.CreateParameter("dScoreSup", adDouble, adParamInput)
Dim iRow As Integer
iRow = 1
Do Until IsEmpty(ws.Cells(iRow, 3))
dtDatePrice = ws.Cells(iRow, 4)
cmd.Parameters("dtDate").Value = dtDatePrice
cmd.Parameters("sInd").Value = Strings.Trim(ws.Cells(iRow, 6))
If Strings.Trim(ws.Cells(iRow, 8)) = "NA" Then
cmd.Parameters("dScoreWgt").Value = Null
Else
cmd.Parameters("dScoreWgt").Value = ws.Cells(iRow, 8)
End If
cmd.Parameters("sScoreRat").Value = Strings.Trim(ws.Cells(iRow, 9))
If Strings.Trim(ws.Cells(iRow, 14)) = "NA" Then
cmd.Parameters("dScoreHlt").Value = Null
Else
cmd.Parameters("dScoreHlt").Value = ws.Cells(iRow, 14)
End If
If Strings.Trim(ws.Cells(iRow, 15)) = "NA" Then
cmd.Parameters("dScoreSup").Value = Null
Else
cmd.Parameters("dScoreSup").Value = ws.Cells(iRow, 15)
End If
cmd.Execute
irow = irow + 1
loop
