Parameters not adding to ADO string for INSERT statement VBA

Question

I am trying to learn how to avoid SQL injection and am using VBA connecting to a mysql DB via ADO in VB.

The problem I am having is that for the line

Set rs = cmd.Execute

I get the following error that I have not been able to figure in two days: "No value given for one or more required parameters".

And surely, when printing the param string, it returns this: (I have noticed the difference in params.. not sure why it occurs)

INSERT INTO prm1=? VALUES prm2=?, prm3=?, prm4=?, prm5=?, prm6=?, prm7=?, prm8=?, prm9=?, prm10=?, prm11=?, prm12=?, prm13=?;

This is the code that I am using for clarification:

Function addToServer(file As String, server As Integer, lastRow As Integer)

Sheets("usage").Select
Dim str As String
Dim i As Integer

Dim conn As ADODB.Connection
Set conn = DBConnection
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Dim cmd As ADODB.Command
 Dim prm2 As ADODB.Parameter
  Dim prm3 As ADODB.Parameter
   Dim prm4 As ADODB.Parameter
    Dim prm5 As ADODB.Parameter
     Dim prm6 As ADODB.Parameter
      Dim prm7 As ADODB.Parameter
       Dim prm8 As ADODB.Parameter
        Dim prm10 As ADODB.Parameter
         Dim prm11 As ADODB.Parameter
          Dim prm12 As ADODB.Parameter
           Dim prm13 As ADODB.Parameter
            Dim prm14 As ADODB.Parameter
             Dim prm15 As ADODB.Parameter

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn

    With cmd
        If server <> 0 Then
            .CommandText = "INSERT INTO NLVMerlinResults (Year, Month, Day, Lab, Station, IP, thisWeek, Week1, Week2, Week3, Week4, Week5, WeeksInMonth, SumOverWeeks) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"
        Else
            .CommandText = "INSERT INTO STMerlinResults (Year, Month, Day, Lab, Station, IP, thisWeek, Week1, Week2, Week3, Week4, Week5, WeeksInMonth, SumOverWeeks) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ;"
        End If

        cmd.CommandType = adCmdText

        Set prm2 = .CreateParameter(Name:="year", Type:=adInteger, size:=4)
        .Parameters.Append prm2
        Set prm3 = .CreateParameter(Name:="month", Type:=adSmallInt, size:=2)
        .Parameters.Append prm3
        Set prm4 = .CreateParameter(Name:="day", Type:=adSmallInt, size:=2)
        .Parameters.Append prm4
        Set prm5 = .CreateParameter(Name:="lab", Type:=adVarChar, size:=30)
        .Parameters.Append prm5
        Set prm6 = .CreateParameter(Name:="cell", Type:=adVarChar, size:=30)
        .Parameters.Append prm6
        Set prm7 = .CreateParameter(Name:="ip", Type:=adVarChar, size:=20)
        .Parameters.Append prm7
        Set prm8 = .CreateParameter(Name:="week", Type:=adSmallInt, size:=2)
        .Parameters.Append prm8
        Set prm9 = .CreateParameter(Name:="1", Type:=adVarChar, size:=10)
        .Parameters.Append prm9
        Set prm10 = .CreateParameter(Name:="2", Type:=adVarChar, size:=10)
        .Parameters.Append prm10
        Set prm11 = .CreateParameter(Name:="3", Type:=adVarChar, size:=10)
        .Parameters.Append prm11
        Set prm12 = .CreateParameter(Name:="4", Type:=adVarChar, size:=10)
        .Parameters.Append prm12
        Set prm13 = .CreateParameter(Name:="5", Type:=adVarChar, size:=10)
        .Parameters.Append prm13
        Set prm14 = .CreateParameter(Name:="weeksinmonth", Type:=adTinyInt, size:=1)
        .Parameters.Append prm14
        Set prm15 = .CreateParameter(Name:="total", Type:=adVarChar, size:=10)
        .Parameters.Append prm15

    Dim j As Integer
    For i = 1 To lastRow
        For j = 1 To 15
            If (j = 1) Then
                prm2.value = CLng(cells(i, j))
            ElseIf (j = 2) Then
                prm3.value = CInt(cells(i, j))
            ElseIf (j = 3) Then
                prm4.value = CInt(cells(i, j))
            ElseIf (j = 4) Then
                prm5.value = CStr(cells(i, j))
            ElseIf (j = 5) Then
                prm6.value = CStr(cells(i, j))
            ElseIf (j = 6) Then
                prm7.value = CStr(cells(i, j))
            ElseIf (j = 7) Then
                prm8.value = CInt(cells(i, j))
            ElseIf (j = 8) Then
                If IsEmpty((cells(i, j))) Then
                    prm9.value = vbNullString
                Else
                    prm9.value = CStr(cells(i, j))
                End If
            ElseIf (j = 9) Then
                If IsEmpty((cells(i, j))) Then
                    prm10.value = vbNullString
                Else
                    prm10.value = CStr(cells(i, j))
                End If
            ElseIf (j = 10) Then
                If IsEmpty((cells(i, j))) Then
                    prm11.value = vbNullString
                Else
                    prm11.value = CStr(cells(i, j))
                End If
            ElseIf (j = 11) Then
                If IsEmpty((cells(i, j))) Then
                    prm12.value = vbNullString
                Else
                    prm12.value = CStr(cells(i, j))
                End If
            ElseIf (j = 12) Then
                If IsEmpty((cells(i, j))) Then
                    prm13.value = vbNullString
                Else
                    prm13.value = CStr(cells(i, j))
                End If
            ElseIf (j = 13) Then
                prm14.value = CByte(cells(i, j))
            ElseIf (j = 14) Then
                prm15.value = CStr(cells(i, j))
            End If
        Next j
           .Execute
    Next i

