One record returned but e.AffectedRows == 0?

M

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
 
G

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
 
M

Mike

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

Mike

GD said:
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 said:
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
 
M

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


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

Mike

GD said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top