VBA/SQL Parameterized Query - Unknown Column in Field List

Question

I am working within VBA in Excel using a MySQL ODBC 5.1 Driver, and am having trouble with a parameterized SQL query that I'm trying to use to insert rows into a table. Before parameterization the query worked fine, but I ran into an issue with SQL injection where one of the columns had comments inside it that contained SQL code and messed the Insert all up. I'm now shifting towards best practice of parameterization but I can't seem to find out what I'm doing wrong with the below code and why it's giving me the "Unknown column 'p1' in 'field list'" error.

For y = 0 To 20000
        qry = "INSERT INTO ticket_system.ticket_weekly_snapshot (issue_type,key_name,summary,assignee,reporter,priority,status,resolution,created,updated,due_date,project,linked_issues,parent_link,epic_link,parent_undefined,label,comment) VALUES ("
        Dim adoCmd As Object
        Dim adoRS As Object
        Set adoCmd = CreateObject("ADODB.Command")
        With adoCmd
            .ActiveConnection = cnn
            .CommandType = adCmdText
            If alltix.Range("A2").Offset(y, 0) = "" Then
                Exit For
            End If
            For x = 0 To 17
                Dim l As Integer
                l = Len(alltix.Range("A2").Offset(y, x))
                If l = 0 Then
                    l = 10
                End If
                .Parameters.Append .CreateParameter("p" & x + 1, adLongVarChar, adParamInput, l, alltix.Range("A2").Offset(y, x))
                If x = 17 Then
                    qry = qry & "p" & x + 1 & ")"
                Else
                    qry = qry & "p" & x + 1 & ","
                End If
            Next x
        End With
        adoCmd.CommandText = qry
        Set adoRS = adoCmd.Execute

I have a messagebox prompt currently in for testing that shows me that the query is building properly, ending with "VALUES (p1, p2, ... p18)" which is how I've seen pretty much all parameterized queries working so far. All parameters get added properly to the adoCmd, no errors with values or anything like that. The "unknown column in field list" error occurs on the final line when the adoCmd executes. Can anyone help me and show me where I've gone wrong?

Answer 1

Dynamically building parameter name worked for me but could try option of unnamed parameters. Declare l variable outside loop. Really should declare all variables at top of procedure.

qry = "INSERT INTO ticket_system.ticket_weekly_snapshot " & _
      "(issue_type,key_name,summary,assignee,reporter,priority,status,resolution,created,updated,due_date,project,linked_issues,parent_link,epic_link,parent_undefined,label,comment) " & _
      "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
...
Dim l As Integer
For x = 0 To 17
    l = Len(alltix.Range("A2").Offset(y, x))
    If l = 0 Then
        l = 10
    End If
    .Parameters.Append .CreateParameter(, adLongVarChar, adParamInput, l, alltix.Range("A2").Offset(y, x))
Next

For more info review How do I use parameters in VBA in the different contexts in Microsoft Access?