microsoft.public.inetserver.iis

Dan Guzman

3/8/2008 1:22:00 PM

> It's only returning a single result set with the RowsAffected as the
> total.

My appologies for the misinformation I posted earlier. That's what I get
for trusting my memory instead of actually reading the documentation and
running a test :-(

I think the only ways to get the individual counts is using either a result
set or output parameters as Plamen and Cowboy suggested. A variant of the
result set technique is to return the counts in a single result set:

UPDATE ...
SET @count1 = @@ROWCOUNT
UPDATE ...
SET @count2 = @@ROWCOUNT
UPDATE ...
SET @count3 = @@ROWCOUNT
SELECT @count1 AS RowsAffected
UNION ALL @count2
UNION ALL @count3

or

DECLARE TABLE @RowsAffected(RowsAffected int)
UPDATE ...
INSERT INTO @RowsAffected VALUES(@@ROWCOUNT)
UPDATE ...
INSERT INTO @RowsAffected VALUES(@@ROWCOUNT)
UPDATE ...
INSERT INTO @RowsAffected VALUES(@@ROWCOUNT)
SELECT RowsAffected FROM @RowsAffected






--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam...

"Andy Fish" <ajfish@blueyonder.co.uk> wrote in message
news:eIvGtrFgIHA.4164@TK2MSFTNGP05.phx.gbl...
> Dan,
>
> Unfortunately this is exactly what I'm doing,
>
> It's only returning a single result set with the RowsAffected as the
> total.
>
> FWIW I'm using .Net framework 2.0 with sql server 2005 express
>
> Andy
>
> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
> news:A40B7C8A-6AD9-4D52-ADFF-84A6B5A538ED@microsoft.com...
>>> is there any way at all to get the individual row counts from ado.net?
>>
>> The ExecuteReader.RowsAffected property will contain the number of rows
>> affected. Invoke the SqlDataReader.NextResult method to navigate the the
>> subsequent result sets. Untested example:
>>
>> //print row counts of each update command (assuming SET NOCOUNT OFF)
>> reader = command.ExecuteReader();
>> do
>> {
>> Console.WriteLine(string.Format("({0} row(s) affected)",
>> reader.RowsAffected.ToString()));
>> } while (reader.NextResult());
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam...
>>
>> "Andy Fish" <ajfish@blueyonder.co.uk> wrote in message
>> news:uV8a$VEgIHA.4880@TK2MSFTNGP03.phx.gbl...
>>> (this is related to my previous post yesterday but I think i'm asking a
>>> distinct question so it's a new post)
>>>
>>> say I have a stored procedure that does 3 updates:
>>>
>>> update table1 set .... where ...
>>> update table2 set .... where ...
>>> update table3 set .... where ...
>>>
>>> if I execute this using management studio, the output window shows (for
>>> instance):
>>>
>>> (1 row(s) affected)
>>> (5 row(s) affected)
>>> (10 row(s) affected)
>>>
>>> however, if I execute it in ado.net using ExecuteReader, I just get a
>>> single resultset with 16 rows affected. RxecuteNonQuery and
>>> ExecuteScalar don't help either.
>>>
>>> is there any way at all to get the individual row counts from ado.net?
>>>
>>> Thanks
>>>
>>> Andy
>>>
>>>
>>
>
>