ExecuteNonQuery returning -1 when using sql COUNT despite the query string

by

Based on MSDN:

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 want to return the number of rows affected by the command and save it to an int variable but since the type of statement is select so it returns -1.

Solution: If you want to get the number of rows affected by the SELECT command and save it to an int variable you can use ExecuteScalar.

var theCount = (int)cmd.ExecuteScalar();

More Related Contents:

  1. SqlBulkCopy from a List
  2. Add WHERE clauses to SQL dynamically / programmatically
  3. How to give ADO.NET Parameters
  4. SQL Server Char/VarChar DateTime Error [closed]
  5. How can I add user-supplied input to an SQL statement?
  6. ExecuteReader requires an open and available Connection. The connection’s current state is Connecting
  7. How to get last inserted id?
  8. What are the pros and cons to keeping SQL in Stored Procs versus Code [closed]
  9. What represents a double in sql server?
  10. SQL WHERE clause matching values with trailing spaces
  11. SqlConnection SqlCommand SqlDataReader IDisposable
  12. Use of SqlParameter in SQL LIKE clause not working
  13. WHERE IN (array of IDs)
  14. How can I retrieve a list of parameters from a stored procedure in SQL Server
  15. What’s the fastest way to bulk insert a lot of data in SQL Server (C# client)
  16. How to fill Dataset with multiple tables?
  17. SqlConnection.Close() inside using statement
  18. How to keep single SQL Server connection instance open for multiple request in C#?
  19. ExecuteScalar vs ExecuteNonQuery when returning an identity value
  20. Using SqlDataAdapter to insert a row
  21. Get affected rows on ExecuteNonQuery
  22. How to pass string array in SQL parameter to IN clause in SQL
  23. Check if a SQL table exists
  24. Query validation using C#
  25. Unable to connect to localDB in VS2012 – “A network-related or instance-specific error occurred while establishing a connection to SQL Server…”
  26. Why does the SqlParameter name/value constructor treat 0 as null?
  27. How do I connect to a SQL database from C#?
  28. Database Error: There is no row at position 0
  29. Passing array to a SQL Server Stored Procedure
  30. Capturing count from an SQL query

Leave a Comment