atuljadhavnetafim
Active Member
- Joined
- Apr 7, 2012
- Messages
- 341
- Office Version
- 365
- Platform
- Windows
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
But i am getting Error on below line
Set rs = cmd.Execute(, , adCmdStoredProc)
Please help i am just near about to my project
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: