Wrox Programmer Forums
|
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 22nd, 2003, 12:49 PM
Authorized User
 
Join Date: Jul 2003
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default Recordset count

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

 
Old October 22nd, 2003, 01:01 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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.
 
Old October 22nd, 2003, 01:04 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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
 
Old October 22nd, 2003, 01:52 PM
Authorized User
 
Join Date: Jul 2003
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.


Quote:
quote:Originally posted by planoie
 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
 
Old October 22nd, 2003, 05:13 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default

<%
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
%>

 
Old October 24th, 2003, 05:39 PM
Authorized User
 
Join Date: Jul 2003
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks UNCLE,

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

 
Old October 26th, 2003, 02:41 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
%>






Similar Threads
Thread Thread Starter Forum Replies Last Post
Count string occurences across recordset goldstein SQL Server 2005 1 July 9th, 2007 06:47 PM
Recordset Count starting from 1 till end nouky Classic ASP Databases 24 October 30th, 2006 06:22 PM
is there any in built function to count page count g.tamilselvan MySQL 1 February 15th, 2006 07:43 AM
Count, sum, count a value, return records CongoGrey Access 1 April 18th, 2005 02:25 PM
SQL record count / recordset chacquard Access VBA 4 July 3rd, 2004 04:41 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.