Returning Record Counts SQL Server Stored Procedure

SQL Rookie here...

I am trying to return a Count from a SQL stored Procedure.  When I run it returns the value that is assigned in the .Parameters("@ReportCount") = x statement.  When I enter the passed parameters directly into the SQL it pulls back a count.  I believe that it works, but not passing the parameters.  In the .Parameters.Append .CreateParameter("@ReportCount", 2, 3, 5) statement, I have tried both 2 and 3 as the next to last parameter for Output and Input/Output.  2 returns a null value.  3 does not change that value from what I passed in.

USE [ORS]
GO
/****** Object:  StoredProcedure [dbo].[sp_SelectTransactionRange]    Script Date: 02/18/2011 08:14:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_SelectTransactionRange]
   @FromDate  varchar(10),
   @ToDate varchar(10),
   @JOBNAME varchar(10),
   @JOBNAME2 varchar(10),
   @USERDATA varchar(10),
   @SPLFNAME varchar(10),
   @Frequency varchar(1),
   @ReportCount int Output
AS

SELECT @ReportCount = COUNT(*)
FROM dbo.Transactions
WHERE JOBNAME=@JOBNAME and JOBNAME2=@JOBNAME2 and USERDATA=@USERDATA and SPLFNAME=@SPLFNAME and Frequency=@Frequency and [Transaction Date]>= @FromDate and [Transaction Date] <= @ToDate
GROUP BY JOBNAME, JOBNAME2, USERDATA, SPLFNAME, Frequency

Open in new window


	Set cmd = CreateObject("ADODB.Command")
	Set cmd.ActiveConnection = conTransactions

	with cmd
		.CommandText = "dbo.sp_SelectTransactionRange"
		.CommandType = adCmdStoredProc
		.Parameters.Append .CreateParameter("@FromDate", adVarChar, 1, 10)
		.Parameters("@FromDate") = "01/01/2011"
		.Parameters.Append .CreateParameter("@ToDate", adVarChar, 1, 10)
		.Parameters("@ToDate") = "02/15/2011"
		.Parameters.Append .CreateParameter("@JOBNAME", adVarChar, 1, 10)
		.Parameters("@JOBNAME") = rsGetAS400("JOBNAME2")
		.Parameters.Append .CreateParameter("@JOBNAME2", adVarChar, 1, 10)
		.Parameters("@JOBNAME2") = rsGetAS400("JOBNAME2")
		.Parameters.Append .CreateParameter("@USERDATA", adVarChar, 1, 10)
		.Parameters("@USERDATA") = rsGetAS400("USERDATA")
		.Parameters.Append .CreateParameter("@SPLFNAME", adVarChar, 1, 10)
		.Parameters("@SPLFNAME") = rsGetAS400("USERDATA")
		.Parameters.Append .CreateParameter("@Frequency", adVarChar, 1, 1)
		.Parameters("@Frequency") = rsGetAS400("FTPFLAG")
		.Parameters.Append .CreateParameter("@ReportCount", 2, 2, 1)
		.Parameters("@ReportCount") = 0
	
		.Execute
		
		intReportCount = .Parameters("@ReportCount")
	end with

Open in new window

michdAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
please change:
.Parameters("@ReportCount") = 0


into:
.Parameters("@ReportCount").Direction = 2 'adParamOutput

to be able to fetch the result later
0
 
michdAuthor Commented:
Thank you...
0
All Courses

From novice to tech pro — start learning today.