Solved

I Need the Rowcount from the Command Object

Posted on 2001-08-02
12
272 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Edureka is one of the fastest growing and most effective online learning sites.  We are here to help you succeed.

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now