Store Procedure thru Excel VBA

atuljadhavnetafim

Active Member
Joined
Apr 7, 2012
Messages
278
Dear Expert

I have table "'AR1" in SQL Server "XYZ'' in Database ''Model''

and store one store procedure



USE [model]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_GetOrdersForCustomer]
@Account nchar(5)
AS
BEGIN
SELECT *
FROM AR1
WHERE Account = @Account
END

________________________________________________________________________________________

and i have Excel VBA Code to extract data from SQL table from excel parameter as follow

Code:
Sub Button1_Click()
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim par As String
Dim WSP1 As Worksheet
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset


Application.DisplayStatusBar = True
Application.StatusBar = "Contacting SQL Server..."


' Remove any values in the cells where we want to put our Stored Procedure's results.
Dim rngRange As Range
Set rngRange = Range(Cells(8, 2), Cells(Rows.Count, 1)).EntireRow
rngRange.ClearContents


' Log into our SQL Server, and run the Stored Procedure
con.Open "Provider=SQLOLEDB;Data Source=NETAFIM;Initial Catalog=MODEL;Integrated Security=SSPI;Trusted_Connection=Yes;"
cmd.ActiveConnection = con


Dim prmAccount As ADODB.Parameter


' Set up the parameter for our Stored Procedure
' (Parameter types can be adVarChar,adDate,adInteger)
cmd.Parameters.Append cmd.CreateParameter("Account", adVarChar, adParamInput, 10, Range("D2").Text)


Application.StatusBar = "Running stored procedure..."
cmd.CommandText = "SP_GetOrdersForCustomer"
Set rs = cmd.Execute(, , adCmdStoredProc)


' Copy the results to cell B7 on the first Worksheet
Set WSP1 = Worksheets(1)
WSP1.Activate
If rs.EOF = False Then WSP1.Cells(8, 2).CopyFromRecordset rs


rs.Close
Set rs = Nothing
Set cmd = Nothing


con.Close
Set con = Nothing


Application.StatusBar = "Data successfully updated."
End Sub

But i am getting Error on below line


Set rs = cmd.Execute(, , adCmdStoredProc)

Please help i am just near about to my project
 
Last edited by a moderator:

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,649
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What's the error?
 

atuljadhavnetafim

Active Member
Joined
Apr 7, 2012
Messages
278
Run-time error '-2147217900 (80040e14)':

Procedure SP_GetOrderForCustomer has no parameter and arguments were supplied.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,872
Messages
5,544,796
Members
410,635
Latest member
phoenix7771
Top