Solved

I Need the Rowcount from the Command Object

Posted on 2001-08-02
12
274 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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
INNER JOIN ? 8 50
Display first 3 lines of text from database field, vbscript asp 4 60
age from date of birth 4 51
Filktering Alphabetically 8 31
I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
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 video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

829 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