Solved

I Need the Rowcount from the Command Object

Posted on 2001-08-02
12
273 Views
Last Modified: 2010-05-18
Hi folks,

I have a stored procedure that performs a SELECT query.  I call it with the command object like this:

Set rs = cmd.Execute

Can anyone show me how I can retrieve the row count?  I was able to get it using @@rowcount in the stored procedure, but it only worked if I executed cmd without setting it to rs.  I know I can do an rs.Open and get a record count, but I want to use the command object in this case.  Can anyone help me?

Thanks, folks.

Jazon
0
Comment
Question by:piratepatrol
12 Comments
 
LVL 7

Expert Comment

by:John844
ID: 6345513
you can create an OUTPUT parameter and return the value through it.
0
 
LVL 7

Expert Comment

by:John844
ID: 6345524
create procedure sp_something @nRowCount int


--do your work
SET @nRowCount = @@rowcount




in your asp code

cmd.Parameters.Append cmd.CreateParameter("@nRowCount", adInteger, adParamOutput)


set rs = cmd.execute

'get the value back from output parameter
intRowCount = cmd.parameters("@nRowCount").value
0
 
LVL 3

Author Comment

by:piratepatrol
ID: 6345595
That's exactly what I did, but I get a blank or null for row count.  I can only get the row count when I do

cmd.Execute

instead of

Set rs = cmd.Execute

Our company server runs NTS 4.0.

Please help!  :)
0
ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

 
LVL 7

Expert Comment

by:John844
ID: 6345630
does the sp return the proper count when you run it from query analyser like

declare @count int
exec sp_something @count OUTPUT
select @count as RowCount

0
 
LVL 7

Expert Comment

by:John844
ID: 6345639
just double checking, you used this constant when you created the parameter in the command object "adParamOutput"

and had the keyword OUTPUT in your sp
0
 
LVL 7

Expert Comment

by:John844
ID: 6345827
one other thing,  if you are using the constants, then you need to include adovbs.inc at the top of your asp page.
0
 
LVL 3

Author Comment

by:piratepatrol
ID: 6345966
Yeah, I did all of that.  It works in Query Analyzer, but not in ASP when I do a

Set rs = cmd.Execute

Any other thoughts?
0
 
LVL 7

Expert Comment

by:John844
ID: 6346109
can I see the part of your code dealing with the cmd object and the sp?
0
 
LVL 3

Author Comment

by:piratepatrol
ID: 6346627
Here you go...

With cmd
    .ActiveConnection = cn
    .CommandText = "myprocedure"
    .CommandType = adCmdStoredProc
    .Parameters.Append = .CreateParameter("@TheCount", adInteger, adParamOutput)
    Set rs = .Execute
    i = .Parameters("@TheCount")
End With

Response.Write i
_______________________

CREATE PROCEDURE myprocedure
(
    @TheCount    INT    OUTPUT
)

AS

SELECT * FROM Titles

SET @TheCount = @@rowcount

RETURN @TheCount
________________________

What am I doing wrong?
0
 
LVL 1

Expert Comment

by:LCP
ID: 6347037
Command.Execute will return Server Cursor Recordset.
You need to specify a recordset with Client Curosr before execute the command

Suppose you have a command object "cmd" with ActiveConnection specified already.

Set rs=new Adodb.Recordset
rs.CursorLocation=adUseClient   'Use 3 if u are using ASP
rs.Open cmd
Msgbox rs.RowCount


0
 
LVL 5

Expert Comment

by:nilapenn
ID: 6347424
try creatin your procedure as

CREATE PROCEDURE myprocedure(@TheCount INT OUTPUT)
AS
begin
SELECT * FROM Titles
SET @TheCount = @@rowcount
end


I have removed the return statement
0
 
LVL 7

Accepted Solution

by:
John844 earned 50 total points
ID: 6350868
I would change
i = .Parameters("@TheCount")
to
i = .Parameters("@TheCount").value

In the sp I would remove the return statement as suggested.  If that still does not solve it, as a last resort I would change
  SELECT * FROM Titles
  SET @TheCount = @@rowcount
to
  SELECT * FROM Titles
  SELECT @TheCount = count(*) FROM Titles


I would also add
SET NOCOUNT ON
as the first line in your stored procedure.
and
SET NOCOUNT OFF
as the last line in your stored procedure.


Are you able to use with statements in your asp code?  I can't, might be my version of the asp.dll

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Live mode in DW, need to creae Session 4 95
XML Parsing Classic ASP 5 58
Using querystring in a hyperlink 3 51
IP API - need data... 4 24
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question