问题描述
我正在尝试使用 VBA 从 Excel 运行存储过程,但我收到以下代码的类型不匹配"错误.我可以执行一个存储过程,参数是作为字符串传递给 Command 对象的,如下所示?
I am trying to run a stored procedure from Excel using VBA, but I'm getting a "Type Mismatch" error with the following code. Can I execute a Stored procedure with parameters as a string passed to the Command object as shown below?
Function Sproc()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As ADODB.Command
Dim ConnectionString As String
Dim StrSproc As String
ConnectionString = "Provider=SQLOLEDB;Data Source=DBSource;" & _
"Initial Catalog=CurrentDb;" & _
"Integrated Security=SSPI;"
'Opens connection to the database
cnn.Open ConnectionString
'Timeout error in seconds for executing the entire query; this will run for 15 minutes
'before VBA times out, but your database might timeout before this value
cnn.CommandTimeout = 900
StrSproc = "EXEC StoredProcedure " & _
"@parameter1 = 0," & _
"@parameter2 = 0," & _
"@parameter3 = 0,"
Application.StatusBar = "Running stored procedure..."
Set rst = cmd.Execute(, , StrSproc)
End Function
推荐答案
试试这个:
Option Explicit
Function Sproc()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As ADODB.Command
Dim cnnStr As String
Dim Rs As New ADODB.Recordset
Dim StrSproc As String
cnnStr = "Provider=SQLOLEDB;Data Source=DBSource;" & "Initial Catalog=CurrentDb;" & _
"Integrated Security=SSPI;"
With cnn
.CommandTimeout = 900
.ConnectionString = cnnStr
.Open
End With
With cmd
.ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = "[StoredProcedureName]"
.Parameters.Append .CreateParameter("@parameter1", adInteger, adParamInput, , 0)
.Parameters.Append .CreateParameter("@parameter2", adInteger, adParamInput, , 0)
.Parameters.Append .CreateParameter("@parameter2", adInteger, adParamInput, , 0)
End With
With Rs
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open cmd
End With
Application.StatusBar = "Running stored procedure..."
Set rst = cmd.Execute
End Function
这篇关于如何使用 Excel VBA 字符串中的参数运行存储过程?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,WP2