Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

I Need the Rowcount from the Command Object

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
piratepatrol
Asked:
piratepatrol
1 Solution
 
John844Commented:
you can create an OUTPUT parameter and return the value through it.
0
 
John844Commented:
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
 
piratepatrolAuthor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
John844Commented:
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
 
John844Commented:
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
 
John844Commented:
one other thing,  if you are using the constants, then you need to include adovbs.inc at the top of your asp page.
0
 
piratepatrolAuthor Commented:
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
 
John844Commented:
can I see the part of your code dealing with the cmd object and the sp?
0
 
piratepatrolAuthor Commented:
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
 
LCPCommented:
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
 
nilapennCommented:
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
 
John844Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now