asked on

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

SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Brendt Hess

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ipjyo

ASKER

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;

SOLUTION

mastoo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ipjyo

ASKER

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!

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

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.

SOLUTION

mastoo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ipjyo

ASKER

I will try to do this. Please give me sometime and will let you know.
Thanks very much