How to call SQL Server stored procedure with datetime parameter from VBA?

Question

I have a SQL Server stored procedure that accepts two input parameters of datetime datatype. I want to call it from Excel VBA.

VBA doesn't have a datetime type so I've tried something like this:

Dim spCommand As ADODB.Command
Set spCommand = New ADODB.Command
spCommand.ActiveConnection = cnn
spCommand.CommandText = "myProcedureName"
spCommand.CommandType = adCmdStoredProc
spCommand.Parameters.Refresh
spCommand.Parameters(1).Value = "6/1/2016"      'DateValue("2016-06-01") + TimeValue("00:00:00")
spCommand.Parameters(2).Value = "6/1/2016"     'DateValue("2016-06-01") + TimeValue("23:59:59")
spCommand.Execute

But I get this error:

How can I solve it?

EDIT 1
After followeing what @Joe suggested, I opened my debugger and get this, but error is still there:

Did you try the date formatted with # around it? e.g. 6/1/2016, would be #6/1/2016#. — Ryan Wildry
– Ryan Wildry, Commented Jun 13, 2016 at 13:28

Community · Accepted Answer · 2017-05-23 11:48:32Z

1

Use the DateSerial function:

spCommand.Parameters(1).Value = DateSerial(2016,1,6)

and, if you need it, optionally the TimeSerial function:

spCommand.Parameters(2).Value = DateSerial(2016,1,6) + TimeSerial(23,59,59)

VBA doesn't have a datetime type

Yes, it does:

Dim dtDateTime as Date
dtDateTime = DateSerial(2016,1,6) + TimeSerial(23,59,59)

UPDATE

Following your edit, I see that the Parameters collection has three items. The first is a return parameter, and the second and third are input parameters of type adDBTimeStamp.

Also the Command.NamedParameters property is false, so you probably can't use named parameters as you seem to be attempting to do in the screenshot accompanying your edit.

UPDATE 2

~~VBA collections are indexed starting at 1, not 0, so you should be specifying your parameters as:~~

~~spCommand.Parameters(2).Value = ... spCommand.Parameters(3).Value = ...~~

I think the above is wrong. Standard VB/VBA collections are indexed, starting at 1. I believe the rationale, mistaken in my view, was that 1-based indexes are "more intuitive".

When ADO was developed, I believe Microsoft realized that 1-based collections had been a mistake, and decided to make ADO collections 0-based. This makes them inconsistent with standard collections, but "more intuitive" to, say, javascript developers. What an inconsistent mess.

If I'm right (and it's a long time since I used ADO, and I haven't tested this), then you need:

spCommand.Parameters(1).Value = ...
spCommand.Parameters(2).Value = ...

which should eliminate your 3265 error, but doesn't solve your original problem. Your debug trace appears to show that the parameters have been set to the expected values. I can only suggest you try to generate an MCVE, including a simplified version of the Stored Procedure in question.

edited May 23, 2017 at 11:48

CommunityBot

11 silver badge

answered Jun 13, 2016 at 10:47

to StackOverflow

125k34 gold badges212 silver badges345 bronze badges

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

3 Comments

baudo2048 Over a year ago

Thanks @Joe but it doesn't work for me! I get the same error.

to StackOverflow Over a year ago

@baudo2048 - 80040e14 is a generic error. Try running under the debugger, and inspect the parameters collection after calling refresh to make sure the data types are what you expect.

baudo2048 Over a year ago

I've tried what you suggest in your UPDATE but it doesn't work, I get another error: runtime error '3265'. I upvote your answer because it solve datetime matter. I think there's something else, maybe a silly mistake in order to solve the question. That first @RETURN_VALUE is strange, my stored procedure doesn't return any value. Thanks

Collectives™ on Stack Overflow

How to call SQL Server stored procedure with datetime parameter from VBA?

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