Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-04-08
9
Medium Priority
?
1,585 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 100 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 100 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
Looking for a new Web Host?

Lunarpages' assortment of hosting products and solutions ensure a perfect fit for anyone looking to get their vision or products to market. Our award winning customer support and 30-day money back guarantee show the pride we take in being the industry's premier MSP.

 
LVL 21

Assisted Solution

by:mastoo
mastoo earned 600 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 1200 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 600 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

If I have to fix slow responding website my first thoughts are server side optimizations: the database may not be optimized or caching is not enabled, or things like that. We often overlook another major part of our web application: the client. We o…
Introduction A frequently used term in Object-Oriented design is "SOLID" which is a mnemonic acronym that covers five principles of OO design.  These principles do not stand alone; there is interplay among them.  And they are not laws, merely princ…
This video teaches users how to migrate an existing Wordpress website to a new domain.
Wufoo.com provides powerful tools for surveying targeted groups, and utilizing data from completed surveys to find trends, discover areas of demand or customer expectation, and make business decisions on products or services.

721 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