Error in ASP ADO, Returns Value in Query Analyzer?? SET NOCOUNT ON??

Hi Experts,

I have a SP that runs and returns correct value in Query Analyzer, but in ASP/ADO I am getting this error

Item cannot be found in the collection corresponding to the requested name or ordinal.
or
Operation is not allowed when the object is closed.

The Sp has references to Linked Servers?

Please find attached Code.
= ====================== ASP / ADO  ===========================
Dim rsDateTmp, strFolderNames
 
set rsDateTmp = CreateObject("ADODB.Recordset")
			
			rsDateTmp.Open "spRateFieldtoNBD 3", objSQLMMDeskConn, 0, 1		
			
			'response.write rsDateTmp.Fields("vchDate").value
			
			if rsDateTmp.EOF <> True then
				response.write "test"
			else
				response.write "Error"
			end if
			
			rsDateTmp.close
			
			set rsDateTmp = Nothing
 
=============== END ASP ADO   ===========================
 
SP
==============================
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
 
 
--EXEC spRateFieldtoNBD 3
--GRANT EXECUTE on spRateFieldtoNBD to MMDeskBatchUser
ALTER  PROC spRateFieldtoNBD (@intFieldID int)
AS
 
SET NOCOUNT ON
/*
--Testing
DECLARE @intFieldID int
SET @intFieldID = 15
*/
 
DECLARE @vchPeriod varchar(10)
DECLARE @vchDorM varchar(1)
DECLARE @intDays int
CREATE TABLE #tmpTable (vchDate varchar(15))
 
--Get Rate Field Period
SET @vchPeriod = (
		SELECT vchPeriod
		FROM tblBankRatesFields
		WHERE intFieldID = @intFieldID
		)
 
--Set D or M
SET @vchDorM = charindex( 'd', @vchPeriod)
 
if @vchDorM = 0 
Begin
SET @vchDorM = charindex ('m', @vchPeriod)
end
 
set @intDays = left(@vchPeriod, @vchDorM -1)
set @vchDorM = right(@vchPeriod, LEN(@vchPeriod) - @vchDorM +1)
 
--print @intDays
--print @vchDorM
 
--Work out Date to Check
DECLARE @DatetoCheck varchar(11)
if @vchDorM = 'd'
BEGIN
SET @DatetoCheck = CONVERT(varchar(11), DATEADD(d, @intDays, GETDATE()), 113)
END
ELSE
BEGIN
SET @DatetoCheck = CONVERT(varchar(11), DATEADD(m, @intDays, GETDATE()), 113)
END
 
Print @DatetoCheck
 
 
 
DECLARE @IsHol bit
DECLARE @IsHolInner bit
 
DECLARE @NextBusDate smalldatetime
DECLARE @NextBusDateFODB varchar(11)
DECLARE @sql varchar(8000)
 
 
SET @IsHol = 1
 
