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

Question

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

Your insert statement specifies character literals with the variable name instead of parameter markers. Should be: "values(?, ?, ?, ?, ?, ?)" — Dan Guzman
– Dan Guzman, Commented Sep 25, 2018 at 11:11
@DanGuzman thanks very much. If you put your comment as an answer happy to mark it as correct — mHelpMe
– mHelpMe, Commented Sep 25, 2018 at 12:06

Dan Guzman · Accepted Answer · 2018-09-26 01:27:51Z

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

edited Sep 26, 2018 at 1:27

answered Sep 25, 2018 at 12:11

Dan Guzman

46.9k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related