在VBA Excel中执行带参数的插入查询

Question

在VBA Excel中执行带参数的插入查询

3

我有点新手，正在尝试在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

自动化错误

这是我收到的错误信息。

编辑：

尝试将查询更改为Insert into Table Values(?,?,?,?,?,?,?,?)，但仍然出现自动化错误。还尝试删除参数名称中的“@”字符，并尝试使用“：”或“？”。

- crimson

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- LinkBerest · Answer 1

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

如果这样可以工作，下一步将是更新您的问题或评论，并获取有关将单元格范围添加到Oracle的各种方法的帮助。此外，MSDAORA已过时，请参阅此处接受的答案以获取更多信息。

' 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