SQL ExecuteNonQuery();

Although my records are successfully INSERTed ExecuteNonQuery(); returns a "-1"  according to my reading of the doc SqlCommand.ExecuteNonQuery Method ()

It should return a 1 or a 2 as 1 row is inserted into two tables??

This is a problem because if I do not get something > 0 it should be an error??

Regards
Sam

CodeBehind Utility I inherited:
internal static bool ExecSproc(string ConnectStringName, string ProcedureName, string[,] Parameters, string RequestingPage)
        {
            SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectStringName].ToString());
            SqlCommand SqlCmd = new SqlCommand(ProcedureName, Conn);
            SqlCmd.CommandType = CommandType.StoredProcedure;

            for (int i = 0; i < Parameters.GetLength(0); i++)
            {
                SqlParameter P = new SqlParameter();
                P.ParameterName = Parameters[i, 0];
                if (Parameters[i, 1] == "")
                {
                    P.Value = DBNull.Value;
                }
                else
                {
                    P.Value = Parameters[i, 1];
                }
                SqlCmd.Parameters.Add(P);
            }
            try
            {
                Conn.Open();     
                int OK = SqlCmd.ExecuteNonQuery();
                Conn.Close();

                if (OK > 0) 
                { 
                    Conn.Dispose(); return true; 
                }
                else
                {
                    Conn.Dispose(); SqlCmd.Dispose();
                    //Exception Ex = new Exception("Error = SQL Server was unable to perform requested operation");
                    //Error_Logging.LogError(Ex, RequestingPage, true);
                    //throw Ex;
                    return false;
                }
            }

Open in new window


Sproc:
CREATE PROCEDURE [dbo].[MessageAdd] 
	-- Add the parameters for the stored procedure here
	@Subject nvarchar(128)
	, @URL nvarchar(255)
	, @UID int
	, @ExpireDate DateTimeOffset(7)
	, @DeleteDate DateTimeOffset(7)
	, @Publish bit
	, @TextType tinyint
	, @DistributionEID int
	, @MessageBody nvarchar(4000)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	-- passed returned record ID ContactID to use in susequent inserts
	DECLARE @IdentityValue TABLE(
		MessageID int
		);

    -- Insert statements for procedure here
	INSERT INTO dbo.MessageHead (Subject, URL, CreatedBY, ExpireDate, DeleteDate, Publish, TextType, DistributionEID)
	OUTPUT INSERTED.MessageID INTO @IdentityValue
	VALUES (@Subject, @URL, @UID, @ExpireDate, @DeleteDate, @Publish, @TextType, @DistributionEID)
	
	
	INSERT INTO dbo.Messages(MessageID, MessageBody)
	SELECT MessageID, @MessageBody
	FROM @IdentityValue
	
END

Open in new window

SamCashAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Hi  SamCash;

From the documentation posted below what you stated is true if the SQL statement is either a UPDATE, INSERT and DELETE. You are using a Stored Procedure which it is not one of the three and therefore the documentation states, "For all other types of statements, the return value is -1".

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.
0
Snarf0001Commented:
Sorry, have to disagree.  Doing an insert statement from a proc will return the rowcount affected, if the last statement executed was an insert, update or delete.

In the above case, you would receive only the rows insert into dbo.Messages as that's the last line in the proc.

The issue is you're specifically setting nocount on in the first line of the proc, which directly tells sql not to monitor the rows affected.
Remove that line and you should get the count back.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SamCashAuthor Commented:
Fernando,

No "sorry" necessary, I thank you.  I never knew what SET NOCOUNT ON; was doing, it is in the template MSSMS provides for >New Storred Procedure. And now I realize it should only be ON for SELECT statements...  After removing NOCOUNT I actually get a count of "2" so it seems to be counting both?  Is the rowcount > 0 a good way to check for errors, or is there a better way, a Best Practice way?  I see the "SQL exceptions" all the time as I am developing code, it would seem getting a hold of them would be better.

Thanks Again
Sam
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Fernando SotoRetiredCommented:
Hi SamCash;

It was actually @Snarf0001 who provided the correct solution.
0
SamCashAuthor Commented:
Fernando,

Thanks again.

Regards
Sam
0
SamCashAuthor Commented:
Snarf0001,

No "sorry" necessary, I thank you.  I never knew what SET NOCOUNT ON; was doing, it is in the template MSSMS provides for >New Storred Procedure. And now I realize it should only be ON for SELECT statements...  After removing NOCOUNT I actually get a count of "2" so it seems to be counting both?  Is the rowcount > 0 a good way to check for errors, or is there a better way, a Best Practice way?  I see the "SQL exceptions" all the time as I am developing code, it would seem getting a hold of them would be better.

Thanks Again
Sam
0
SamCashAuthor Commented:
Snarf001,

No "sorry" necessary, I thank you.  I never knew what SET NOCOUNT ON; was doing, it is in the template MSSMS provides for >New Storred Procedure. And now I realize it should only be ON for SELECT statements...  After removing NOCOUNT I actually get a count of "2" so it seems to be counting both?  Is the rowcount > 0 a good way to check for errors, or is there a better way, a Best Practice way?  I see the "SQL exceptions" all the time as I am developing code, it would seem getting a hold of them would be better.

Thanks Again
Sam
0
Snarf0001Commented:
Hi Sam, my mistake.  It does indeed return all of the rows affected, not just on the last insert.
If you add several more inserts into different tables, all are being returned as well.

As far as errors go, you should really do both.  If an actual SQLException happens, you should handle that in your try / catch block.
In this case, the rowcount > 0 is a good catch all though.  Not knowing if you have any other triggers or items in the db that could be affecting this, there's no "real" reason you should EVER get rowcount less than (or equal to zero), unless it throw an error and bypassed your entire "OK" check.  But it's not going to hurt anything to check for that as well.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.