DECLARE Holiday_Cursor CURSOR SCROLL FOR
		SELECT @DatetoCheck as DateCol, @DatetoCheck as DateCol1 UNION ALL -- No Adding already added...
		SELECT CONVERT(varchar(11),DATEADD(d, 1, @DatetoCheck), 113) as DateCol, DATEADD(d, 1, @DatetoCheck) as DateCol1 UNION ALL
		SELECT CONVERT(varchar(11),DATEADD(d, 2, @DatetoCheck), 113) as DateCol, DATEADD(d, 2, @DatetoCheck) as DateCol1 UNION ALL
		SELECT CONVERT(varchar(11),DATEADD(d, 3, @DatetoCheck), 113) as DateCol, DATEADD(d, 3, @DatetoCheck) as DateCol1 UNION ALL
		SELECT CONVERT(varchar(11),DATEADD(d, 4, @DatetoCheck), 113) as DateCol, DATEADD(d, 4, @DatetoCheck) as DateCol1 UNION ALL
		SELECT CONVERT(varchar(11),DATEADD(d, 5, @DatetoCheck), 113) as DateCol, DATEADD(d, 5, @DatetoCheck) as DateCol1 UNION ALL
		SELECT CONVERT(varchar(11),DATEADD(d, 6, @DatetoCheck), 113) as DateCol, DATEADD(d, 6, @DatetoCheck) as DateCol1  
	OPEN Holiday_Cursor
 
	FETCH NEXT FROM Holiday_Cursor
	INTO @NextBusDateFODB, @NextBusDate
 
	WHILE @IsHol = 1
		BEGIN
			
			--Set BusDays Info
			--print @NextBusDate 
			SET @NextBusDateFODB = REPLACE(@NextBusDateFODB, ' ', '-')
			
			--Dynamic FODB Lookup SQL
			SET @sql = 'UPDATE tblLists
					SET vchListItem = (
						SELECT Cnt
						FROM
						OPENQUERY (PIPR,
						''select count(c_eh_date) as Cnt
						from core.c_eh_exshare_holiday t
						where t.c_eh_uk = ''''Y''''
						AND c_eh_date = '''''+ @NextBusDateFODB + ''''''')) 
					WHERE vchListName = ''FODBNBDCount'''
			--insert into Temp Table
			--Print @sql
			EXEC(@sql)
		
			IF (SELECT vchListItem FROM tblLists WHERE vchListName = 'FODBNBDCount') >= 1
			BEGIN --Is Bank Hol
				SET @IsHolInner = 1
			END
			ELSE
			BEGIN --Is Not Bank Hol
				SET @IsHolInner = 0
			END
 
			UPDATE tblLists
			SET vchListItem = 0
			WHERE vchListName = 'FODBNBDCount'
			
 
			--Check if it is a weekend if it is not a Bank Hol
			IF @IsHolInner = 0
			BEGIN
				
				IF DATEPART(dw, @NextBusDateFODB) = 7 -- Sat
				BEGIN 
					
					SET @IsHolInner = 1
				END
				ELSE
				BEGIN
					SET @IsHolInner = 0
				END
				
				-- if Not Sat, Check if Sunday
				IF @IsHolInner = 0
				BEGIN
					IF DATEPART(dw, @NextBusDateFODB) = 1 --Sun
					BEGIN
						
						SET @IsHolInner = 1
					END
					ELSE
					BEGIN
						SET @IsHolInner = 0
					END
				END
			END
			
			--Set Date
			If @IsHolInner = 1 
			BEGIN
				SET @IsHol = 1
				FETCH NEXT FROM Holiday_Cursor
				INTO @NextBusDateFODB, @NextBusDate
			END
			ELSE
			BEGIN	
				SET @IsHol = 0
			END		
		END
 
	CLOSE Holiday_Cursor
	
	DEALLOCATE Holiday_Cursor
 
 
--Return NBD to Caller
--print CONVERT(varchar(11), @NextBusDate, 103)
INSERT INTO #tmpTable (vchDate)
Values (CONVERT(varchar(11), @NextBusDate, 103))
 
SELECT vchDate
FROM #tmpTable
 
 
 
 
 
 
 
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Open in new window

LVL 1
Craig LambieAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ashish PatelCommented:
Try like this in ASP code and the final ReturnVal will have the value returned from your stored procedure.
	Dim Cmmnd
	Dim CmmndParam
	Dim ReturnVal
 
	set Cmmnd = Server.CreateObject("Adodb.Command")
	Cmmnd.ActiveConnection = objSQLMMDeskConn
	Cmmnd.CommandType = 4 ' For stored procedure
	Cmmnd.CommandTimeout = 180
	Cmmnd.CommandText = "spRateFieldtoNBD"
 
	set CmmndParam = Cmmnd.CreateParameter("@vchDate", 200, 2, 50)
	Cmmnd.Parameters.append CmmndParam
	Cmmnd.Parameters.append Cmmnd.CreateParameter("@intFieldID", 3, 1, 9, 3)  
	Cmmnd.execute
	
	ReturnVal = CmmndParam.value

Open in new window

0
Craig LambieAuthor Commented:
Hi

I'm not used to this syntax for ADO command, and I got this error?

Procedure or function spRateFieldtoNBD has too many arguments specified.

Looks like you have set up to many parameters?

Would it be
 set CmmndParam = Cmmnd.CreateParameter("@vchDate")
       
        Cmmnd.Parameters.append CmmndParam
       
        Cmmnd.Parameters.append Cmmnd.CreateParameter("@intFieldID", 3)  
       
        Cmmnd.execute
0
Ashish PatelCommented:
Oay now try this


	Dim Cmmnd
	Dim CmmndParam
	Dim ReturnVal
 
	set Cmmnd = Server.CreateObject("Adodb.Command")
	Cmmnd.ActiveConnection = objSQLMMDeskConn
	Cmmnd.CommandType = 4 ' For stored procedure
	Cmmnd.CommandTimeout = 180
	Cmmnd.CommandText = "spRateFieldtoNBD"
 
	Cmmnd.Parameters.append Cmmnd.CreateParameter("@intFieldID", 3, 1, 9, 3)  
	Cmmnd.execute
	
	ReturnVal = CmmndParam.value

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Craig LambieAuthor Commented:
I don't understand what the 3, 1, 9, 3 is sorry?

Microsoft VBScript runtime error '800a01a8'

Object required: ''




Usual Script I would use?
 
comLimits.CommandText = "spCheckLimit"
						comLimits.CommandType = adCmdStoredProc
						comLimits.NamedParameters = True
						comLimits.Parameters.refresh
						
						Response.Write " Request.Form(""txtBank"")" &  request.Form("txtBank") & "<br>"
						
						comLimits.Parameters("@vchDealer") = Request.Form("txtDealer")
						comLimits.Parameters("@vchBank") = request.Form("txtBank")
						comLimits.Parameters("@vchCodetoUse") = strCFCodetoUse
						comLimits.Parameters("@monAmount") = strMultiplier
						
						comLimits.Execute
						
						strReturn = comLimits.Parameters("@RETURN_VALUE")

Open in new window

0
Ashish PatelCommented:
3, 1, 9, 3 is sorry?

3 is int
1 is for parameter input type
9 is the size of Int
3 is the value being passed to your procedure.

Well your way is though as good way. Just try changing that code and pass the parameters and you should be good.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Craig LambieAuthor Commented:
I just remembered why I decided not to use Command Object.

Return can only be a Integer?

So I am returning a date.

I guess I could return the DateSerial, then convert in ASP to normal date again?

Thoughts?
0
Craig LambieAuthor Commented:


Yep.....
Got it...
RETURN CONVERT(int, @NextBusDate)

Then in ASP
response.write formatdatetime(strReturn,2)
0
Ashish PatelCommented:
You can return a Date by passing adDate type (7) is the code for that. Try using 7 as data type
0
Craig LambieAuthor Commented:
Thanks, lead me back to where I started, but it worked!!
Ta
0
Anthony PerkinsCommented:
Why are you making this so complicated?  You have already discovered the solution.  All you have to do is add SET NOCOUNT ON to the top of your stored procedure.  You are getting the error "Item cannot be found in the collection corresponding to the requested name or ordinal." because you are trying to read the informational message "x record(s) affected"

And of course you can and should use the Command object for this.
0
Craig LambieAuthor Commented:
How would I do that?
In the SP or in ADO?

Problem I have run into is that the CONVERT(int, Date) is losing 2 days in the conversion, whatever date it is passing?
Known??
0
Anthony PerkinsCommented:
>>In the SP or in ADO?<<
SET NOCOUNT ON is T-SQL so it needs to be in your stored procedure after declaring your local variables.

>>Problem I have run into is that the CONVERT(int, Date) is losing 2 days in the conversion, whatever date it is passing?<<
What would be the purpose of converting a date column to an integer?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.