Excel VBA MySql parameterised update 'invalid parameter type'

Question

I am creating an interface where users can use excel to seamlessly alter an SQL database. I can retrieve data fine however when updating records I get an 'invalid parameter type'.

It works fine with just concatenating values into the query, however to prevent SQL injections I require a parameterised query. I have tried substituting in the ADO datatype with the value however this has not changed anything. I have tried unnamed parameters which just always submits a value of 16 to the database instead of the desired string value

Private Sub Worksheet_Change(ByVal Target As Range)
        ID = Cells(Target.Row, 1).Value
        Dim locValue As String
        locValue = Cells(Target.Row, 2).Value

        Dim Cm As ADODB.Command
        Set Cm = New ADODB.Command

        Cm.NamedParameters = True
        Cm.CommandText = "UPDATE issues SET " _
        & "location = @location " _
        & "WHERE id = " & ID

        Dim locationParameter As ADODB.Parameter

        Set locationParameter = Cm.CreateParameter("@location", adVarChar, adParamInput, 255)
        Cm.Parameters.Append locationParameter
        locationParameter.Value = locValue

        SqlConnection(Cm)
End Sub

(I am aware that ID is not yet parameterized, the issue is with the location)

Public Function SqlConnection(Cm As ADODB.Command) As ADODB.Recordset
 Dim Cn As ADODB.Connection
    Dim Server_Name As String
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String

    Server_Name = "127.0.0.1" ' Enter your server name here
    Database_Name = "issues_and_outages" ' Enter your  database name here
    User_ID = "root" ' enter your user ID here
    Password = "password" ' Enter your password here

    Set Cn = New ADODB.Connection
    Cn.Open "Driver={MySQL ODBC 8.0 ANSI Driver};Server=" & Server_Name & ";Database=" & Database_Name & _
    ";Uid=" & User_ID & ";Pwd=" & Password & ";"

    Cm.CommandType = adCmdText
    Cm.ActiveConnection = Cn

    Set SqlConnection = Cm.Execute

End Function

The server is MySQL with an issues_and_outages table, having columns with:

id (integer, unsigned, key, auto_increment)
location (varchar(255), nullable)

When updating a cell, which should update the location column, an error of

"Run-time error '-2147217887 (80040e21)': [MySQL][ODBC 8.0(a) Driver][mysqld-8.0.16] Invalid parameter type"

is given, with an error on the Cm.Execute line. However the database has a column of type varchar of size 255, which should be an adVarChar so I do not expect an error.

But, `TINYTEXT` is *not* a `VARCHAR`, as a `TEXT` data type it's closer to being a *blob of bytes*, which probably explains the `16` you were getting instead of the supplied string - so the ordinal parameters worked better than the named ones. Consider avoiding the more exotic data types and sticking to `VARCHAR`. If the reason for `TINYTEXT` is a performance concern, see "[TINYTEXT is probably never a good idea"](https://dba.stackexchange.com/a/114860/48511). — Mathieu Guindon, Jun 25 '19 at 14:57
Agreed with using `VARCHAR` if it's a small text; `TEXT` is likely inappropriate. [Reference](https://dev.mysql.com/doc/refman/8.0/en/blob.html). I bet that the `16` is the length prefix of the string. — this, Jun 25 '19 at 15:09
I have updated the mysql table so location is now a varchar of length 255, however I'm still getting the same error. Interestingly it now appears to work with Cm.NamedParameters set to false and the parameters as ? in the query — edh649, Jun 25 '19 at 15:37

score 1 · Accepted Answer · answered Jun 25 '19 at 19:20

As regularly discussed and concluded with this SO answer, the ADO API for most providers/drivers does not support named parameters for non-stored procedure SQL statement. Instead use the qmark, positional parameter style.

Set Cm = New ADODB.Command

With Cm
    .ActiveConnection = Cn
    .CommandType = adCmdText
    .CommandText = "UPDATE issues SET location = ? WHERE id = ?"

    .Parameters.Append .CreateParameter("loc_param", adVarChar, adParamInput, 255, locValue)
    .Parameters.Append .CreateParameter("id_param", adInteger, adParamInput,, ID)

    .Execute
End With

Excel VBA MySql parameterised update 'invalid parameter type'

1 Answers1

Linked