Blog

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

Posted by Nitesh Luharuka Blogs | SQL Dec 19, 2012
This post details another solution to a very basic problem that we face in our development and tend to miss writing Stored Procedures.

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", "email@email.com"))
        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. 

COMMENT USING
PREMIUM SPONSORS
Hire Mobile & Web Developer on demand. 100% satisfaction. Try for 1 week or Money Back. Local and remote developers available all over USA.
Employers - Post Free Jobs
SPONSORED BY
  • MCN is your source for developing solutions involving websites, mobile apps, cloud-computing, databases, BI, back-end services and processes and client-server applications.