ASP Classic Recordset In Stored Procedure -1 Recordcount

Question

I'm working with a SQL Server stored procedure in Classic ASP with 3 parameters. I am attempting to find the recordcount, but it returns '-1'.
I saw a similar post, did what it suggested (check cursortype, and add 'set nocount on' in the stored procedure), but those changes did not impact the -1 recordcount.

Here is my code in the Classic ASP page, below.

strInterestName = request("InterestName") 
strActiveDate = request("activedate")
strExpireDate = request("expiredate")


Set objCommandSec = CreateObject("ADODB.Command") 
Set objRS = CreateObject("ADODB.RecordSet") 
objRS.cursorlocation = 3
objRS.cursortype = adOpenStatic 


With objCommandSec  
Set .ActiveConnection = objConnection  
.CommandText = "[01_cms_search_pg_select_news_items_4]"  
.CommandType = 4   
.Parameters.Append .CreateParameter("@InterestName",adVarChar,
adParamInput, 25)              
.Parameters.Append .CreateParameter("@ActiveDate",adDate, adParamInput)  
.Parameters.Append .CreateParameter("@ExpireDate",adDate,
    adParamInput)
.Parameters("@InterestName") = strInterestName
.Parameters("@ActiveDate") = strActiveDate   
.Parameters("@ExpireDate") = strExpireDate

 set objRS =.Execute()   
End With

Here is the code for the stored procedure, below:

ALTER PROCEDURE [dbo].[01_cms_search_pg_select_news_items_4]

@InterestName varchar(50), 
@ActiveDate datetime, 
@ExpireDate datetime


AS DECLARE @sql nvarchar(4000)

SELECT @sql = ' SELECT * ' + 
              ' FROM news ' +
              ' WHERE ' +
              ' bulletin_display_indicator = ''true'' '+
              ' AND ' +
              ' website_homepg_display_indicator= ''false'' '



IF @InterestName is not null    
SELECT @sql = @sql + ' AND (InterestName = @InterestName) 

IF @ExpireDate is not null and @ExpireDate IS NOT NULL
SELECT @sql = @sql + ' AND (expiredate between @ActiveDate and @ExpireDate)


SELECT @sql = @sql + '; '

EXEC sp_executesql @sql, N'@InterestName varchar(50), @ActiveDate
DateTime, @ExpireDate DateTime',@InterestName, @ActiveDate,
@ExpireDate

Unknown Unknown · Accepted Answer · 2019-06-11T07:22:36

I struggled with that for a while, then found something that works for me. It's not pretty but it does the job.

Have your Stored Procedure return 2 recordsets: one with the table data you need followed by one with the recordcount:

SELECT <all_you_need> FROM <your_table> WHERE <your_arguments>
SELECT [myCount] = @@ROWCOUNT

Then, in your ASP file:

dim objRS, dataRS, countRS
Set objRS = CreateObject("ADODB.RecordSet") 
Set dataRS = CreateObject("ADODB.RecordSet") 
Set countRS = CreateObject("ADODB.RecordSet") 
[your original call to the stored procedure]
set objRS =.Execute()
set dataRS = objRS
set countRS = objRS.nextrecordset()

countRS now contains a recordset with a single row and a single column named 'myCount' that you can query to get the recordcount. dataRS contains your original dataset.

NOTE: if you don't need to know the recordcount BEFORE you process the dataset, you can simplify like this:

dim objRS, countRS
Set objRS = CreateObject("ADODB.RecordSet") 
Set countRS = CreateObject("ADODB.RecordSet") 
[your original call to the stored procedure]
set objRS =.Execute()
[process your dataset as you need to]
set countRS = objRS.nextrecordset()

ASP Classic Recordset In Stored Procedure -1 Recordcount

1 Answers