Solution - ExecuteNonQuery() always returning -1 when calling a Stored Procedure

This post details another solution to a very basic problem that we face in our development and tend to miss writing Stored Procedures. We know ExecuteNonQuery() function defined in the SqlCommand class returns the number of rows affected by the query we are executing. Sometimes, when calling a Stored Procedure using the ExecuteNonquery() from .Net code returns a value of -1.

Let us consider this function that tries inserting Email address and Password fields to the database via a test SP.

Public Function TestInsert() As Boolean
    Dim conn As SqlConnection = Nothing
    Dim cmd As SqlCommand = Nothing
    Try
        conn = New SqlConnection(connectionString)
        conn.Open()
        If passport Is Nothing Then
            cmd = New SqlCommand("sp_Test_Insert", conn)
        End If
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter("Email", "[e-mail]"))
        cmd.Parameters.Add(New SqlParameter("Password", "password"))
        Dim rows = cmd.ExecuteNonQuery()
   If rows > -1 Then
            Return True
        Else
            Return False
        End If
    Catch ex As Exception
        Return False
    Finally
        If Not conn Is Nothing Then
            conn.Close()
        End If
        conn = Nothing
    End Try
End Function

When you generate a Stored Procedure in SSMS using the “New Stored Procedure” link, SQL generates a template for you. Below is a modified version of the same being used in our code above:

CREATE PROC sp_Test_Insert
@Email nvarchar(255),
@Password nvarchar(20)
AS
BEGIN
   SET NOCOUNT ON;
   INSERT INTO Test_Table(Email, Password) VALUES(@Email, @Password)
END

The query looks fine and after doing a bit of research, I found that the 1st line in the Stored procedure is the culprit. From MSDN, placing SET NOCOUNT ON;  in the query sets, prevent extra result sets from SQL server interfering with SELECT statements.

To resolve, just remove this line or change this line to SET NOCOUNT OFF; and everything works fine.