Excel VBA更新SQL数据库时触发Automation Error报错问题求助

问题原因 #

• 直接触发语法错误的核心原因是UPDATE语句的SET子句末尾多了多余的逗号：你在[Valu]字段赋值的语句最后加了逗号，而逗号后面直接跟WHERE关键字，不符合SQL语法规范。
• 潜在的其他报错风险：
  • 如果ItmId是SQL表中的字符串类型字段，你的WHERE条件没有给strItmId加单引号包裹，也会触发语法错误
  • 直接拼接SQL字符串的方式，如果strName、strValu的值中包含单引号，会直接导致SQL语法断裂，同时存在SQL注入风险

快速修复方案（仅修复现有语法错误） #

把SQL拼接部分的代码修改如下：

' 如果ItmId是字符串类型请改成：strWhere = "ItmId = '" & Replace(strItmId, "'", "''") & "'"
strWhere = "ItmId = " & strItmId

SQLQuery = "update COCFG " & _
            "set " & _
            "[Name] = '" & Replace(strName, "'", "''") & "', " & _
            ' 这里去掉了末尾的多余逗号
            "[Valu] = '" & Replace(strValu, "'", "''") & "' " & _
            "where " & strWhere

代码中新增的Replace(xxx, "'", "''")是为了转义值中的单引号，避免字段值本身带引号时触发语法错误。

更安全的优化方案（参数化查询，推荐） #

直接用ADO的Command参数传值，完全避免拼接SQL的各类问题，对应循环内的代码修改如下：

While Cells(i, jOffset).Value <> ""
    strItmId = Cells(i, 0 + jOffset).Value
    strName = Cells(i, 2 + jOffset).Value
    strValu = Cells(i, 3 + jOffset).Value
    
    ' 参数化SQL语句，用?作为参数占位符
    SQLQuery = "update COCFG set [Name] = ?, [Valu] = ? where ItmId = ?"
    
    cmd_ADO.CommandText = SQLQuery
    cmd_ADO.ActiveConnection = cn_ADO
    ' 按顺序添加参数，对应上面三个?的位置，字段类型、长度请根据你实际表结构调整
    cmd_ADO.Parameters.Append cmd_ADO.CreateParameter("@Name", adVarChar, adParamInput, 255, strName)
    cmd_ADO.Parameters.Append cmd_ADO.CreateParameter("@Valu", adVarChar, adParamInput, 255, strValu)
    ' 如果ItmId是数值类型，把adVarChar改成adInteger，删除长度参数即可
    cmd_ADO.Parameters.Append cmd_ADO.CreateParameter("@ItmId", adVarChar, adParamInput, 50, strItmId)
    
    cmd_ADO.Execute
    ' 执行完清空参数，方便下一次循环复用Command对象
    cmd_ADO.Parameters.Clear
    
    i = i + 1
Wend

内容的提问来源于stack exchange，提问作者1._-