时间戳

首页 > 解决方案 > 运行时错误 3001 '参数类型错误或超出可接受范围...' INSERT INTO MySQL 数据库

mysql - 运行时错误 3001 '参数类型错误或超出可接受范围...' INSERT INTO MySQL 数据库

问题描述

我知道有很多相同类型的问题，但没有人帮助我，我真的被困住了。所以我正在尝试使用 VBA 从 Excel 表中发送数据，但我总是收到相同的错误：'运行时错误 3001'参数类型错误或超出可接受范围'问题是我不明白我的错误在哪里来自？是来自我的 SQL 查询还是来自 Visual Basic 代码？我的代码在 3 sub

Dim oConn As ADODB.Connection

第二个

Private Sub ConnectDB()
    Set oConn = New ADODB.Connection
    Dim str As String

    str = "DRIVER={MySQL ODBC 5.1 Driver};" & _
          "SERVER=XXX.XXX.XXX.XXX" & ";" & _
          "PORT=3306" & ";" & _
          "DATABASE=myDB" & ";" & _
          "USER=User" & ";" & _
          "PASSWORD=pwd" & ";" & _
          "Option=3"

    ''' error '''
    oConn.Open str
End Sub

最后一个

Sub InsertData()    
    Dim Rs As ADODB.Recordset

    Dim Requete As String

    Set Rs = New ADODB.Recordset
    Call ConnectDB
    With Sheets("Feuil5")
        Requete = "INSERT INTO Position (Emplacement,Etage,Colonne,Taille) VALUES ('1','1','1','1');"
        Rs.Open Requete, oConnect, adOpenDynamic, adLockOptimistic
    End With
    Debug.Print Requete
    oConnect.Close
    Set Rs = Nothing
End Sub

有人能找到我的错误并帮助在 vba 中实现和简单的 sql 查询吗？

标签： mysqlsqlexcelvbasql-insert

解决方案

的工作ConnectDB是创建数据库连接。不要分配给oConn在另一个范围内声明的某个对象（可能已经连接并打开），而是将其设为Function.

Private Function ConnectDB() As ADODB.Connection
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection

    Const connString As String = _
      "DRIVER={MySQL ODBC 5.1 Driver};" & _
      "SERVER=XXX.XXX.XXX.XXX;" & _
      "PORT=3306l" & _
      "DATABASE=myDB;" & _
      "USER=User;" & _
      "PASSWORD=pwd;" & _
      "Option=3"

    On Error GoTo CleanFail
    conn.Open connString
    Set ConnectDB = conn
    Exit Function
CleanFail:
    Debug.Print "Error opening database connection:"
    Debug.Print Err.Number, Err.Description
    Debug.Print "ConnectDB is returning Nothing."
    Set ConnectDB = Nothing
End Function

现在您有一个函数，它要么连接并返回 live ADODB.Connection，要么不连接并返回Nothing- 并且所有涉及的值的范围都没有超出它们需要的范围。

所以InsertData现在可以使用了。现在，我们根本不需要 a Recordset- 我们不关心任何记录，我们只想运行INSERT查询；这可能会针对连接本身运行 - 请注意，数量VALUES必须与子句中指定的列数相匹配INSERT INTO......并且由于一组硬编码的值不是很有用，您需要正确参数化这些值（不要将工作表单元格值连接到您的 SQL 语句中 -见Little Bobby Tables）：

Public Sub InsertData(ByVal emplacement As String, ByVal etage As String, ByVal colonne As String, ByVal taille As String)
    Dim conn As ADODB.Connection
    Set conn = ConnectDB
    If conn Is Nothing Then
        ' couldn't connect to database; review Debug output, fix connection string.
        MsgBox "Could not connect to database.", vbExclamation
        Exit Sub
    End If

    Const sql As String = _
        "INSERT INTO Position (Emplacement, Etage, Colonne, Taille) " &
        "VALUES(?, ?, ?, ?) "

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

    Set cmd.ActiveConnection = conn
    cmd.CommandType = adCmdText
    cmd.CommandText = sql

    'TODO: verify parameter types & sizes.
    'NOTE: parameters must be added in the order they are specified in the SQL.
    cmd.Parameters.Append cmd.CreateParameter(Type:=adVarWChar, Size:=200, Value:=emplacement)
    cmd.Parameters.Append cmd.CreateParameter(Type:=adVarWChar, Size:=200, Value:=etage)
    cmd.Parameters.Append cmd.CreateParameter(Type:=adVarWChar, Size:=200, Value:=colonne)
    cmd.Parameters.Append cmd.CreateParameter(Type:=adVarWChar, Size:=200, Value:=taille)

    On Error GoTo CleanFail
    cmd.Execute '<~ when you DO need a recordset, this gets you your recordset.
CleanExit:
    conn.Close
    Exit Sub
CleanFail:
    Debug.Print "Error executing command: " & sql
    Debug.Print Err.Number, Err.Description
    Resume CleanExit
End Sub

推荐阅读