Click here to Skip to main content
13,591,301 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


5 bookmarked
Posted 19 Dec 2012
Licenced CPOL

Solution: ExecuteNonQuery() Always Returning -1 When Calling a Stored Procedure

, 19 Dec 2012
Rate this:
Please Sign up or sign in to vote.
A solution for 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
    conn = New SqlConnection(connectionString)
    If passport Is Nothing Then
          cmd = New SqlCommand("sp_Test_Insert", conn)
    End If
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add(New SqlParameter("Email", ""))
    cmd.Parameters.Add(New SqlParameter("Password", "password"))
    Dim rows = cmd.ExecuteNonQuery()
   If rows > -1 Then
    Return True
    Return False
   End If
Catch ex As Exception
   Return False
   If Not conn Is Nothing Then
   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)
   INSERT INTO Test_Table(Email, Password) VALUES(@Email, @Password)

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.

Hope you enjoyed reading this. Cheers!


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Nitesh Luharuka
Team Leader Director @ Rebin Infotech
India India
An Independent IT Professional and Consultant on MS Technologies like .Net | MVC | Silverlight | Sharepoint | MS Project Server along with open source CMS Systems like DotNetNuke etc.

I am an enthusiastic technocrat who love to debug problems and solve them. I love writing articles on my website in my spare time. Please visit my Website for more details and subscribe to get technology related tips/tricks.

You may also be interested in...


Comments and Discussions

Questionthanks Pin
aleroy24-May-13 0:26
memberaleroy24-May-13 0:26 
AnswerRe: thanks Pin
Nitesh Luharuka24-May-13 5:35
memberNitesh Luharuka24-May-13 5:35 
GeneralMy vote of 4 Pin
Savalia Manoj M21-Jan-13 2:49
memberSavalia Manoj M21-Jan-13 2:49 
GeneralMy vote of 5 Pin
VitorHugoGarcia8-Jan-13 23:30
memberVitorHugoGarcia8-Jan-13 23:30 
GeneralRe: My vote of 5 Pin
Nitesh Luharuka24-May-13 5:33
memberNitesh Luharuka24-May-13 5:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web04-2016 | 2.8.180618.1 | Last Updated 19 Dec 2012
Article Copyright 2012 by Nitesh Luharuka
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid