Recordset returning -1 recordcount from Sql Structured Query in Classic ASP

By kiranaks82 ·
I have tried simple stored procedures to return recordset

Select * From bTable

It is running good, giving proper out put.

dim rsTemp, strSql
strSql = "Exec test1"

set rsTemp = erver.CreateObject("ADODB.Recordset")
rsTemp.Open strSql, con, 1 , 3
Response.Write rsTemp.RecordCount
set con = nothing

This code is returning me 15 record count

But If I am making it bit complicated then recordset is returning -1 recordcount

Select Count(bId) as cntBid from bTable

This stored procedure is running well in SQL Server 2005, but if I am caling them in ASP pages it is returning me -1 recordcount.

It is very urgent, Please Help me

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

It's returning -1 for

by Tony Hopkinson In reply to Recordset returning -1 re ...

Select Count(bId) as cntBid from bTable

not 1 ?

-1 as a recordcount usually means record is either not applicable or in some cases not implemented.

Personally I never use it, if it's implemented by teh driver clientside then in order to get it it has to page the entire query result in, horriblt inefficient and slow.

If I want a count I'd do
Select Count(bId) as cntBid from bTable
and show the value of cntBid


Collapse -

Return recordset from stored procedure

by kiranaks82 In reply to It's returning -1 for

Hi Tony,
Thanks for your reply.
I would like to put more light on this issue.
Actually I want to get recordset from a stored procedure.
The actual query is more complicated.
So I have taken simple example here.

I have used following ASP code to Call SQL Stored Procedure (test1).

dim rsTemp, strSql
strSql = "Exec test1"
set rsTemp = Server.CreateObject("ADODB.Recordset")
rsTemp.Open strSql, con, 1, 3
Response.Write rsTemp.RecordCount

When I use simple stored procedure i.e.
Select * From TableName

I get proper output. But when I am tring to get

Select Count(bId) as Bid from TableName

It is not showing the records.

My original stored procedure query look like:

SELECT TOP (50) MAX(feedback.replyID) AS replyID, feedback.wrkID, MAX( AS feedback, MAX(employee_Master.empName) AS empName, MAX(feedback.replydate) AS replydate, feedback.empID, worklist_C.complited FROM employee_Master INNER JOIN worklist_P INNER JOIN feedback ON worklist_P.wrkID = feedback.wrkID ON employee_Master.empID = feedback.empID INNER JOIN worklist_C ON worklist_P.wrkID = worklist_C.wrkID AND employee_Master.empID = worklist_C.assignedWhom WHERE (worklist_P.assigedby = 2) GROUP BY feedback.wrkID, feedback.empID, worklist_C.complited HAVING (feedback.empID <> 2) ORDER BY replyID DESC, replydate DESC

I have found that I am not getting records when, I am using function like Count(), Max(), Sum(). But simple query are giving proper output.

I have also tried other formats of calling Stored Procedure in ASP. Giving same above out put.

Please help me out

Collapse -

Frankly puzzled

by Tony Hopkinson In reply to Return recordset from sto ...

If you have an SP

where the content is Select Count(bld) as bld From SomeTable

and execute through a query, you should get a recordset of 1 record back with one column called bld.

If that's not happening, I can only assume that something in .net is doing Execwith no result.

Try setting up a command object with the sql, exec that and direct it to your recordset.


NB most of teh adice in getting results from an SP is return value and in out params, not result sets. They should work just liek any other piece of SQL

Related Discussions

Related Forums