Stored procedure has too many arguments in SQL Server and C# but on second entry

Question

I got this problem that I got when I insert value of a list into the database.

At first, it works perfectly, but when being run a second time, it shows the error. I knew that many of this error has been solved in here.

But for my condition it happened at the second entry where the first one works perfectly.

This is the stored procedure:

ALTER PROCEDURE [dbo].[InsertMonthlyIncome]
    @UserId INT,
    @MonthlyIncomeName VARCHAR(300),
    @MonthlyIncomeAmount FLOAT,
    @TotalMonthlyIncome FLOAT,
    @Month VARCHAR(30),
    @Year INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @InfoId INT, 
            @InfoTotalIncome FLOAT, @InfoTotalExpense FLOAT,
            @InfoMonth VARCHAR(30), @InfoYear INT, @InfoUserId INT

    SELECT 
        @InfoId = MFInfoId, @InfoTotalIncome = MFInfoTotalIncome, 
        @InfoTotalExpense = MFInfoTotalExpense, 
        @InfoMonth = MFInfoMonth, @InfoYear = MFInfoYear,
        @InfoUserId = MFUserId
    FROM 
        MonthlyFinancialInformation 
    WHERE 
        MFInfoMonth = @Month 
        AND MFInfoYear = @Year 
        AND MFUserId = @UserId

    IF @InfoId IS NOT NULL
    BEGIN
        UPDATE MonthlyFinancialInformation
        SET MFInfoTotalIncome = (@TotalMonthlyIncome + MFInfoTotalIncome)
        WHERE MFInfoId = @InfoId
    END      
    ELSE
    BEGIN
        INSERT INTO MonthlyFinancialInformation(MFInfoTotalIncome, MFInfoMonth, MFInfoYear, MFUserId)
        VALUES (@TotalMonthlyIncome, @Month, @Year, @UserId)
    END

    INSERT INTO MonthlyCertainIncome(MCIncomeName, MCIncomeAmount, MCIncomeDateCreated, MCUserId)
    VALUES (@MonthlyIncomeName, @MonthlyIncomeAmount, GETDATE(), @UserId)
END

Then this is the C# code:

using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["UangBulananComConnectionString"].ConnectionString))
{
    con.Open();

    using (SqlCommand cmd = new SqlCommand("InsertMonthlyIncome", con))
    {
        foreach (var income in monthlyIncomeList)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@UserId", userId);
            cmd.Parameters.AddWithValue("@MonthlyIncomeName", income.MonthIncomeName);
            cmd.Parameters.AddWithValue("@MonthlyIncomeAmount", income.MonthIncomeAmount);
            cmd.Parameters.AddWithValue("@TotalMonthlyIncome", income.MonthIncomeAmount);
            cmd.Parameters.AddWithValue("@Month", insertMonth);
            cmd.Parameters.AddWithValue("@Year", insertYear);

            cmd.ExecuteNonQuery();
        }

        con.Close();
        monthlyIncomeList.Clear();
    }
}

Thanks for any help.

I suspect some of your parameter types do not match the SP's types. Can you tell us what C# types are used with all your `cmd.Parameters.AddWithValue` calls? — cha, Apr 06 '18 at 00:16
Since you're looping over a list of values to insert, you should **create** the parameters **outside** the loop (before it starts), and then only set the **values** of the parameters inside the loop to execute the stored procedure once for each value in your list. Right now, you're **creating** the parameters for each of the values in your list - and that'll work the first time, but on the second value, those parameters **already exist** in your `SqlCommand` object - so you **CANNOT** re-create them again and again — marc_s, Apr 06 '18 at 04:24

TraxX · Accepted Answer · 2018-04-06T00:29:37.650

0

I think you didn't clear the parameters. You should clear it always after your transaction.

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@UserId", userId);
cmd.Parameters.AddWithValue("@MonthlyIncomeName", income.MonthIncomeName);
cmd.Parameters.AddWithValue("@MonthlyIncomeAmount", income.MonthIncomeAmount);
cmd.Parameters.AddWithValue("@TotalMonthlyIncome", income.MonthIncomeAmount);
cmd.Parameters.AddWithValue("@Month", insertMonth);
cmd.Parameters.AddWithValue("@Year", insertYear);
cmd.ExecuteNonQuery();

cmd.Parameters.Clear(); // insert this line

And search about AddWithValue vs Add like @cha said that check your parameter types. See this.

edited Apr 06 '18 at 00:29

answered Apr 06 '18 at 00:22

TraxX

382
2
13

1

That *might work* - but if it does, it will be **highly inefficient**, since you keep creating and re-creating the same parameters over and over again, for every value in the list to be inserted - but the parameters as such do not change - the same and datatype and everything remains the same. The only thing changing are the **values** that get assigned to those parameters. Those parameters should be created **once, before the loop** and then inside the loop, only assign the values to use – marc_s Apr 06 '18 at 04:26

Stored procedure has too many arguments in SQL Server and C# but on second entry

1 Answers1