SET NOCOUNT
-
When I Put SET NOCOUNT ON before sql statement its giving -1 rows effected in sql server 2005. But in my code i need rows effected 0 . How to do that ?
-
When I Put SET NOCOUNT ON before sql statement its giving -1 rows effected in sql server 2005. But in my code i need rows effected 0 . How to do that ?
That's an old concept for returning a value when actually no value can be returned (e.g. due to an error). Here, you told the server that it must not count the rows affected. When you now ask "how many rows were affected" it cannot return the "correct" value, you ought to expect an exception, but for compatibility with legacy applications, it returns a number. Any whole number from 0 to inifinite would pretend to be the correct result. Hence, the server must not return any value in that range. And therefore, it returns -1: it is not possible that a negative number of rows was affected. Get used to that old concept, you may encounter it more often.
-
That's an old concept for returning a value when actually no value can be returned (e.g. due to an error). Here, you told the server that it must not count the rows affected. When you now ask "how many rows were affected" it cannot return the "correct" value, you ought to expect an exception, but for compatibility with legacy applications, it returns a number. Any whole number from 0 to inifinite would pretend to be the correct result. Hence, the server must not return any value in that range. And therefore, it returns -1: it is not possible that a negative number of rows was affected. Get used to that old concept, you may encounter it more often.
Dim Ssql Dim dbstatus Dim rs '************* Ssql = " Select * from User_Access A Where A.User_pwd ='1234' " set rs = Server.CreateObject("ADODB.RecordSet") rs = DB.execute(Ssql,dbstatus) Response.Write(dbstatus) If dbstatus <> 0 Then 'Error code here Else 'code here END IF '************* Above is the code working ok . Actually this is written in dll. Now code will give error if "dbstatus" is other than 0 . Here I'm getting -1 always in my environment.
-
Dim Ssql Dim dbstatus Dim rs '************* Ssql = " Select * from User_Access A Where A.User_pwd ='1234' " set rs = Server.CreateObject("ADODB.RecordSet") rs = DB.execute(Ssql,dbstatus) Response.Write(dbstatus) If dbstatus <> 0 Then 'Error code here Else 'code here END IF '************* Above is the code working ok . Actually this is written in dll. Now code will give error if "dbstatus" is other than 0 . Here I'm getting -1 always in my environment.
..and what's the problem with changing that code to reflect reality? There aren't 0 records affected, but an "uncounted" number;
If dbstatus <> -1 Then
'Error code here
Else
'code here
END IFThen again, your If-Else wouldn't be very valuable, since it will always return -1 (unless an exception occurs or you raiserror)
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
-
When I Put SET NOCOUNT ON before sql statement its giving -1 rows effected in sql server 2005. But in my code i need rows effected 0 . How to do that ?
Hi, Because you are telling to server, "don't count the numbers of rows affected". If you need the numbers of rows affected then try setting
SET NOCOUNT OFF. All the bset.Read the article "Table Valued Parameters". --Amit
-
..and what's the problem with changing that code to reflect reality? There aren't 0 records affected, but an "uncounted" number;
If dbstatus <> -1 Then
'Error code here
Else
'code here
END IFThen again, your If-Else wouldn't be very valuable, since it will always return -1 (unless an exception occurs or you raiserror)
Bastard Programmer from Hell :suss: if you can't read my code, try converting it here[^]
I can't do that ...
-
Hi, Because you are telling to server, "don't count the numbers of rows affected". If you need the numbers of rows affected then try setting
SET NOCOUNT OFF. All the bset.Read the article "Table Valued Parameters". --Amit
SET NOCOUNT OFF is working for insert delete update but for select statement it is giving -1 always .
-
SET NOCOUNT OFF is working for insert delete update but for select statement it is giving -1 always .
Member 3487632 wrote:
for select statement it is giving -1 always
What a shocker. It is what the doc[^] says: For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command... For all other types of statements, the return value is -1 :|
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
Member 3487632 wrote:
for select statement it is giving -1 always
What a shocker. It is what the doc[^] says: For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command... For all other types of statements, the return value is -1 :|
Luc Pattyn [My Articles] Nil Volentibus Arduum
YES..it is
-
When I Put SET NOCOUNT ON before sql statement its giving -1 rows effected in sql server 2005. But in my code i need rows effected 0 . How to do that ?
Based on your other responses. You have the following code 1. SQL 2. Calling code. In a previous statement it appears that you cannot modify 2. So that means you MUST modify 1 such that it returns the value that you want. Thus for example you can structure a stored proc such that if there are no results, which you check for, then you return zero. (That means you write the code to do just that.)
-
I can't do that ...
-
SET NOCOUNT OFF is working for insert delete update but for select statement it is giving -1 always .
Member 3487632 wrote:
select statement it is giving -1 always
Why you are checking the SET NOCOUNT in the case of select? Instead of that you can check your result-set rows.. Check your result-set rows for the number of rows selected.. :)
Read the article "Table Valued Parameters". --Amit