• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 573
  • Last Modified:

@@rowcount not working

Can someone please help me?

The @@rowcount does not produce a value. There is no error when I run the stored procedure from the query analyser of from an ASP page. The records retrieved display without a problem. I am using SQL Server 2000.

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

ALTER  PROCEDURE pr_landlordDisplay (
      @intLandlordID int,
      @rowCount int OUTPUT
      )
AS
SET NOCOUNT ON        
SELECT *
FROM dbo.tblLandlords
WHERE intLandlordID = @intLandlordID

SET @rowCount = @@rowcount

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
0
savillekaufman
Asked:
savillekaufman
  • 4
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
let me ask to run this in query analyser:

declare @r int
exec pr_landlordDisplay 1, @r OUTPUT
select @r [number of rows]

what does the last query return?
0
 
savillekaufmanAuthor Commented:
It returns the correct number of rows!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then, the problem is in the code that calls this procedure.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
savillekaufmanAuthor Commented:
I really do appreciate the help and apologise for being thick, but my stored procedure reads as follows:

ALTER  PROCEDURE pr_landlordDisplay (
      @intLandlordID INT,
      @rowCount INT OUTPUT
      )

AS

SET NOCOUNT ON        
SELECT *
FROM dbo.tblLandlords
WHERE intLandlordID = @intLandlordID

SELECT @rowCount = @@rowcount


and I am running it through the query analyseras follows:

exec pr_landlordDisplay 39, 1
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>and I am running it through the query analyseras follows:
>exec pr_landlordDisplay 39, 1

which is wrong, as the second parameter needs to be a variable (like I posted above), and not a constant
0
 
lahousdenCommented:
You also need to make sure that the variable is followed by the keyword OUTPUT (as shown in Angel's first response above), otherwise the value will not be returned.  
0
 
savillekaufmanAuthor Commented:
Can you please answer one further question?

How do I access the @rowCount parameter in this ASP page?

<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "DSN=KISS"

set cmd = Server.CreateObject("ADODB.Command")
with cmd
      .ActiveConnection = con
      .CommandText = "pr_landlordList"
      .CommandType = adCmdStoredProc
      .Parameters.Append .CreateParameter("@fl", adVarChar, adParamInput, 1, request.queryString("fl"))
      .Parameters.Append .CreateParameter("@rowCount", adInteger, adParamOutput)
      set rs = .execute
end with
%>
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "DSN=KISS"

set cmd = Server.CreateObject("ADODB.Command")
with cmd
      .ActiveConnection = con
      .CommandText = "pr_landlordList"
      .CommandType = adCmdStoredProc
      .Parameters.Append .CreateParameter("@fl", adVarChar, adParamInput, 1, request.queryString("fl"))
      .Parameters.Append .CreateParameter("@rowCount", adInteger, adParamOutput)
      set rs = .execute

     rowcount = .parameters("@rowcount").value
end with
%>
0
 
savillekaufmanAuthor Commented:
I really do appreciate the help you are giving me, but I am still stuck on the issue of passing an OUTPUT parameter to my ASP pages. Here is a procedure and the relevant part of the ASP code I am using. The procedure executes perfectly through the query analyser showing the @rowCount variable, yet the ASP pages does not read any OUTPUT variables. It does display the record set.

Procedure:
ALTER     PROCEDURE pr_landlordDisplay (
      @intLandlordID INT,
      @rowCount INT OUTPUT
      )
AS
SET NOCOUNT ON        
SELECT *
FROM dbo.tblLandlords
WHERE intLandlordID = @intLandlordID
SELECT @rowCount = @@rowcount

ASP Code:
<!-- #INCLUDE FILE = "includes/adovbs.inc" -->
<%
set con = Server.CreateObject("ADODB.Connection")
con.Open "DSN=KISS"
set cmd = Server.CreateObject("ADODB.Command")
with cmd
      .ActiveConnection = con
      .CommandText = "pr_landlordDisplay"
      .CommandType = adCmdStoredProc
      .Parameters.Append .CreateParameter("@intLandlordID", adInteger, adParamInput, , request.querystring("intLandlordID"))
      .Parameters.Append .CreateParameter("@rowCount", adInteger, adParamOutput)
      set rs = .execute
      rowcount = .parameters("@rowcount").value
end with
response.write rowcount
%>
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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