c# ExecuteNonQuery always returns Zero

Question

I think nothing's wrong with the connection because when I open it, it does not throw any error. So I guess the error is when I'm executing a command. This is my code:

OleDbCommand cmd = new OleDbCommand("SELECT * FROM cars", conn);
cmd.CommandType = CommandType.Text;
int numbers = cmd.ExecuteNonQuery();

I tried using a message box to display the value of numbers but the result is always 0. The table cars contains 5 records. So why am I not getting the correct results?..

Paul Sasik · Accepted Answer · 2011-10-14T16:37:24.037

4

To the anonymous downvoter, the key part of the OP:

I tried using a message box to display the value of numbers but the result is always 0. The table cars contains 5 records. So why am I not getting the correct results?..

The OP is obviously trying to get a count of records in the table (a scalar aggregate) and not all of the table data.

My answer:

That's because your query is returning a table and not a scalar value and you're calling the incorrect function. Your query should be should be:

"SELECT COUNT(*) FROM cars"

And ExecuteNonQuery doesn't actually expect any results to be returned. (You usually run insert, update and delete operations with ExecuteNonQuery.) You should be using ExecuteScalar which expects a single-valued result such as count(*).

All together now:

OleDbCommand cmd = new OleDbCommand("SELECT count(*) FROM cars", conn);
cmd.CommandType = CommandType.Text;
int numbers = cmd.ExecuteScalar();

edited Oct 14 '11 at 16:37

answered Oct 14 '11 at 16:21

Paul Sasik

79,492
20
149
189

For a select * query like he has shown ExecuteScalar does not help. – Davide Piras Oct 14 '11 at 16:26
Cool! Nice! Can't Accept yet though, it tells me "You can accept an answer after 6minutes". hehe Anyways, Thank you! But how can I get the different fields for example id, names and such?.. – jovhenni19 Oct 14 '11 at 16:28
@DavidePiras: If you read the question closely you'll see that the query is not correct. The OP is trying to get an aggregate... a count of records in the table. – Paul Sasik Oct 14 '11 at 16:28
No because question is unclear and he mentions numbers but not clearly aggregates... I didnt vote you down anyway. – Davide Piras Oct 14 '11 at 16:37
@DavidePiras: No, the question is not explicit but the implication is clear. The OP expects a single number to be returned. The number of items in the table. The terms are not used but it is a scalar, aggregate value. count(*) is an aggregate function. – Paul Sasik Oct 14 '11 at 16:39
You cannot infer so. what if he has a column called numbers in that table? anyway does not matter. i voted you up to balance the phantom down vote of smbd else... – Davide Piras Oct 14 '11 at 16:42

score 1 · Answer 2 · answered Oct 14 '11 at 16:21

1

Try using ExecuteScalar that should give you the count. ExecuteNonQuery doesn't return the results from your query. The return your looking at indicates how many rows were affected by your statement, in your case zero.

answered Oct 14 '11 at 16:21

Orn Kristjansson

3,435
4
26
40

score 1 · Answer 3 · answered Oct 14 '11 at 16:24

ExecuteNonQuery as the name tells you does not make a query. it is normally used for inserts or updates and returns the number of affected records. for the query you provided you should use ExecuteReader or a DataAdapter and its Fill method to fill a datatable.

score 0 · Answer 4 · answered Oct 14 '11 at 16:22

0

Because you are executing a query, and ExecuteNonQuery returns the number of rows effected, which when you select is always 0 since you aren't changing anything (ie. INSERT,UPDATE or DELETE)

answered Oct 14 '11 at 16:22

curtisk

19,950
4
55
71

robyaw · Answer 5 · 2011-10-14T16:28:38.867

0

ExecuteNonQuery only returns the number of rows affected for UPDATE, DELETE or INSERT operations. For the number of rows in the SELECT statement, try:

OleDbCommand cmd = new OleDbCommand("SELECT * FROM cars", conn);
OleDbDataReader reader = cmd.ExecuteReader();

int numbers = 0;
while (reader.Read())
{
    //// other Reader-related operations here...

    numbers++;
}

edited Oct 14 '11 at 16:28

answered Oct 14 '11 at 16:22

robyaw

2,274
2
22
29

But this is really bad if he needs a count should count in SQL not client side looping of all records! – Davide Piras Oct 14 '11 at 16:39
Agreed, a COUNT is much better, though am I assuming the OP may want to use the result set for other purposes. – robyaw Oct 14 '11 at 17:11

c# ExecuteNonQuery always returns Zero

5 Answers5