ExecuteNonQuery doesn t return results

Question

This is my  rough  code  DAL    int i     Some other declarations  SqlCommand myCmdObject   new SqlCommand  some query     conn open    i   myCmdObject ExecuteNonQuery    conn close      The problem is  Even though there is a record present on my SELECT query  the value in i remains -1   What could be the problem

User · Answer

Because the SET NOCOUNT option is set to on. Remove the line "SET NOCOUNT ON;" in your query or stored procedure.

See more at SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete.

User · Answer

From MSDN   SqlCommand ExecuteNonQuery Method      You can use the ExecuteNonQuery to   perform catalog operations  for   example  querying the structure of a   database or creating database objects   such as tables   or to change the data   in a database without using a DataSet   by executing UPDATE  INSERT  or DELETE   statements       Although the ExecuteNonQuery returns   no rows  any output parameters or   return values mapped to parameters are   populated with data       For UPDATE  INSERT  and DELETE   statements  the return value is the   number of rows affected by the   command  When a trigger exists on a   table being inserted or updated  the   return value includes the number of   rows affected by both the insert or   update operation and the number of   rows affected by the trigger or   triggers  For all other types of   statements  the return value is -1  If   a rollback occurs  the return value is   also -1    You are using SELECT query  thus you get -1

User · Answer

What kind of query do you perform  Using ExecuteNonQuery is intended for UPDATE  INSERT and DELETE queries  As per the documentation      For UPDATE  INSERT  and DELETE   statements  the return value is the   number of rows affected by the   command  When a trigger exists on a   table being inserted or updated  the   return value includes the number of   rows affected by both the insert or   update operation and the number of   rows affected by the trigger or   triggers  For all other types of   statements  the return value is -1

User · Answer

You use EXECUTENONQUERY   for INSERT UPDATE and DELETE   But for SELECT you must use EXECUTEREADER

User · Answer

if you want to run an update  delete    or insert statement  you should use   the ExecuteNonQuery  ExecuteNonQuery   returns the number of rows affected by   the statement    How to Set Count On

User · Answer

The ExecuteNonQuery method is used for SQL statements that are not queries  such as INSERT  UPDATE      You want to use ExecuteScalar or ExecuteReader if you expect your statement to return results  i e  a query

User · Answer

Whenever you want to execute an SQL statement that shouldn t return a value or a record set  the ExecuteNonQuery should be used   So if you want to run an update  delete  or insert statement  you should use the ExecuteNonQuery  ExecuteNonQuery returns the number of rows affected by the statement  This sounds very nice  but whenever you use the SQL Server 2005 IDE or Visual Studio to create a stored procedure it adds a small line that ruins everything   That line is  SET NOCOUNT ON  This line turns on the NOCOUNT feature of SQL Server  which  Stops the message indicating the number of rows affected by a Transact-SQL statement from being returned as part of the results  and therefore it makes the stored procedure always to return -1 when called from the application  in my case a web application    In conclusion  remove that line from your stored procedure  and you will now get a value indicating the number of rows affected by the statement   Happy programming   http   aspsoft blogs com jonas 2006 10 executenonquery html

User · Answer

If what you want is to get just a single integer from the query  use   myCmdObject ExecuteScalar

User · Answer

Could you post the exact query  The ExecuteNonQuery method returns the   ROWCOUNT Sql Server variable what ever it is after the last query has executed is what the ExecuteNonQuery method returns

[c#] ExecuteNonQuery doesn't return results

Examples related to c#

Examples related to asp.net