Re: Retrieving Rows Affected Counts

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

I don't like the answer here despite I don't agree with your approach. I would never let users enter SQL commands and just execute them ad hoc. I'm investigating why SSMS can show the correct number of resultsets, rowsets and correct (unsummarized) rows affected counts.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________



"sloan" <sloan@xxxxxxxxx> wrote in message news:urCCrvskJHA.4372@xxxxxxxxxxxxxxxxxxxxxxx
The only other thing that you might hack together is this:

"InfoMessage"
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.infomessage.aspx

(also kinda related
http://msdn.microsoft.com/en-us/library/cc452062.aspx but not helpful for your issue I think)

.......

I think InfoMessage is able to deal with PRINT statements in the code.

I have no idea about DONE_IN_PROC






"Doug" <Doug@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:BE71610F-FEED-444E-8FAD-C06D00B291CF@xxxxxxxxxxxxxxxx
I guess I am hoping someone can shed some light on how to access the
DONE_IN_PROC messages referred to in BOL and other ADO posts (if that is even
possible).

I understand breaking out the commands and sending an OUTPUT parameter back
for each one but I would like to allow the user to type in multiple
consecutive statements on the front end without having to try and parse
through the command script on the backend to insert OUTPUT parameters
everywhere. Somehow, Mgmt Studio is doing this.


"sloan" wrote:


William is correct.

If you want the granularity you are speaking of (instead of a "Grand Total")
you will need to send back the data as an output parameter, one for ~each
UPDATE you're interested in.





"Doug" <Doug@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9012BD5A-325A-43AA-A690-9D7307BA914B@xxxxxxxxxxxxxxxx
> Thanks for the response but the commands are passed adhoc style so > they
> will
> be different every time (again, like you might enter into Mgmt > Studio).
> Any
> results that have a data set associated with it (like SELECT commands) > I
> obviously don't have a problem with. But for UPDATE and DELETE
> statements, I
> would like to respond to the user how many rows were affected by each > of
> the
> commands.
>
> I've found other threads talking about using SET NOCOUNT ON to > *prevent*
> this information from being returned, but I can't seem to find > anything
> specific on how to get the information if you really want it. In BOL, > SET
> NOCOUNT ON refers to preventing DONE_IN_PROC messages but I can't > figure
> out
> how to access these DONE_IN_PROC messages with SET NOCOUNT OFF.
>
>
> "William Vaughn (MVP)" wrote:
>
>> I would probably put these commands into a stored procedure and pass >> back
>> the counts (or other data) in OUTPUT parameters.
>>
>> -- >> __________________________________________________________________________
>> William R. Vaughn
>> President and Founder Beta V Corporation
>> Author, Mentor, Dad, Grandpa
>> Microsoft MVP
>> (425) 556-9205 (Pacific time)
>> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
>> http://betav.com http://betav.com/blog/billva
>> ____________________________________________________________________________________________
>>
>>
>>
>> "Doug" <Doug@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:D88BBC0C-AF96-4F6E-8075-FA57D4F80ED3@xxxxxxxxxxxxxxxx
>> > I would like to pass the following commands to SQL 2005 using >> > ADO.NET
>> > SQLCommand in VS2005:
>> >
>> > SELECT * FROM Table1 WHERE Column1 = 'SomeValue';
>> > SELECT * FROM Table2;
>> > UPDATE Table1 SET Column1 = 'something' WHERE Column2 =
>> > 'SomeOtherValue';
>> > UPDATE Table2 SET Column1 = 1234 WHERE Column2 = 'YetAnotherValue'
>> > etc...
>> >
>> > I get back the result sets OK but what I am having trouble finding >> > is
>> > how
>> > many rows were affected by each individual UPDATE statement.
>> >
>> > I know I can add @@ROWCOUNT to the script above but the commands >> > are
>> > entered
>> > in an interface similiar to Mgmt Studio and would like to display
>> > something
>> > like the Messages tab in Mgmt Studio. The commands are passed to a
>> > remote
>> > SQL Server instance, executed, and the results are returned via a >> > web
>> > service.
>> >
>> > It seems like the SqlDataReader.RecordsAffected property will give >> > me a
>> > count of rows affected, but it is a sum of all UPDATE (or DELETE)
>> > statements
>> > that executed for the whole script. So I would have to divide out >> > the
>> > separate commands and execute them separately to use that method. >> > I am
>> > wondering if there is another way...
>> >
>> > TIA!
>> >
>> >
>> >





.

Relevant Pages

  • Re: Retrieving Rows Affected Counts
    ... like you might enter into Mgmt Studio). ... results that have a data set associated with it (like SELECT commands) I ... "William Vaughn " wrote: ... separate commands and execute them separately to use that method. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: How I can sysnchronize changes in DataTable with my Database?
    ... Do you mean whether ADO .NET can generate the commands for you? ... Does your question actually mean "I don't want to bother with all that SQL. ... >>> DataTableMapping tableMapping) ... >>> foreach(DataRow myDataRow in myDataTable.Rows) ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Post Revised: A Desperate Plea for Help
    ... I got your point that generated sql is often inadequate. ... generated commands may be inadequate" but "Use the dataadapter's Update ... That's why my advice was to call the data adapter's Update method. ... But the bottom line here is that you need to create the SQL Statements ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: DataGrid problem.
    ... (and SQL) ... as well as the Insert and Update commands? ... > dataAdapters sql commands parameters Value field, ... >> to the grid, it is automatically assigned a NULL value when a new row is ...
    (microsoft.public.dotnet.framework.windowsforms.databinding)
  • Re: Post Revised: A Desperate Plea for Help
    ... I got your point that generated sql is often inadequate. ... generated commands may be inadequate" but "Use the dataadapter's Update ... That's why my advice was to call the data adapter's Update method. ... But the bottom line here is that you need to create the SQL Statements ...
    (microsoft.public.dotnet.framework.adonet)