gpt4 book ai didi

mysql - 打开有密码 : Run-time error '-2147221241 (80040107) Automation error Unspecified error 的连接

转载 作者:行者123 更新时间:2023-11-29 07:17:42 25 4
gpt4 key购买 nike

我有一个托管在 AWS RDS-MySQL 数据库上的数据库,我试图通过单击按钮使用 VBA 将数据从 Excel 推送到数据库。

Private Sub CommandButton1_Click()
Dim con As ADODB.Connection

Set con = New ADODB.Connection
con.Open "Driver={MySQL ODBC 5.2.2 Driver};" & _
"Server=worksheetautomation.***************.rds.amazonaws.com;" & _
"Database=worksheet;" & _
"User=****;" & _
"Password=****;"
"Option=3;"

Dim rng As Range
Set rng = Application.Range("EXP")
Dim row As Range
For Each row In rng.Rows
Sql = "insert into TestExperiment(Experiment_id, Experiment_Name, Experiment_Method, Experiment_Analyst, Experiment_NumSample) values (' " & row.Cells(1).Value & " ' , ' " & row.Cells(1).Value & " ', ' " & row.Cells(2).Value & " ', ' " & row.Cells(3).Value & " ',' " & row.Cells(5).Value & ")"
con.Execute Sql
Next row

con.Close
MsgBox "Done"

End Sub

我正在尝试执行此代码并遇到运行时错误,当我尝试调试代码时:

Run-time error '-2147221241 (80040107)

con.Open "Driver={MySQL ODBC 5.2.2 Driver};" & _
"Server=worksheetautomation.***************.rds.amazonaws.com;" & _
"Database=worksheet;" & _
"User=****;" & _
"Password=****;"
"Option=3;"

这部分用黄色标记,密码行上有一个箭头。

最佳答案

考虑以下 VBA 最佳实践:

  1. 始终在代码更改后Debug\Compile 代码。事实上,一旦您的光标离开问题行,VBA 就应该提出或突出显示或用红字标记行继续的语法问题。请参阅下面添加的 &_。但这可能是您帖子中的错字。

    "Password=****;"  & _
    "Option=3;"
  2. 使用合适的error handling尤其是在与 ADO 等 API 交互时,这些 API 可以引发运行时异常以获得更多信息:

    Private Sub CommandButton1_Click()
    On Error Goto ErrHandle

    '...code...

    ExitHandle:
    Set con = Nothing ' RELEASE ALL set OBJECTS
    Exit Sub

    ErrHandle:
    Msgbox Err.Number & ": " & Err.Description
    Resume ExitHandle
    End Sub
  3. 每当您在应用程序层(即 VBA)和数据库(即 MySQL)之间导入数据时,请使用 SQL 参数化的行业最佳实践标准。其中security由于避免了困惑的引号和连接,它更加高效、可读和可维护。事实上,您当前的代码遗漏了最后一个单引号:

    " ... ' " & row.Cells(3).Value & " ',' " & row.Cells(5).Value & ")"

    大多数 DB-API,如 ADO 支持 parameterization .尽管除了 VBA 之外,其他语言和 MySQL 之外,其他数据库的实现会有所不同,但核心概念是相同的,涉及带有参数占位符的准备好的语句,这些占位符稍后与文字值绑定(bind)。考虑以下调整:

    Dim con As ADODB.Connection, cmd As ADODB.Command
    Dim rng As Range, row As Range

    Set con = New ADODB.Connection
    con.Open ...

    ' PREPARED STATEMENT (NO DATA)
    sqlStr = "INSERT INTO TestExperiment(Experiment_id, Experiment_Name, Experiment_Method, " & _
    " Experiment_Analyst, Experiment_NumSample) " & _
    " VALUES (?, ?, ?, ?, ?)"

    ' ITERATE THROUGH ROWS
    For Each row In Application.Range("EXP").Rows
    Set cmd = New ADODB.Command

    With cmd
    .ActiveConnection = con
    .CommandType = adCmdText
    .CommandText = sqlStr

    ' BIND PARAMS
    .Parameters.Append .CreateParameter("id_param", adVarChar, adParamInput, 255, row.Cells(1).Value)
    .Parameters.Append .CreateParameter("name_param", adVarChar, adParamInput, 255, row.Cells(2).Value)
    .Parameters.Append .CreateParameter("method_param", adVarChar, adParamInput, 255, row.Cells(3).Value)
    .Parameters.Append .CreateParameter("analyst_param", adVarChar, adParamInput, 255, row.Cells(4).Value)
    .Parameters.Append .CreateParameter("sample_param", adVarChar, adParamInput, 255, row.Cells(5).Value)

    ' EXECUTE ACTION
    .Execute
    End With

    Set cmd = Nothing
    Next row

关于mysql - 打开有密码 : Run-time error '-2147221241 (80040107) Automation error Unspecified error 的连接,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/58288512/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com