Hello, When using ASP.NET controls like datagrids and datareaders do I need the rowcount? Should I add in every stored procedure: Set NOCOUNT ON Or only for specific procedures? Which one? (Delete, update, add but not for select?) Thanks for the support!
Please remember to 'Mark as Answer' if this post answered your question!
Hello, the Update method tests the RowsAffected value returned from the server--not any @@rowcount you supply. This is generated automatically and is returned unless suppressed by SET NOCOUNT ON. It's common practice in Visual Studio to include SET NOCOUNT
ON to eliminate some overhead and to make programming easier for action queries as ADO often considers the extra resultsets generated by stored procedures as extra resultsets (because they are) and instead of simply using Set RS = RS.NextRecordset To step
to the next, developer insert SET NOCOUNT ON to turn off these counts. Now they have to go back and turn these off for their ADO.NET programs and hope the COM-based ADO code still works (it won't). ---------------------------------- In the following there
is an answer from David Sceppa from Microsoft which I have found some days ago in the internet ... but I don´t know where anymore ... Here's the scoop. It's a long message, so are the highlights only: If you're submitting updates using DataAdapters and you're
working with = stored procedures and/or triggers that modify the contents of your = database, you must make sure you set NOCOUNT properly. Otherwise, the = DataAdapter will not correctly determine whether the intended update = succeeded or failed. The solution
is to make intelligent use of the NOCOUNT setting to = ensure that only the query that the client cares about returns messages = about the number of rows it affected. The Microsoft Press title "Microsoft ADO.NET" will cover this and many = other common updating
scenarios (retrieving new auto-increment and = timestamp values, submitting hierarchical updates, detecting conflicts, = etc.) and includes examples in both VB.NET and C#. =20 Now back to the actual technical content. SET NOCOUNT ON suppresses the "(n row(s)
affected)" messages that SQL = Server returns for subsequent queries. How does this affect ADO.NET? If you're just calling stored procedures manually from ADO.NET, this = setting will affect the return value for ExecuteNonQuery and the = RecordsAffected property
on the DataReader generated by ExecuteReader. = Use SET NOCOUNT ON/OFF intelligently so the call to your stored = procedure will receive the desired value for RecordsAffected. If you want your application to receive and interpret the REAL results = of the
stored procedure call, you should make sure that only the query = that modifies the desired row will return information about the number = of rows affected. Here's an example of just such a stored procedure = that turns off the "rows affected" messages for
all queries except the = one that the client application actually cares about. SET NOCOUNT ON INSERT INTO LogTable (Description) VALUES ('About to modify a row') SET NOCOUNT OFF UPDATE MyTable SET ... SET NOCOUNT ON IF @@ROWCOUNT =3D 1 INSERT INTO LogTable
(Description) VALUES ('Update succeeded') ELSE INSERT INTO LogTable (Description) VALUES ('Update failed') Now this stored procedure will report that it modified one row if the = desired update succeeds and zero if it fails. If we had used the = default (NOCOUNT
OFF), a successful call to this stored procedure would = report that it modified three rows. A call that fails to actually = update the desired row would report that it modified two rows. The same premise holds true for triggers. If your triggers add, update
= or delete rows and you don't want your client application to receive the = "rows affected" messages, use SET NOCOUNT ON in your triggers. What if you're submitting changes via a DataAdapter? The DataAdapter = doesn't inherently know whether or not the update
really succeeded. If = the database returns a message saying that the query modified zero rows, = it assumes the update attempt failed. Otherwise, it assumes success. This means that if you're working with stored procedures and/or = triggers that modify the
contents of your database, you must make sure = you set NOCOUNT properly to ensure that the DataAdapter understands = whether the intended update succeeded or failed. You could tell the DataAdapter to submit updates through the sample = stored procedure I
just described. But if the update attempt fails, the = stored procedure will report that two rows were modified. The = DataAdapter will interpret this message as a successful update. It = doesn't know that for this particular stored procedure, modifying two
= rows implies failure but modifying three rows implies success. If you suppress all "rows affected" messages, that won't help either. = The stored procedure will report -1 row(s) affected. ADO.NET interprets = that as success as well, regardless of whether
or not the update attempt = succeeded. The solution is to make intelligent use of the NOCOUNT setting to = ensure that only the query that the client cares about returns messages = about the number of rows it affected. If you simply want to call the stored
procedure and the client = application will not check the number of records it affects, then = suppressing the "rows affected" messages by using SET NOCOUNT ON can = improve performance slightly. HTH,
ShadowDanser
Participant
1580 Points
429 Posts
Always set nocount on?
Nov 18, 2004 10:17 PM|LINK
KBrocksi_SEC
Contributor
3382 Points
627 Posts
Re: Always set nocount on?
Nov 18, 2004 10:39 PM|LINK