Clash Royale CLAN TAG#URR8PPP

using a parameterised insert query but getting conversion failed when converting date

I am trying to insert data into a SQL Server table using a parameterised insert statement.

However I get the error message when trying to execute the command,

-2147217913 Conveersion failed when converting date and/or time from character string

Which I don't understand as I have specified that dtDate is adDBDate. The value being passed to it is a date, its 2018-09-24. What am I missing?

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


"values(?, ?, ?, ?, ?, ?)"

1 Answer
1

Your insert statement specifies character literals with the variable name instead of parameter markers. OLE DB and ODBC use ? as parameter markers so your VALUES clause should be:

?

VALUES

values(?, ?, ?, ?, ?, ?)


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.