Executenonquery Doesn't Return Results

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.

ExecuteNonQuery doesn't return value?

Please delete the line in the SP SET NOCOUNT ON; in the SP so you will get
the value

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

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 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 doesn't work

I'm not too familiar with C# and Sql integration, but I'm pretty sure you're missing a transaction commit in your last try-catch block:

        try
        {
            cmd.ExecuteNonQuery();
            MessageBox.Show("success");
            cnn.Close();
        }
        catch (Exception mess)
        {
            MessageBox.Show(mess.Message);
            trans.Rollback();
            cnn.Close();
        }

should be something like:

        try
        {
            cmd.ExecuteNonQuery();
            MessageBox.Show("success");
            trans.Commit();
            cnn.Close();
        }
        catch (Exception mess)
        {
            MessageBox.Show(mess.Message);
            trans.Rollback();
            cnn.Close();
        }

Note the "trans.Commit()" before you close the connection.

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

---

Related Topics