Oh. Just found out what the problem is. Looking at the AffectedRows
documentation here:
http://msdn2.microsoft.com/en-us/lib...ectedrows.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" <in*****@newsgroups.comwrote in message
news:Oz**************@TK2MSFTNGP06.phx.gbl...
Thanks, but it still give me an AffectedRows of 0.
Mike
"GD" <No*****************************@eircom.net.NoSpam wrote in message
news:O2**************@TK2MSFTNGP02.phx.gbl...
>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" <xx*@xxx.xxxwrote in message
news:Oc**************@TK2MSFTNGP06.phx.gbl...
>>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
