VBA：使用两个参数调用 SQL Server 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26230428/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA: Call SQL Server stored procedure with two arguments
提问by Kipcak08
Like mentioned in the title, I just want to call a SQL Server stored procedure from VBA.
就像标题中提到的那样，我只想从 VBA 调用 SQL Server 存储过程。
I can call my stored procedure like this:
我可以这样调用我的存储过程：
EXEC dbo.spClientXLS @Nr = ' 131783', @date = '21.09.2014'
Nris a varChar(50)type value, and dateis of type date
Nr是一个varChar(50)类型值，并且date是类型date
Now, if I want to call it from VBA, I get an error message. My code in VBA is:
现在，如果我想从 VBA 调用它，我会收到一条错误消息。我在 VBA 中的代码是：
...'SQL Server stored procedure which is to execute with parameters
Dim ADODBCmd As New ADODB.Command
With ADODBCmd
.ActiveConnection = objconn
.CommandTimeout = 500
.CommandText = "dbo.spClient"
.CommandType = adCmdStoredProc
End With
Set recordset = ADODBCmd.Execute(, date, Nr)
Dateis of type Date, Nris of type String.
Date是类型Date，Nr是类型String。
I would be happy, if somebody can explain me, how I can handle it with two arguments.
我会很高兴，如果有人能解释我，我如何用两个论点来处理它。
Regards
问候
回答by bodjo
Try this.
尝试这个。
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
With cmd
.ActiveConnection = objcnn
.CommandText = "spClient"
.CommandType = adCmdStoredProc
.Parameters.Refresh
If .Parameters.Count = 0 Then
.Parameters.Append cmd.CreateParameter("@Nr", adVarChar, adParamInput, 50)
.Parameters.Append cmd.CreateParameter("@date", adDate, adParamInput)
End If
.Parameters.Item("@Nr").Value = "131783"
.Parameters.Item("@date").Value = "09/21/2014"
Set rs = .Execute()
End With
回答by Paresh J
You need to add command parameters in your code to accept parameter values. Check the below code:
您需要在代码中添加命令参数以接受参数值。检查以下代码：
Set oConn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
oConn.Open strConn '' strConn will have your connection string
stProcName = "thenameofmystoredprocedurehere" 'Define name of Stored Procedure to execute.
cmd.CommandType = adCmdStoredProc 'Define the ADODB command
cmd.ActiveConnection = oConn 'Set the command connection string
cmd.CommandText = stProcName 'Define Stored Procedure to run
Set prmUser = cmd.CreateParameter("@user", adVarChar, adParamInput, 7)
prmUser.Value = strUser
cmd.Parameters.Append prmUser
Set prmApplication = cmd.CreateParameter("@application", adInteger, adParamInput)
prmApplication.Value = 1
cmd.Parameters.Append prmApplication
Set rs = cmd.Execute
Range("A1").CopyFromRecordset rs '' to copy data in recordset to excel.
'' or you can do like this
Set rs = cmd.Execute
Do Until rs.EOF
'' Do Something
rs.MoveNext
Loop
