Excel VBA MySql参数化更新“无效参数类型" [英] Excel VBA MySql parameterised update 'invalid parameter type'

问题描述

我正在创建一个界面，用户可以在其中使用excel无缝更改SQL数据库.我可以很好地检索数据，但是在更新记录时，我得到的是无效的参数类型".

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'.

仅将值连接到查询中就可以正常工作，但是为了防止SQL注入，我需要一个参数化查询.我试图用ADO数据类型替换该值，但是这并没有改变任何内容.我尝试了未命名的参数，这些参数始终向数据库提交值16而不是所需的字符串值

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

(我知道ID尚未参数化，问题出在位置)

(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

该服务器是具有issue_and_outages表的MySQL，其列包括:

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

• id(整数，无符号，密钥，自动递增)

• id (integer, unsigned, key, auto_increment)

位置(varchar(255)，可为空)

location (varchar(255), nullable)

更新单元格时，应该更新位置列，错误为

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

运行时错误'-2147217887(80040e21)":[MySQL] [ODBC 8.0(a)驱动程序] [mysqld-8.0.16]无效的参数类型"

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

，在Cm.Execute行上有错误.但是，数据库具有一列类型为varchar的大小为255的列，该列应为adVarChar，因此我不希望出现错误.

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.

推荐答案

正如对此 SO答案定期讨论并得出的结论，大多数提供程序/驱动程序的ADO API不支持非存储过程SQL语句的命名参数.而是使用qmark位置参数样式.

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参数化更新“无效参数类型"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！