One record returned but e.AffectedRows == 0?

[Mike]

Stored Procedure:

CREATE PROCEDURE [dbo].[GetBanner]

@BannerPage nvarchar(50),
@MagazineID int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @BannerID int
SELECT TOP 1
@BannerID = BannerID
FROM Banners
WHERE BannerPage=@BannerPage AND MagazineID = @MagazineID
AND StartDate <= GetDate() AND EndDate >=GetDate()
ORDER BY Views

SELECT
BannerID,
BannerFileName,
AltText,
URL,
Views
FROM Banners
WHERE BannerID = @BannerID

UPDATE Banners SET Views = Views +1 WHERE BannerID = @BannerID
UPDATE BannerStats SET Views = Views + 1 WHERE BannerID = @BannerID
END

OnSelected method:

protected void dsBanner_Selected(object sender,
SqlDataSourceStatusEventArgs e)
{
int RecordCount = e.AffectedRows;
Response.Write(RecordCount);
}


This ALWAYS gives me 0 - despite the fact that the correct banner is pulled
from the database, and updated etc. Also, when I execute the procedure (for
valid parameter values) in SSMS, I get "1 row(s) Affected". Can anyone tell
me what I need to change to get the proper value?

Thanks

 

[GD]

AffectedRows refers to the number of rows affected by the last SQL statement
to be executed. Therefore it would appear that the line
UPDATE BannerStats SET Views = Views + 1 WHERE BannerID = @BannerID does not
affect any rows, even though a record is returned from the SELECT statement
previous to it.

So the solution would be to move the two update statements to be above the
SELECT and execute them based on @@ROWCOUNT=1 from your SELECT TOP
statement.

Gary

 

[Mike]

Thanks, but it still give me an AffectedRows of 0.

Mike

AffectedRows refers to the number of rows affected by the last SQL
statement
to be executed. Therefore it would appear that the line
UPDATE BannerStats SET Views = Views + 1 WHERE BannerID = @BannerID does
not
affect any rows, even though a record is returned from the SELECT
statement
previous to it.

So the solution would be to move the two update statements to be above the
SELECT and execute them based on @@ROWCOUNT=1 from your SELECT TOP
statement.

Gary

Stored Procedure:

CREATE PROCEDURE [dbo].[GetBanner]

@BannerPage nvarchar(50),
@MagazineID int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @BannerID int
SELECT TOP 1
@BannerID = BannerID
FROM Banners
WHERE BannerPage=@BannerPage AND MagazineID = @MagazineID
AND StartDate <= GetDate() AND EndDate >=GetDate()
ORDER BY Views

SELECT
BannerID,
BannerFileName,
AltText,
URL,
Views
FROM Banners
WHERE BannerID = @BannerID

UPDATE Banners SET Views = Views +1 WHERE BannerID = @BannerID
UPDATE BannerStats SET Views = Views + 1 WHERE BannerID = @BannerID
END

OnSelected method:

protected void dsBanner_Selected(object sender,
SqlDataSourceStatusEventArgs e)
{
int RecordCount = e.AffectedRows;
Response.Write(RecordCount);
}


This ALWAYS gives me 0 - despite the fact that the correct banner is pulled
from the database, and updated etc. Also, when I execute the procedure (for
valid parameter values) in SSMS, I get "1 row(s) Affected". Can anyone tell
me what I need to change to get the proper value?

Thanks

 

[Mike]

Oh. Just found out what the problem is. Looking at the AffectedRows
documentation here:
http://msdn2.microsoft.com/en-us/li...qldatasourcestatuseventargs.affectedrows.aspx, I
find this useful snippet:
"All operations return the number of rows affected by the operation. The
AffectedRows property has the same value as the return value of the Update,
Insert, and Delete methods.

When the Select method is called and the data source is set to DataReader
mode, the return value is 0 in all cases. "

I am using a DataReader. Now I think about this, of course it's logical.
DataReader only supports a forward-only cursor. It can't go through the
recordset to get a total, then go back to the beginning to process the data.

Mike

Thanks, but it still give me an AffectedRows of 0.

Mike

AffectedRows refers to the number of rows affected by the last SQL
statement
to be executed. Therefore it would appear that the line
UPDATE BannerStats SET Views = Views + 1 WHERE BannerID = @BannerID does
not
affect any rows, even though a record is returned from the SELECT
statement
previous to it.

So the solution would be to move the two update statements to be above
the
SELECT and execute them based on @@ROWCOUNT=1 from your SELECT TOP
statement.

Gary

Stored Procedure:

CREATE PROCEDURE [dbo].[GetBanner]

@BannerPage nvarchar(50),
@MagazineID int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @BannerID int
SELECT TOP 1
@BannerID = BannerID
FROM Banners
WHERE BannerPage=@BannerPage AND MagazineID = @MagazineID
AND StartDate <= GetDate() AND EndDate >=GetDate()
ORDER BY Views

SELECT
BannerID,
BannerFileName,
AltText,
URL,
Views
FROM Banners
WHERE BannerID = @BannerID

UPDATE Banners SET Views = Views +1 WHERE BannerID = @BannerID
UPDATE BannerStats SET Views = Views + 1 WHERE BannerID = @BannerID
END

OnSelected method:

protected void dsBanner_Selected(object sender,
SqlDataSourceStatusEventArgs e)
{
int RecordCount = e.AffectedRows;
Response.Write(RecordCount);
}


This ALWAYS gives me 0 - despite the fact that the correct banner is pulled
from the database, and updated etc. Also, when I execute the procedure (for
valid parameter values) in SSMS, I get "1 row(s) Affected". Can anyone tell
me what I need to change to get the proper value?

Thanks