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 protected]"))
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.