Solved

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

Posted on 2009-04-08
9
1,584 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:Brendt Hess
Brendt Hess 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

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

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video teaches users how to migrate an existing Wordpress website to a new domain.
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…

617 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