End With
Set cmd = Nothing
End Function

I have two different approaches that I used shown above, but it seems neither of the two work. Can someone give general direction as to what I can do to help? I tried taking the params out from the .CreateParameter function (http://www.w3schools.com/asp/met_comm_createparameter.asp says they were optional), but that didnt work either.

Best,

Ashley

EDIT:: I have added edits above. I tried a test insert and it was successful.

INSERT INTO  `NLVMerlinResults` (Year, Month, Day, Lab, Station, IP, thisWeek, Week1, Week2, Week3, Week4, Week5, WeeksInMonth, SumOverWeeks) VALUES ("1", "2", "3", "4", "5", 1, 5, "5", "5", "6", 6, "5", "4", "5")

Although its odd that it lets me enter integers and strings when the DB takes only varchar...

At least part of your problem is that you cannot parameterise a table name: only field values. See: http://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement — Tim Williams, Sep 20 '16 at 23:11
@TimWilliams By insert syntax you are referring to where the parameters are set or to what Comintern mentioned above your last comment? — noc_coder, Sep 20 '16 at 23:20
@TimWilliams I am following example like this: https://msdn.microsoft.com/en-us/library/ms675860(v=vs.85).aspx — noc_coder, Sep 20 '16 at 23:25
I'd test your sql in the DB provider *first*, and then parameterise it. — Comintern, Sep 20 '16 at 23:29
@Comintern I verified that the provider is MSDASQL.1 on this line "cmd.ActiveConnection = conn". — noc_coder, Sep 20 '16 at 23:40
No, I meant verify that your ***SQL statement*** works by executing a sample directly in the back-end. — Comintern, Sep 20 '16 at 23:41
@Comintern I have done that and it does work... now. I updated the code above. — noc_coder, Sep 20 '16 at 23:53

score 1 · Accepted Answer · edited Sep 21 '16 at 05:01

1

Remove the "qualifer=?" from the SQL statement. ODBC parameters are specified with a single ?, and have to be added in the same order as they appear in the statement:

INSERT INTO  `NLVMerlinResults` (Year, Month, Day, Lab, Station, IP, 
thisWeek, Week1, Week2, Week3, Week4, Week5, WeeksInMonth, SumOverWeeks) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

OP EDIT: Functional code provided in the question's statement.

edited Sep 21 '16 at 05:01

noc_coder

349
1
15

answered Sep 20 '16 at 23:57

Comintern

21,855
5
33
80

This is still errors out in the same place complaining that "No value was given for one or more required parameters". – noc_coder Sep 21 '16 at 00:12
@noc_coder - Change the `Set rs = cmd.Execute` to `cmd.Execute` and `cmd.CommandType = adCmdStoredProc` to `cmd.CommandType = adCmdText`. – Comintern Sep 21 '16 at 00:16
I used the debugger after making these changes because excel is just closing on cmd.Execute. Im printing each parameter and they all print out with Debug.print. However, str as a whole shows up as ...VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?). Then it just closes on that line. – noc_coder Sep 21 '16 at 00:32
@noc_coder - That's what it is supposed to be. The parameters are processed in the ODBC provider. What do you mean by "Excel is just closing"? – Comintern Sep 21 '16 at 00:35
Ahh I see. That makes sense. But yes, literally on the cmd.Execute line before iteration 2 of the for loop even starts, all the excel files including VBA close. So its not even adding the line to the DB. Even if I change it back to "Set rs = cmd.Execute" it crashes. – noc_coder Sep 21 '16 at 00:38
@noc_coder - There's nothing there that should be taking Excel down like that... I did notice that the data types of your parameters don't match the sample query in your question - The code has the first 3 as `adInteger`, but in your test `INSERT` you are passing them as strings. – Comintern Sep 21 '16 at 00:41
@noc_coder - Also try calling `Cells(i, x).Value` explicitly - it may be trying to add the `Range` as the parameter instead of calling the default member. – Comintern Sep 21 '16 at 00:43
I updated the current state above. I updated the DB types and the types here to completely match. And now its not crashing. But the error is: "Multiple step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done". So Ill look into this, although I do not see whats wrong. The params all printed when I had the debug lines there. – noc_coder Sep 21 '16 at 01:11
@noc_coder - That error means that one of your parameters is declared as the wrong type. – Comintern Sep 21 '16 at 01:13
I see what you mean. But its not making sense why the error is there.. From the DB: int(4), int(2), int(2), varchar(30), varchar(30), varchar(30), int(2), varchar(10), varchar(10), varchar(10), varchar(10), varchar(10), int(1), varchar(10). I tried both adChar and adVarChar. – noc_coder Sep 21 '16 at 01:22
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/123826/discussion-between-comintern-and-noc-coder). – Comintern Sep 21 '16 at 01:25

Parameters not adding to ADO string for INSERT statement VBA

1 Answers1