Networks

Ask something
Question Locked

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

0 helpful
4 replies

by kiranaks82

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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
con.close
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

4 replies

by tony hopkinson

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

HtHs

by kiranaks82

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(feedback.feedback) 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.
Ref: http://support.microsoft.com/kb/q164485/

Please help me out

by tony hopkinson

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.

HtHs

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

This discussion is closed to new replies.