vba - 在vba excel中执行参数化插入查询

Question

对此有点陌生，我正在尝试在 vba excel 中使用参数化查询执行插入查询(到 Oracle 数据库)

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ccmd As New ADODB.Command

str = "Provider=MSDAORA;Data Source=db;Persist Security Info=True;Password=pword;User ID=uname"
Set cnn = CreateObject("ADODB.Connection")
cnn.Open str
Set rs = CreateObject("ADODB.Recordset")

ccmd.ActiveConnection = cnn
ccmd.CommandText = "Insert into Table Values(@col1,@col5,@col8,@col6,@col7,@col2,@col3,@col4)"
ccmd.CommandType = adCmdText

ccmd.Parameters.Append ccmd.CreateParameter("@col1", adVarChar, adParamInput, 50, Cells(i, 1).Value)
ccmd.Parameters.Append ccmd.CreateParameter("@col5", adVarChar, adParamInput, 50, Cells(i, 5).Value)
ccmd.Parameters.Append ccmd.CreateParameter("@col8", adVarChar, adParamInput, 50, Cells(i, 8).Value)
ccmd.Parameters.Append ccmd.CreateParameter("@col6", adVarChar, adParamInput, 50, Cells(i, 6).Value)
ccmd.Parameters.Append ccmd.CreateParameter("@col7", adVarChar, adParamInput, 50, Cells(i, 7).Value)
ccmd.Parameters.Append ccmd.CreateParameter("@col2", adVarChar, adParamInput, 50, Cells(i, 2).Value)
ccmd.Parameters.Append ccmd.CreateParameter("@col3", adVarChar, adParamInput, 50, Cells(i, 3).Value)
ccmd.Parameters.Append ccmd.CreateParameter("@col4", adVarChar, adParamInput, 50, Cells(i, 4).Value)

'execute the command here,  im having an error here. I'm not sure how to execute the command. I'm also not sure whether the error i'm getting is caused by how im executing the command or something else.

'I've tried:
'ccmd.Execute
'cnn.Execute(ccmd.CommandText)   
'rs = ccmd.execute

Automation Error

这就是我得到的

编辑:

尝试将我的查询更改为插入表值(?,?,?,?,?,?,?,?)，但我仍然收到自动化错误。还尝试删除参数名称中的“@”字符并尝试使用“:”或“?”。

Answer 1

有几种方法可以在插入中包含范围，但您确定连接良好吗？您可以使用以下代码进行检查(请参阅打开后的 If)，因为自动化错误是一个相当普遍的错误，并且可能包含几个不同的可能问题(用户没有正确的权限是常见的)。

如果这有效，下一步将是更新您的问题或评论新问题，您可以获得有关向 Oracle 添加单元格范围的各种方法的帮助。此外，MSDAORA 已贬值，请参阅 the accepted answer here了解更多相关信息。

' Works on one of my test systems
Dim SQLString As String
str = "Provider=MSDAORA;Data Source=db;Persist Security Info=True;Password=pword;User ID=uname"

Set cnn = New ADODB.Connection
cnn.ConnectionString = str
cnn.ConnectionTimeout = 90
' Set the connection string directly and set timeout rather than open so
' We can check if connect worked.

cnn.Open
If cnn.State = adStateOpen Then
   MsgBox "Connected"
Else
   MsgBox "Sorry. Connection Failed"
   Exit Sub
End If

' Then I'd try a simple insert command and see if that works (to target error)
' If this works then the error is likely how the insert is created
SqlString = "Insert into table Values('Something Silly')"
cnn.Execute SqlString, ,adCmdText ' As this is a direct SQL you shouldn't need adCmdText but will later

cnn.Close