Solved

cmd.ExecuteNonQuery always returning -1 even if it is inserting records correctly?

Posted on 2009-04-08
9
1,579 Views
Last Modified: 2012-06-27
Hi,

I am trying to insert a record by executing cmd.ExecuteNonQuery. My code is inserting a record but always retuning -1. Could anybody please help me to solve this? I am pasting my code and stored procedure below. Thanks for the help!
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);

            SqlCommand cmd = new SqlCommand("spTest_InsertActivationpending", con);

            cmd.CommandType = CommandType.StoredProcedure;
 

            SqlParameter ParamActivationCd = new SqlParameter("@ActivationCd", SqlDbType.Char);

            ParamActivationCd.Value = ActivationCd;

            cmd.Parameters.Add(ParamActivationCd);

            SqlParameter ParamGroupNo = new SqlParameter("@GroupNumber", SqlDbType.VarChar);

            ParamGroupNo.Value = GroupNo;

            cmd.Parameters.Add(ParamGroupNo);

            SqlParameter ParamTaxId = new SqlParameter("@TaxId", SqlDbType.Char);

            ParamTaxId.Value = TaxId;

            cmd.Parameters.Add(ParamTaxId);

            SqlParameter ParamGroupNm = new SqlParameter("@GroupName", SqlDbType.VarChar);

            ParamGroupNm.Value = GroupNm;

            cmd.Parameters.Add(ParamGroupNm);

            SqlParameter ParamFirstNm = new SqlParameter("@FirstName", SqlDbType.VarChar);

            ParamFirstNm.Value = FirstNm;

            cmd.Parameters.Add(ParamFirstNm);

            SqlParameter ParamLastNm = new SqlParameter("@LastName", SqlDbType.VarChar);

            ParamLastNm.Value = LastNm;

            cmd.Parameters.Add(ParamLastNm);

            SqlParameter ParamEmail = new SqlParameter("@EmailAddress", SqlDbType.VarChar);

            ParamEmail.Value = Email;

            cmd.Parameters.Add(ParamEmail);

            SqlParameter ParamChiefAdmin = new SqlParameter("@ChiefAdmin", SqlDbType.Bit);

            ParamChiefAdmin.Value = ChiefAdmin;

            cmd.Parameters.Add(ParamChiefAdmin);
 

             con.Open();

            return cmd.ExecuteNonQuery();
 

//Stored Procedure

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go
 

ALTER procedure [dbo].[spTest_InsertActivationpending]

	@ActivationCd char(10),

	@GroupNumber	varchar(50),

	@TaxId			char(10),

	@GroupName		varchar(50),

	@FirstName		varchar(50),

	@LastName		varchar(50),

	@EmailAddress	varchar(50),

	@ChiefAdmin		bit

as

set nocount on
 

begin

	insert tb_ActivationPending 

		   (ActivationCode, GroupNumber, TaxId,

		    GroupName, FirstName, LastName, EmailAddress, ChiefAdmin, 

			CreateDate)

	values (@ActivationCd, @GroupNumber, @TaxId,	

			@GroupName, @FirstName, @LastName, @EmailAddress, @ChiefAdmin,

			Getdate())

	

end

Open in new window

0
Comment
Question by:ipjyo
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 25 total points
Comment Utility
What value is -1 coming back in.  I don't see where you are attempting to capture an identity value or anything in your SP.
0
 
LVL 32

Assisted Solution

by:bhess1
bhess1 earned 25 total points
Comment Utility
I believe that the syntax of the Return statement in VB.Net would return the value of the cmd object or the execution state of cmd.ExecuteNonQuery(), not the return value of cmd.ExecuteNonQuery().  Try the variation:

            Dim ReturnValue as integer
            ReturnValue = cmd.ExecuteNonQuery();
            return ReturnValue;

It should return the correct value for you.
0
 

Author Comment

by:ipjyo
Comment Utility
I thought cmd.ExecuteNonQuery returns 0 when success and -1 when failure. Please correct me if I am wrong.
I also tried the below code but it is still returning -1. Please tell me if I have to include anything in my stored procedure? Thanks!
int ret = cmd.ExecuteNonQuery;
return ret;
0
 
LVL 21

Assisted Solution

by:mastoo
mastoo earned 150 total points
Comment Utility
"For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1."

Aren't you executing a stored proc call so one would expect -1 as a return?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:ipjyo
Comment Utility
Hello mastoo,
I am trying for cmd.ExecuteNonQuery to tell any local variable say
"int ret" that the stored procedure is executed successfully.
Do I need to check for -1 in case of success? I got a little confused myself.
Could you please clarify.
Thanks!
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 300 total points
Comment Utility
Quite simply, you are getting -1 because you are using:
SET NOCOUNT ON

-1 means the property is not supported.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
For the record, I am not advocating you remove the line "SET NOCOUNT ON", but rather find some other alternative, for example you can return the number of rows affected as an output parameter.
0
 
LVL 21

Assisted Solution

by:mastoo
mastoo earned 150 total points
Comment Utility
We've been using the same code for years so I might be rusty in this area, but if the proc fails with any kind of sql error you get an exception in the .net code.  Put a raiserror in your proc to simulate this.
0
 

Author Comment

by:ipjyo
Comment Utility
I will try to do this. Please give me sometime and will let you know.
Thanks very much
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Using Quotation Marks in PHP This question (http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28217211.html) seems to come up a lot for developers who are new to PHP.  And it got me thinking, "How can we explain the rule…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now