Passing parameters to a SQL Server stored procedure from VBA

[RRinTetons]

I've got an Excel workbook with dates in two cells, D9 and D10.

I want to pass those values to an sp called 'SetOTBDates' in SQL Server. The sp works fine when called from within SSMS. So, I wrote a little macro and assigned it to a button. SQL Profiler shows it working to the extent that the sp is called, but it seems to indicate that no parameters are being passed. The profiler shows the statement as:

EXEC SetOTBDates default default

That doesn't work at all.

Here's the macro as it exists:

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..."
    
    ' Log into our SQL Server, and run the Stored Procedure
    con.Open "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDB;Integrated Security=SSPI;Trusted_Connection=Yes;"
    cmd.ActiveConnection = con
    
    Dim prmOTBStart As ADODB.Parameter
    Dim prmOTBEnd As ADODB.Parameter
    
    ' Set up the parameters for our Stored Procedure
    cmd.Parameters.Append cmd.CreateParameter("BeginTransactionDate", adVarChar, adParamInput, Range("D9").Text)
    cmd.Parameters.Append cmd.CreateParameter("EndTransactionDate", adVarChar, adParamInput, Range("D10").Text)
    
    Application.StatusBar = "Setting Report Dates..."
    cmd.CommandText = "SetOTBDates"
    Set rs = cmd.Execute(, , adCmdStoredProc)
    
    con.Close
    Set con = Nothing
    
    Application.StatusBar = "Data successfully updated."
End Sub

From what the profiler shows it appears the sp is being called properly, but it's not getting its parameters. As I understand what's going on, VBA is pushing the two parameters and the name of the sp onto the stack, then popping them off to send to SQL Server when it executes. It appears that I'm not properly pushing the parameters onto the stack.

Pointers?

-
Richard Ray
DBA, Developer, Data Analyst
Jackson Hole Mountain Resort

 

[SkipVought]

Hi,

What's the code within the stored procedure?

 

[RRinTetons]

Here's the SP. I've been experimenting with passing the parameters as DATETIME and NVARCHAR and typing the parameters in the VBA as adDate, adVarChar and adDBDate with a variety of text and values passed from Excel. No joy.

CREATE PROCEDURE [dbo].[SetOTBDates]
	(
	 @BeginTransactionDate NVARCHAR(10)
   , @EndTransactionDate NVARCHAR(10)
   )
AS
	BEGIN

		IF EXISTS ( SELECT	1
					FROM	[dbo].[ReportDateSettings] AS [rds]
					WHERE	[rds].[ReportDateName] = 'OTBStartDate' )
			BEGIN
				UPDATE	[dbo].[ReportDateSettings]
				SET		[DateValue] = @BeginTransactionDate
				WHERE	[ReportDateSettings].[ReportDateName] = 'OTBStartDate'
			END
		ELSE
			BEGIN
				INSERT	[dbo].[ReportDateSettings]
						([ReportDateName]
					   , [DateValue]
						)
				VALUES	('OTBStartDate'
					   , @BeginTransactionDate
						)
			END

		IF EXISTS ( SELECT	1
					FROM	[dbo].[ReportDateSettings] AS [rds]
					WHERE	[rds].[ReportDateName] = 'OTBEndDate' )
			BEGIN
				UPDATE	[dbo].[ReportDateSettings]
				SET		[DateValue] = @EndTransactionDate
				WHERE	[ReportDateSettings].[ReportDateName] = 'OTBEndDate'
			END
		ELSE
			BEGIN
				INSERT	[dbo].[ReportDateSettings]
						([ReportDateName]
					   , [DateValue]
						)
				VALUES	('OTBEndDate'
					   , @EndTransactionDate
						)
			END
	 SELECT 1
	 RETURN	
	END

GO

The SP does work. This takes care of it just fine.

EXEC [dbo].[SetOTBDates] 
	@BeginTransactionDate = '2031-12-15',
	@EndTransactionDate = '2041-12-21'

-
Richard Ray
DBA, Developer, Data Analyst
Jackson Hole Mountain Resort

 

[fredericofonseca]

cmd.Parameters.Append cmd.CreateParameter("BeginTransactionDate", adVarChar, adParamInput, Range("D9").Text)
cmd.Parameters.Append cmd.CreateParameter("EndTransactionDate", adVarChar, adParamInput, Range("D10").Text)

are Range("D9").Text and Range("D10").Text dates or real text strings representing a data? if real dates convert them first to format YYYY-MM-DD and then pass those values to the createparameter method

and try the following 2 variations.
cmd.Parameters.Append cmd.CreateParameter("BeginTransactionDate", adVarChar, adParamInput, "2041-12-21")
cmd.Parameters.Append cmd.CreateParameter("EndTransactionDate", adVarChar, adParamInput, "2031-12-15")

cmd.Parameters.Append cmd.CreateParameter("@BeginTransactionDate", adVarChar, adParamInput, "2041-12-21")
cmd.Parameters.Append cmd.CreateParameter("@EndTransactionDate", adVarChar, adParamInput, "2031-12-15")

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886