Executenonquery for Select SQL Statement Returning No Rows

ExecuteNonQuery for SELECT sql statement returning no rows

The ExecuteNonQuery Method returns the number of row(s) affected by either an INSERT, an UPDATE or a DELETE. This method is to be used to perform DML (data manipulation language) statements as stated previously.

The ExecuteReader Method will return the result set of a SELECT. This method is to be used when you're querying for a bunch of results, such as rows from a table, view, whatever.

The ExecuteScalar Method will return a single value in the first row, first column from a SELECT statement. This method is to be used when you expect only one value from the query to be returned.

In short, that is normal that you have no results from a SELECT statement while using the ExecuteNonQuery method. Use ExecuteReader instead. Using the ExecuteReader method, will will get to know how many rows were returned through the instance of the SqlDataReader object returned.

int rows = 0;

if (reader.HasRows)
    while (reader.Read())
        rows++;

return rows; // Returns the number of rows read from the reader.

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

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

ExecuteNonQuery returning a 0?

ExecuteNonQuery returns the rows affected by modifying data statements (insert, update, delete). You should use ExecuteScalar for such select statements, and for example return the user's ID value. If you want to return more than one value (either multiple rows or multiple columns), you should use ExecuteReader.

Here is your code modified to return UserID of the matched user.

//-
    SqlConnection con = new SqlConnection();
    con.ConnectionString = ("Data Source=DESKTOP-PGHMM6M;Initial Catalog=LocalUsers;Integrated Security=True");
    con.Open();                

    var cmd = new SqlCommand(@"SELECT UserId FROM App_Users3 WHERE Hash = @Hash AND Username = @Username");
    cmd.Connection = con;
    //savedPasswordHash = cmd.ExecuteScalar() as string;
    cmd.Parameters.Add("@Hash", SqlDbType.NVarChar, 50).Value= savedPasswordHash;
    cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 400).Value = AppUsername;

    if (cmd.ExecuteScalar() != null) {
        MessageBox.Show(" Query successful..something matched..  ");
        //change page.. load a profile? 
    }
}

ExecuteNonQuery() always returning -1

It seems that you want a single method to do every possible task against a database. This is practically impossible. A better approach is to have specific methods in your class that do the interaction with a database

For example you could change your Gebruikerklasse and add a method called Exists where you can fine tune the interaction for the specific task. No need to create a lot of parameters when you need only two. Use the more performant ExecuteScalar (and correct) call to get your information back from the storage, etc....

public class Gebruikerklasse 
{
    ....
    public bool Exists()
    {
        string commandText = @"Select count (*) from Gebruiker 
                              where Wachtwoord = @Wachtwoord AND 
                             Gebruikersnaam = @Gebruikersnaam;", 
        using (SqlConnection conn = new SqlConnection(DBClass.GetConnectionString()))
        using (SqlCommand cmd = new SqlCommand(commandText, conn))
        {
            conn.Open();
            cmd.Parameters.AddWithValue("@Gebruikersnaam", gebruiker.Gebruikersnaam);
            cmd.Parameters.AddWithValue("@Wachtwoord", gebruiker.Wachtwoord);
            int a = Convert.ToInt32(cmd.ExecuteScalar());
            return (a > 0);
        }
    }
}

This should be only a first step in creating an OOP approach to your tasks. Next is to learn how to separate your model from your database code.

Side note: forcing a count of the table just to discover if an entry exists or not is a waste. There is a specific SQL statement that helps a lot here.

Search for IF EXISTS and read this article

Exists vs Count The battle never ends

ExecuteNonQuery doesn't return results

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.

SQL Server 2008: Executenonquery not returning number of rows affected

I have solved it.

The culprit was the line I had put at the beginning of the stored procedure:

SET NOCOUNT ON;

By removing above line, now it is working perfectly, the number of rows affected is returned correctly to C#.

c# ExecuteNonQuery always returns Zero

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

---

Related Topics