如何使用 Excel VBA 字符串中的参数运行存储过程? [英] How do I run a Stored procedure with parameters from Excel VBA string?
本文介绍了如何使用 Excel VBA 字符串中的参数运行存储过程?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用 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 字符串中的参数运行存储过程?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文