ExecuteNonQuery to count number of rows?

My understanding from a previous thread was that ExecuteNonQuery() could be used to display the number of rows returned.

Does this also work when calling stored procedures and passing parameters?

I have code (shown) that perfectly calls and returns Distinct models downloaded by Country. Yet the rowCount variable displays a -1.

What should I do?

Dim myCommandAs New SqlClient.SqlCommandmyCommand.CommandText ="ap_Select_ModelRequests_RequestDateTime"myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.AddWithValue("@.selectDate", dateEntered)myCommand.Parameters.AddWithValue("@.selectCountry",CInt(selectCountry))myCommand.Connection = concon.Open()Dim rowCountAs Integer = myCommand.ExecuteNonQuery()numberParts.Text = rowCount.ToStringcon.Close()

Thank you.

Check yourap_Select_ModelRequests_RequestDateTimestored procedure, and if you find:

SET NOCOUNT OFF

useually at the end of SP, remove it and I hope this will work.

Good luck.

|||

Hi SolitaryMan,

Normally one would use ExecuteReader for SELECT statements, which you seem to be doing, and call sqlDataReader.RecordsAffected to get the number of rows affected.

Getting -1 from ExecuteNonQuery for a SELECT statement is documented behavior:

"For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1." --http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(VS.71).aspx

As for passing parameters to a stored proceudre with ExecuteNonQuery, that should not affect the return value. ExecuteNonQuery does indeed return the number of rows affected, just not for SELECT statements for example.

Here's some sample code for using SqlDataReader to get the number of rows affected from a SELECT Statement:

SqlCommand selectStatement =newSqlCommand();

selectStatement.CommandText ="PROC_SELECT_ITEM";

selectStatement.CommandType =CommandType.StoredProcedure;

selectStatement.Connection = connection;

SqlDataReader reader = selectStatement.ExecuteReader();

int rowsAffected = reader.RecordsAffected;

Pete

|||

You can use @.@.ROWCOUNT in your stored procedure and assign it to an output parameter.

|||

Peter Lee:

Hi SolitaryMan,

Normally one would use ExecuteReader for SELECT statements, which you seem to be doing, and call sqlDataReader.RecordsAffected to get the number of rows affected.

Getting -1 from ExecuteNonQuery for a SELECT statement is documented behavior:

"For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1." --http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery(VS.71).aspx

As for passing parameters to a stored proceudre with ExecuteNonQuery, that should not affect the return value. ExecuteNonQuery does indeed return the number of rows affected, just not for SELECT statements for example.

Here's some sample code for using SqlDataReader to get the number of rows affected from a SELECT Statement:

SqlCommand selectStatement =newSqlCommand();

selectStatement.CommandText ="PROC_SELECT_ITEM";

selectStatement.CommandType =CommandType.StoredProcedure;

selectStatement.Connection = connection;

SqlDataReader reader = selectStatement.ExecuteReader();

int rowsAffected = reader.RecordsAffected;

Pete

Hi Pete.

What you wrote is correct, but I do not think it will work if the stored procedure has a SET NOCOUNT OFF?

Thanks.

|||

Hi CS4Ever,

Thanks. You are right, you cannot have the SET NOCOUNT in the stored procedure.

In any case, you do not typically use ExecuteNonQuery to execute a SELECT statement, especially if you want to know how many rows are retrieved.

So both suggestions are indeed valid, with the resulting suggestion being to use an ExecuteReader with a stored procedure without the SET NOCOUNT statement.

Pete

|||

Peter Lee:

Hi CS4Ever,

Thanks. You are right, you cannot have the SET NOCOUNT in the stored procedure.

In any case, you do not typically use ExecuteNonQuery to execute a SELECT statement, especially if you want to know how many rows are retrieved.

So both suggestions are indeed valid, with the resulting suggestion being to use an ExecuteReader with a stored procedure without the SET NOCOUNT statement.

Pete

You are right.

Thanks Pete.