存储过程在 SQL Server 和 C# 中具有太多参数，但在第二个条目中

Question

当我将列表的值插入数据库时，我遇到了这个问题。

起初，它

运行良好，但是当第二次运行时，它会显示错误。我知道许多错误已经在这里得到解决。

但就我的情况而言，它发生在第二个条目中，第一个条目工作得很好。

这是存储过程：

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

那么这是 C# 代码：

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();
    }
}

感谢您的任何帮助。

Answer 1

我认为您没有清除参数。您应该在交易后始终清除它。

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

并像@cha所说的那样搜索有关AddWithValue与Add的信息，请检查您的参数类型。看到这里。