Solved

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

Posted on 2009-04-08
9
1,583 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 25 total points
ID: 24101732
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
ID: 24101830
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
ID: 24101889
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 21

Assisted Solution

by:mastoo
mastoo earned 150 total points
ID: 24102778
"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
 

Author Comment

by:ipjyo
ID: 24102835
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
ID: 24103180
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
ID: 24103200
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
ID: 24103224
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
ID: 24103272
I will try to do this. Please give me sometime and will let you know.
Thanks very much
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Thoughout my experience working on eCommerce web applications I have seen applications succumbing to increased user demand and throughput. With increased loads the response times started to spike, which leads to user frustration and lost sales. I ha…
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 PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

751 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