Excel VBA To Call SQL Stored Procedure (Date parameter - No results!)

Question

I finally got this code working by adding Set NOCOUNT ON in my stored procedure. I'm having trouble getting results when I use dates as parameters though.

Code as below -

Sub Button1_Click()

Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.RecordSet
Dim WSP1 As Worksheet

Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.RecordSet

'''Clear extract area'''
Worksheets("Extract").UsedRange.Delete

'''Log into SQL Server'''
con.Open "Provider = SQLOLEDB;" & _
         "Data Source = MySource;" & _
         "Initial Catalog = MyDatabase;" & _
         "User ID = MyUser;" & _
         "Password = MyPassword;"
cmd.ActiveConnection = con

'''Set up parameters for stored procedure'''
'cmd.Parameters.Append cmd.CreateParameter("lot", adVarChar, adParamInput, 7, Range("C4"))

cmd.Parameters.Append cmd.CreateParameter("startDate", adDBTimeStamp, adParamInput, Range("C2"))
cmd.Parameters.Append cmd.CreateParameter("endDate", adDBTimeStamp, adParamInput, Range("C3"))

'adDBTimeStamp

cmd.CommandText = "DB.MyStoredProc"
Set rs = cmd.Execute(, , adCmdStoredProc)

Set WSP1 = Worksheets("Extract")
WSP1.Activate
If rs.EOF = False Then WSP1.Cells(1, 1).CopyFromRecordset rs

rs.Close
Set rs = Nothing
Set cmd = Nothing

con.Close
Set con = Nothing

End Sub

As I said, just using the first parameter by itself, I get results pasted into my Worksheet as expected. When I comment that line out and try to run with the two date parameters I get nothing.

The code runs without error but shows an empty worksheet. I've got a feeling this has something to do with date formatting but am unsure how to input the dates into SQL as it needs them.

Could somebody help please?

---Update--- I've tried setting my parameters like this -

Set prm = cmd.CreateParameter("startDate", adDate, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("startDate").Value = "2017-07-17"

Set prm = cmd.CreateParameter("endDate", adDate, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("endDate").Value = "2017-07-19"

But Excel VBA still appears to be sending date through in dd/mm/yyyy format!

---Update2---

As per @avb's answer I have changed my code to include the following -

Dim sql As String
sql = "exec DB.myStoredProc '__dateParameter1__', '__dateParameter2__' ;"
sql = Replace(sql, "__dateParameter1__", Format(Range("C2").Value, "yyyy-mm-dd"))
sql = Replace(sql, "__dateParameter2__", Format(Range("C3").Value, "yyyy-mm-dd"))

cmd.CommandText = sql
Set rs = cmd.Execute()

This appears to pass the date values in the correct format, but still returns an empty recordset. As before, testing the same string with the single value VarChar works fine. It's just when I use the 2 date parameters.

Working SQL query generated by SSMS when clicking 'Execute' in menu -

DECLARE @return_value int

EXEC    @return_value = [DB].[myStoredProc]
        @startDate = N'2017-07-20'

SELECT  'Return Value' = @return_value

GO

Working query copied from VBA (pulls single batch number)

exec DB.myStoredProc '4238176' ;

Non-working query from VBA (attempting to pull all batches after this date)

exec DB.myStoredProc '2017-07-20' ;

Yup. Inputting the parameters as yyyy-mm-dd returns results. I'm just not sure how I can force the VBA to pass the Date value in that format in ADODB (I think that's the issue). — Jamsandwich
– Jamsandwich, Commented Jul 20, 2017 at 10:27
You can test the theory by passing hard-coded params. If that fixes use the VBA format function to ensure passed as YYYY-MM-DD. — David Rushton
– David Rushton, Commented Jul 20, 2017 at 10:29
Not sure how to hard-code this. Tried #2017-07-17# but it just auto-corrects to vba format. — Jamsandwich
– Jamsandwich, Commented Jul 20, 2017 at 10:47
and Worksheets("Extract").UsedRange.clearcontents is more effective than Worksheets("Extract").UsedRange.Delete — Dy.Lee
– Dy.Lee, Commented Jul 20, 2017 at 11:02

avb · Accepted Answer · 2017-07-21 10:35:07Z

3

Replace Range("C2") in CreateParameter with
Format(Range("C2").Value, "yyyymmdd")

Date format yyyymmdd is the only one that is always recognizable to sql server, disregarding your locale.

constructing sql statement without using parameters:

Dim sql As String  
sql = "exec DB.MyStoredProc '__dateParameter__' ;"
sql = Replace(sql, "__dateParameter__", Format(Range("C2").Value, "yyyymmdd"))

cmd.CommandText = sql
Set rs = cmd.Execute()

Finally it appeared stored procedure had first, optional parameter being some other value than date, so the correct answer is:

Dim sql As String  
sql = "exec DB.MyStoredProc null, '__dateParameter__' ;"
sql = Replace(sql, "__dateParameter__", Format(Range("C2").Value, "yyyymmdd"))

cmd.CommandText = sql
Set rs = cmd.Execute()

edited Jul 21, 2017 at 10:35

answered Jul 20, 2017 at 11:00

avb

1,8821 gold badge14 silver badges25 bronze badges

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

10 Comments

Jamsandwich Over a year ago

Sorry, it doesn't produce an error. But I still don't get any values returned.

Jamsandwich Over a year ago

When I step through the code and mouse over one of these parameters it still shows the date in it's original excel format rather than this format style. That's frustrating

avb Over a year ago

Another way to do this is (I always do it this way) is to construct whole query string to be executed with VBA and then assign it to .CommandText

Jamsandwich Over a year ago

I've done similar with previous queries but never with a stored procedure taking parameters. No amount of searching has showed me an example of somebody doing this within a string, do you think it's doable? I'm going to look into setting the parameters as strings in the SP and converting within SQL.

avb Over a year ago

Surely it is, just "exec DB.MyStoredProc 'parm1', 'parm2', 'parmN'" and you have it

|

Dy.Lee · Accepted Answer · 2017-07-20 11:08:37Z

0

Setting your datetime data columns numberformat like this.

Set WSP1 = Worksheets("Extract")
With WSP1
If rs.EOF = False Then .Cells(1, 1).CopyFromRecordset rs

    .Columns("c").NumberFormat = "yyyy-mm-dd" '<~~~ datetime data column c
End With

answered Jul 20, 2017 at 11:08

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

1 Comment

Jamsandwich Over a year ago

Hi @Dy.Lee. This doesn't seem to have made any difference. I'm really confused about why my VBA won't work.

Collectives™ on Stack Overflow

Excel VBA To Call SQL Stored Procedure (Date parameter - No results!)

2 Answers 2

10 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related