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

Question

For some reason, ExecuteNonQuery() in C# returns -1, though when I run a query separately, the value returns the actual value needed.

For Example:

try
{

    var connString ="Data Source=ServerName;InitialCatalog=DatabaseName;Integrated Security=true;"
    SqlConnection conn = new SqlConnection(connString);

    SqlCommand someCmd = new SqlCommand("SELECT COUNT(*) FROM SomeTable");

    someCmd.Connection = conn;

    conn.Open();

    var theCount = cmd.ExecuteNonQuery();

    conn.Close();
}
catch(Exception ex)
{
    Console.WriteLine(ex.Message);
}

When the command is executed it returns -1. Though if run the query separately,

SELECT COUNT(*) FROM SomeTable;

Column returns one row with a count of 4 if that table being queried has 4 rows.

Possible duplicate of ExecuteNonQuery for SELECT sql statement returning no rows — Orel Eraki
– Orel Eraki, Commented Jun 27, 2016 at 18:18

Salah Akbari · Accepted Answer · 2016-06-27 20:02:15Z

11

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();

edited Jun 27, 2016 at 20:02

answered Jun 27, 2016 at 18:12

Salah Akbari

40k10 gold badges85 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dougajmcdonald Over a year ago

Good answer, it's worth mentioning the specifics of ExecuteScaler, from MSDN

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

JDavila Over a year ago

Thanks! That worked great! I will have to look up ExecuteScaler in MSDN, as am unfamiliar with the (int) syntax before the cmd.ExecuteScalar();

Soonts Over a year ago

This works with a minor modification: the result I getting is long not int, trying to cast to int throws an exception.

Mohammad Daliri · Accepted Answer · 2018-03-08 21:16:49Z

0

You can use Ef core with Ado.net like this example

var context = new SampleDbContext();
using (var connection = context.Database.GetDbConnection())
{
    connection.Open();

    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT COUNT(*) FROM SomeTable";
        var result = command.ExecuteScalar().ToString();
    }
}

answered Mar 8, 2018 at 21:16

Mohammad Daliri

1,4067 gold badges26 silver badges44 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related