Solved

I Need the Rowcount from the Command Object

Posted on 2001-08-02
12
279 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
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 is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

615 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