VBA:调用带有两个参数的SQL Server存储过程 [英] VBA: Call SQL Server stored procedure with two arguments

问题描述

就像标题中提到的那样，我只想从VBA调用SQL Server存储过程.

Like mentioned in the title, I just want to call a SQL Server stored procedure from VBA.

我可以这样调用存储过程:

I can call my stored procedure like this:

  EXEC  dbo.spClientXLS @Nr =  ' 131783', @date = '21.09.2014'

Nr是varChar(50)类型的值，而date是date类型的

Nr is a varChar(50) type value, and date is of type date

现在，如果我想从VBA调用它，则会收到一条错误消息.我在VBA中的代码是:

Now, if I want to call it from VBA, I get an error message. My code in VBA is:

...'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)

Date的类型为Date，Nr的类型为String.

Date is of type Date, Nr is of type String.

如果有人可以解释我，我将很高兴如何用两个参数来处理它.

I would be happy, if somebody can explain me, how I can handle it with two arguments.

致谢

推荐答案

尝试一下.

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

这篇关于VBA:调用带有两个参数的SQL Server存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！