Hi!

I am using SQL Server Stored Procedures and displaying the query result with ASP. I would like to know the number of rows retruned by the query. When I use recordset object (rs) I could easily get the count by using rs.RecordCount. Is there a similar way to get the record count when using stored procedures? If I use RecordCount method here I get -1.

Any help appreciated..

Hi there,

How do you create your recordset? Most likely, you have create a forward-only and read-only (a firehose) cursor / recordset. These kinds of recordsets can't return a correct count.

Check this page for a solution for this problem:
http://www.adopenstatic.com/faq/recordcounterror.asp

and this page for better alternatives:
http://www.adopenstatic.com/faq/reco...ternatives.asp

HtH,

Imar




---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

It doesn't matter how you generate the result set, be it a standard SQL query ("select * from widgets") or a sproc ("getAllWidgets"), the result is the same. What matters is how you open the recordset.

There are several threads regarding this issue, do a forum search for posts by KenSchaefer. He's the unofficial authority on all things recordsets. He has several articles on dealing with recordset type stuff: http://www.adopenstatic.com/

Peter

Thank for your suggestions. It was helpful. I have following syntax to run stored procedure and display the record set. I could not figure out where do you insert the cursor type information.

Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = conn
objCmd.CommandText = "AllTasksByType"
objCmd.CommandType = adCmdStoredProc

Set rs = objCmd.Execute
Set objCmd = Nothing
response.write rs("name")
response.write rs("add")

Appreciate your suggestions.

Thanks.

<%
set cn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.RecordSet")

cn.open "your connection string"
rs.CursorLocation = 3 ' adUseClient
rs.Open "AllTasksByType", cn, 1, 3, 4 ' adOpenKeyset, adLockOptimistic, adCmdStoredProc

response.write("RecordCount=" & rs.recordcount)

rs.close
set rs = nothing
cn.close
set cn = nothing
%>

Thanks UNCLE,

Is there a way to call sproc with parameters using recordset object as you have demonstrated??

Neither is pretty, but gives you the correct results:

<%
set objCN = Server.CreateObject("adodb.connection")
objCN.Open "your connection"

set objCommand = Server.CreateObject("adodb.command")
with objCommand
   .CommandType = adCmdStoredProc
   .CommandText = "AllTasksByType"
   ' add your parameters here
end with

objCommand.ActiveConnection = objCN

Set objRS = objCommand.Execute
' Here you have your recordset

' Method 1:
while not objRS.EOF
    n = n + 1
    ' do something here
    objRS.MoveNext
wend
response.write("RecordCount=" & n)

' Method 2:
' Move recordset data to 2 dimensional array using GetRows()
'If not objRS.EOF then
' arrResults = objRS.GetRows
' response.write("RecordCount=" & Ubound(arrResults,2)+1)
'End If

objrs.close
%>