我有点新手,正在尝试在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(?,?,?,?,?,?,?,?)
,但仍然出现自动化错误。还尝试删除参数名称中的“@”字符,并尝试使用“:”或“?”。
sqlStr = "Update Table set ColumnA = '" & Cells(i,1).Value & "'"
cnn.Execute(sqlStr)
。 - crimsonccmd
调用,并直接调用ccn
或使用 ccmd with。 - LinkBerest