Executing a SQL Server SP from VBA - Arguments Error

Question

I'm getting the error "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another" while trying to call a SQL Server stored procedure from Excel/VBA.

Can't figure out how to call from VBA?

Stored Procedure Parameters

CREATE PROCEDURE [dbo].[sp_Productivity_GetIndividuals] (
@startDate DATE,
@endDate DATE)
....

VBA Code

Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command

With cmd
    .ActiveConnection = con
    .CommandText = "sp_Productivity_GetIndividuals"
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("@startDate", adDate, adParamInput, , "3/1/2014")
     .Parameters.Append .CreateParameter("@endDate", adDate, adParamInput, , "3/31/2014")
    Set rs.ActiveConnection = .Execute
End With

nutsch · Accepted Answer · 2014-04-18 20:12:50Z

1

Try passing your dates as "2014-03-01" and it will work. If you start with an excel date parameter, use FORMAT to convert to string, as in:

.Parameters.Append .CreateParameter("@startDate", adDate, adParamInput, , Format(now(),"yyyy-mm-dd"))

answered Apr 18, 2014 at 20:12

nutsch

5,9622 gold badges23 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user3139397 Over a year ago

I changed the code to the below, but I still get the exact same error.

user3139397 Over a year ago

Sorry for last post (I'm a rookie on formatting). I changed the code to the suggestion but I get the exact error.

nutsch Over a year ago

What if you try .Parameters.Append .CreateParameter("@startDate", adDate, adParamInput, , "2014-03-31")?

Collectives™ on Stack Overflow

Executing a SQL Server SP from VBA - Arguments Error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related