运行时错误“-2147217900(80040e14)”

7xzttuei 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(550)

我正在尝试使用excel更新mysql数据库，它只在mysql数据库的一列上运行良好，但如果其他列。。excel vba返回此错误
“运行时错误'-2147217900（80040e14）'”
这是我的密码

Sub ud()
Dim cmdCommand As New ADODB.Command
Dim recSet As New ADODB.Recordset
Dim cn As ADODB.Connection
Dim i As Integer
Dim sheet1 As Worksheet
Dim code, datesold As String
tf = Range("O2")
sf = Range("H3")

ActiveWorkbook.ActiveSheet.Activate
Set sheet1 = ActiveWorkbook.ActiveSheet
Set Rng = Range("Table_Query_from_20172018_1")
Set cn = New ADODB.Connection
cn.ConnectionString = "Driver=MySQL ODBC 5.3 ANSI
Driver;SERVER=localhost;PWD=12345678;UID=root;DATABASE=bio;PORT=3306"
cn.Open
cmdCommand.ActiveConnection = cn
Loop sheet1 data rows
For i = 1 To Rng.Rows.Count
ID = Rng.Cells(i, 5).Value
shf = Rng.Cells(i, 6).Value
wazaef = Rng.Cells(i, 7).Value
emtihan = Rng.Cells(i, 11).Value

 strSQLCommand = "UPDATE students INNER JOIN (e1a INNER JOIN (eshfwi INNER JOIN wanda ON eshfwi.ID = wanda.ID) ON e1a.ID = wanda.ID) ON students.ID = e1a.ID SET eshfwi." & tf & " = " & shf & ", wanda." & tf & " = " & wazaef & ", e1a." & tf & " = " & emtihan & " where eshfwi.ID = " & ID & " and wanda.ID=" & ID & " and e1a.ID=" & ID & ";"
 cmdCommand.CommandText = strSQLCommand
 cmdCommand.CommandType = adCmdText
 Set recSet = cmdCommand.Execute

Next i
End Sub

update:sql语句正在处理同一表中的另一列。。但是，当我更新另一个时，这个错误正在显示。。现在我尝试删除列并创建一个新列。。代码与某些id一起工作，然后相同的错误出现在包含此消息的报告中
https://i.stack.imgur.com/o4cy0.jpg

mysql excel vba

来源：https://stackoverflow.com/questions/48146422/run-time-error-2147217900-80040e14

2条答案

按热度按时间

yyyllmsg1#

我改了密码。。它现在的工作方式是创建一个带有连接字符串的表，其中包含sql更新命令，然后使用“for next”按id循环该命令：

Sub ud()
Dim i As Integer

Dim j As Integer

Dim k As Integer

 Dim sheet1 As Worksheet

 tf = Range("O2")

 sf = Range("H3")

Range("P8").Select

Application.CutCopyMode = False

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "ODBC;DSN=20172018;", Destination:=Range("$P$8")).QueryTable

.CommandText = Array("SELECT e1.ID FROM e1 where id=1")

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = False

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.PreserveColumnInfo = True

.ListObject.DisplayName = "Query_from_20172018t"

.Refresh BackgroundQuery:=False

End With

 Act
 iveWorkbook.ActiveSheet.Activate

 Set sheet1 = ActiveWorkbook.ActiveSheet

 Set Rng = Range("Table_Query_from_20172018_1")

 For i = 1 To Rng.Rows.Count

 ID = Rng.Cells(i, 5).Value

 shf = Rng.Cells(i, 6).Value

 wazaef = Rng.Cells(i, 7).Value

 emtihan = Rng.Cells(i, 11).Value

 With ActiveWorkbook.Connections("Connection").ODBCConnection

 .CommandText = Array("UPDATE students INNER JOIN e1a ON students.ID = e1a.ID SET e1a." & tf & " = " & emtihan & " where students.class='" & sf & "' and e1a.ID=" & ID & ";")

 End With

 ActiveWorkbook.RefreshAll

 Next i

 For j = 1 To Rng.Rows.Count

 ID = Rng.Cells(j, 5).Value

 shf = Rng.Cells(j, 6).Value

 wazaef = Rng.Cells(j, 7).Value

 emtihan = Rng.Cells(j, 11).Value

 With ActiveWorkbook.Connections("Connection").ODBCConnection

 .CommandText = Array("UPDATE students INNER JOIN eshfwi ON students.ID = eshfwi.ID SET eshfwi." & tf & " = " & emtihan & " where students.class='" & sf & "' and eshfwi.ID=" & ID & ";")

 End With

 ActiveWorkbook.RefreshAll

 Next j

For k = 1 To Rng.Rows.Count

ID = Rng.Cells(k, 5).Value

shf = Rng.Cells(k, 6).Value

 wazaef = Rng.Cells(k, 7).Value

 emtihan = Rng.Cells(k, 11).Value

 With ActiveWorkbook.Connections("Connection").ODBCConnection

 .CommandText = Array("UPDATE students INNER JOIN wanda ON students.ID = wanda.ID SET wanda." & tf & " = " & emtihan & " where students.class='" & sf & "' and wanda.ID=" & ID & ";")

End With

 ActiveWorkbook.RefreshAll

 Next k

End Sub

赞(0）回复(0）举报 2021-06-21

qrjkbowd2#

很可能sql语句有错误。
发表声明 Debug.print strSQLCommand 在你写信给 commandText ，检查立即窗口中的内容，并尝试在sql控制台上执行它。由于我们不知道您的数据类型，很难说，但可能您缺少引号。假设 tf 是文本字段，代码应为 ... SET eshfwi." & tf & " = '" & shf & "'" ... （提示：最好使用参数，例如https://codereview.stackexchange.com/questions/144063/passing-multiple-parameters-to-an-sql-query)

赞(0）回复(0）举报 2021-06-21

我来回答

运行时错误“-2147217900(80040e14)”

2条答案

相关问题

热门标签

最新问答