none
Return -1 from ExecuteNonquery RRS feed

  • Question

  • I have a stored procedure that is always returning -1 when I invoke it with ExecuteNonQuery. I read about this ADO.NET method and it says that it will return -1 for all but INSERT, UPDATE, and DELETE. Can the fact that I have an IF statement in the stored procedure be affecting the output? How do I fix it? Here is the body of the stored procedure:

    	SET NOCOUNT ON;
    	IF @LineItemCreditId = 0
        BEGIN
           INSERT INTO LineItemCredits ([PaymentId],
                                        [LineItemId],
                                        [OrderGroupId],
                                        [OrderSource],
                                        [CreditCost],
                                        [CreditTax],
                                        [CreditShipping],
                                        [CreditHandling],
                                        [Processed])
           VALUES(@PaymentId,
                  @LineItemId,
                  @OrderGroupId,
                  @OrderSource,
                  @CreditCost,
                  @CreditTax,
                  @CreditShipping,
                  @CreditHandling,
                  @Processed)
        END
        ELSE
        BEGIN
    		UPDATE LineItemCredits
    			SET [PaymentId] = @PaymentId,
    				[LineItemId] = @LineItemId,
    				[OrderGroupId] = @OrderGroupId,
    				[OrderSource] = @OrderSource,
    				[CreditCost] = @CreditCost,
    				[CreditTax] = @CreditTax,
    				[CreditShipping] = @CreditShipping,
    				[CreditHandling] = @CreditHandling,
    				[Processed] = @Processed,
    				[DateCreated] = @DateCreated
    		WHERE [LineItemCreditId] = @LineItemCreditId
    		IF @@rowcount = 0
    		BEGIN
    			INSERT INTO LineItemCredits ([PaymentId],
    										 [LineItemId],
    									     [OrderGroupId],
                                             [OrderSource],
                                             [CreditCost],
                                             [CreditTax],
                                             [CreditShipping],
                                             [CreditHandling],
                                             [Processed])
    			VALUES(@PaymentId,
                       @LineItemId,
                       @OrderGroupId,
                       @OrderSource,
                       @CreditCost,
                       @CreditTax,
                       @CreditShipping,
                       @CreditHandling,
                       @Processed)
    	   END
    	END
    

    So you see that it always either executes an INSERT or UPDATE. In this case I am passing 

    @LineItemCreditId = 0

    so I know I am executing an INSERT. Ideas?


    Kevin Burton

    Wednesday, September 19, 2012 6:14 AM

Answers

  • Hi,

    executenonquery returns the number of affected rows if it concerns insert select and update.

    I quess it makes use of the 'count' infomessage data that is returned after the query. 

    A stored proc with

    set nocount on

    will not return this message, and i'm not sure if it would work even if you left out 'set nocount on'.

    The only sure way to get that number is to create an 'out' parameter, and return the rowsaffected number to that parameter.

    You could also try to return it as the return value of the stored proc (which is actually also an OUT parameter )

    See this thread: http://stackoverflow.com/questions/3309213/getting-return-value-from-stored-procedure-in-ado-net


    Regards, Nico

    Wednesday, September 19, 2012 7:01 AM