Excel VBA - Run SQL Stored Procedure Error

Question

I have the following code in my Excel workbook that I copied from this page.

Code:

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 = MyDB;" & _
         "User ID = MyID;" & _
         "Password = MyPassword;"
cmd.ActiveConnection = con

'''Set up parameters for stored procedure'''
cmd.Parameters.Append cmd.CreateParameter("startDate", adDate, adParamInput, , Range("C2"))
cmd.Parameters.Append cmd.CreateParameter("endDate", adDate, adParamInput, , Range("C3"))

cmd.CommandText = "DB.StoredProc"
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

I get the following error message on the line 'If rs.EOF = False Then'

"Operation is not allowed when the object is closed."

This is the first time I've used these functions. What have I done wrong?

Also, have I set up the multiple parameters correctly?

---Quick Edit--- Not sure if it's worth mentioning that I have formatted my date as yyyy-mm-dd, as it is in SQL server.

Jamsandwich · Accepted Answer · 2017-07-20 09:00:58Z

1

I've solved the issue by adding SET NOCOUNT ON to my stored procedure.

I wasn't initially sure if I would have access to be able to make that change. I have a new issue though. It works if I only use a single parameter but not when I use multiple parameters.

I will close this thread, do some research and potentially open a new one.

Thanks for your help.

answered Jul 20, 2017 at 9:00

Jamsandwich

6421 gold badge6 silver badges27 bronze badges

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

Comments

Harassed Dad · Accepted Answer · 2017-07-19 11:09:16Z

0

 cmd.CommandText = "DB.StoredProc"

should be the actual name of your stored procedure - unless you've actually named it StoredProc?

answered Jul 19, 2017 at 11:09

Harassed Dad

4,7141 gold badge12 silver badges12 bronze badges

3 Comments

Jamsandwich Over a year ago

I changed the name to "DB.StoredProc" for posting the question. It is the real name on my system.

Harassed Dad Over a year ago

Oh sorry, just spotted it Dim rs As ADODB.Recordset should be Dim rs As New ADODB.Recordset

Jamsandwich Over a year ago

No problem. I changed it as you said but no luck there.

ASH · Accepted Answer · 2017-07-20 02:35:55Z

Here are two options for you to consider.

Option Explicit

Sub RunSProc()

'USE [Northwind]
'GO
'DECLARE @return_value int
'EXEC    @return_value = [dbo].[TestNewProc]
'        @ShipCountry = NULL
'SELECT  'Return Value' = @return_value
'GO

Dim con As Connection
Dim rst As Recordset
Dim strConn As String

Set con = New Connection
strConn = "Provider=SQLOLEDB;"
strConn = strConn & "Data Source=YOUR_SERVER_NAME;"
strConn = strConn & "Initial Catalog=YOUR_DB_NAME;"
strConn = strConn & "Integrated Security=SSPI;"

con.Open strConn

'Put a country name in Cell E1
Set rst = con.Execute("Exec dbo.TestNewProc '" & ActiveSheet.Range("E1").Text & "'")

'The total count of records is returned to Cell A5
ActiveSheet.Range("A5").CopyFromRecordset rst

rst.Close
con.Close

End Sub


Sub TryThis()

'USE [Northwind]
'GO
'DECLARE @return_value int
'EXEC    @return_value = [dbo].[Ten Most Expensive Products]
'SELECT  'Return Value' = @return_value
'GO

Dim con As Connection
Dim rst As Recordset

Set con = New Connection
con.Open "Provider=SQLOLEDB;Data Source=YOUR_SERVER_NAME;Initial Catalog=YOUR_DB_NAME;Integrated Security=SSPI;"

Set rst = con.Execute("Exec dbo.[Ten Most Expensive Products]")
'Results of SProc are returned to Cell A1
ActiveSheet.Range("A1").CopyFromRecordset rst

rst.Close
con.Close
End Sub

Collectives™ on Stack Overflow

Excel VBA - Run SQL Stored Procedure Error

3 Answers 3

Comments

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Related