CODEWITHSUNDEEP
sql-serverexcelvbaadodb
mHelpMe
mHelpMe

Reputation: 6668

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

Upvotes: 0

Views: 57

Answers (1)

Dan Guzman
Dan Guzman

Reputation: 46281

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(?, ?, ?, ?, ?, ?)

Upvotes: 1

Related Questions