ExecuteNonQuery() not giving correct affected rows

When I use ExecuteNonQuery() with the stored procedure below it returns -1. However, when i tried to get rid of the if/else statements and just leave one insert statement for testing purposes, ExecuteNonQuery() returns the correct affected rows which is 1. So it seems like ExecuteNonQuery() doesn't work when the INSERT statement is inside the IF..ELSE. Can anybody help me with this problem? I haven't tried using @.@.RowCount because I really want to use ExecuteNonQuery() to do this because I don't want to rewrite my DAL. Thanks in advance

-- With if/else ExecuteNonQuery returns -1

ALTER PROCEDURE [dbo].[SP_AddObjectContribution]
@.ObjectId int,
@.FanId int,
@.DateContributed DateTime,
@.Notes nvarchar(512),
@.ObjectType int
AS

BEGIN

BEGIN TRAN
IF @.ObjectType = 2
BEGIN
INSERT INTO FighterContributions
(FighterId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 3
BEGIN
INSERT INTO FighterPhotoContributions
(FighterPhotoId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 4
BEGIN
INSERT INTO OrganizationContributions
(OrganizationId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 5
BEGIN
INSERT INTO EventContributions
(EventId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 6
BEGIN
INSERT INTO FightContributions
(FightId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 7
BEGIN
INSERT INTO FightPhotoContributions
(FightPhotoId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END

IF @.@.ERROR <> 0
BEGIN
ROLLBACK RETURN
END

COMMIT TRAN

END

-- Without if/else ExecuteNonQuery returns 1

ALTER PROCEDURE [dbo].[SP_AddObjectContribution]
@.ObjectId int,
@.FanId int,
@.DateContributed DateTime,
@.Notes nvarchar(512),
@.ObjectType int
AS

BEGIN

BEGIN TRAN

INSERT INTO FighterContributions
(FighterId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)

IF @.@.ERROR <> 0
BEGIN
ROLLBACK RETURN
END

COMMIT TRAN

END

1ALTER PROCEDURE [dbo].[SP_AddObjectContribution]2 @.ObjectIdint,3 @.FanIdint,4 @.DateContributedDateTime,5 @.Notesnvarchar(512),6 @.ObjectTypeint7AS89BEGIN1011 BEGIN TRAN12 IF @.ObjectType = 213BEGIN14 INSERT INTO FighterContributions15 (FighterId, FanId, DateContributed, Notes)VALUES16 (@.ObjectId, @.FanId, @.DateContributed, @.Notes)17RETURN@.@.ROWCOUNT18END1920END212223

Try each statement like this

|||

RETURN @.@. ROWCOUNT on each statement won't work because it won't commit the transaction (it won't hit COMMIT TRAN).

|||

Perhaps you can add an OUTPUT Parameter to get the rows affected. Instead of this statement: IF @.@.ERROR <> 0, try:

SELECT @.rows = @.@.ROWCOUNT, @.Error = @.@.ERROr

IF @.ERROR <> 0

You will have to declare the @.Rows and @.Error variables. Add @.Rows to the parameters list as OUTPUT param. Check the value in @.rows from your front end.

|||

I think only one insert statment will execute at one time based on parameter.. don;t kwno why u are using transaction for single insert. ? Is this correct

|||

I'm sorry guys. I think I messed up while testing the method because right now the stored procedure in question is now working. I also added RETURN SCOPE_IDENTITY() to get the ID of the new inserted record and it works. So having INSERT statements inside IF ELSE is not a problem for executenonquery.

So this is the final procedure

ALTER PROCEDURE [dbo].[SP_AddObjectContribution]
@.ObjectId int,
@.FanId int,
@.DateContributed DateTime,
@.Notes nvarchar(512),
@.ObjectType int
AS
BEGIN
BEGIN TRAN
IF @.ObjectType = 2
BEGIN
INSERT INTO FighterContributions
(FighterId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 3
BEGIN
INSERT INTO FighterPhotoContributions
(FighterPhotoId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 4
BEGIN
INSERT INTO OrganizationContributions
(OrganizationId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 5
BEGIN
INSERT INTO EventContributions
(EventId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 6
BEGIN
INSERT INTO FightContributions
(FightId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END
ELSE IF @.ObjectType = 7
BEGIN
INSERT INTO FightPhotoContributions
(FightPhotoId, FanId, DateContributed, Notes) VALUES
(@.ObjectId, @.FanId, @.DateContributed, @.Notes)
END

IF @.@.ERROR <> 0
BEGIN
ROLLBACK RETURN
END

COMMIT TRAN

RETURN SCOPE_IDENTITY()

END

For those who are interested, here is how to get the value of the new id. Ignore CreateParameter() method. It is just an abstraction.

IDataParameter param = CreateParameter("ReturnValue", DbType.Int32);
param.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(param);
connection.Open();
if (command.ExecuteNonQuery() > 0)
{
newObjectId = (int)((IDataParameter)command.Parameters["ReturnValue"]).Value;
}

|||

satya_tanwar:

I think only one insert statment will execute at one time based on parameter.. don;t kwno why u are using transaction for single insert. ? Is this correct

Actually, I am going to add delete statements before the insert statements later. Anyways, thanks for helping out.

|||

thats good mark the post as answered and close the post