VBA: Call SQL Server stored procedure with two arguments

Question

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 is a varChar(50) type value, and date is of type date

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

Regards

Answer 1

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

Answer 2

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