Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1586
  • Last Modified:

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

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
ipjyo
Asked:
ipjyo
  • 3
  • 2
  • 2
  • +2
5 Solutions
 
BrandonGalderisiCommented:
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
 
Brendt HessSenior DBACommented:
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
 
ipjyoAuthor Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mastooCommented:
"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
 
ipjyoAuthor Commented:
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
 
Anthony PerkinsCommented:
Quite simply, you are getting -1 because you are using:
SET NOCOUNT ON

-1 means the property is not supported.
0
 
Anthony PerkinsCommented:
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
 
mastooCommented:
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
 
ipjyoAuthor Commented:
I will try to do this. Please give me sometime and will let you know.
Thanks very much
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now