过程只更新第一个值

Question

我有这个SQL过程用于更新给定@StepId值的记录。我以数组的形式从隐藏字段中获取值，然后循环它们。问题是这个过程只更新第一个值。循环发生，但不更新超过一个值。我的数组包含像{1000,2000,3000，}这样的东西我的参数是 @StepId (int)@DateCalculationRule(字符)@Result (int)

Private Sub buttonCalculateDatesClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles buttonCalculateDates.Click
        Dim mySteps As String
        Dim myRule As String
        Dim ok As Integer = 1
        Dim connectionString As String = WebConfigurationManager.ConnectionStrings("cnnstring").ConnectionString
        Dim conn As SqlConnection = New SqlConnection(connectionString)
        Dim cmd As New SqlCommand("SaveStepDeadlineRule", conn)
        conn.Open()
        conn.CreateCommand()
        cmd.CommandType = CommandType.StoredProcedure
        myRule = HiddRule.Value
        mySteps = HiddStepIDs.Value
        'Separate string by comas
        Dim parts As String() = mySteps.Split(New Char() {","c})
        Dim part As String
        For Each part In parts
            cmd.Parameters.Add(New SqlParameter("@StepId", part))
            cmd.Parameters.Add(New SqlParameter("@DateCalculationRule", myRule))
            cmd.Parameters.Add(New SqlParameter("@Result", 0))
            cmd.Parameters("@Result").Direction = ParameterDirection.Output
            Try
                cmd.ExecuteNonQuery()
                ok = IIf(IsDBNull(cmd.Parameters("@Result").Value), 1, cmd.Parameters("@Result").Value)
                RadGrid1.Rebind()

            Catch ex As Exception
                ok = 1
            End Try
        Next
        conn.Close()
    End Sub

Answer 1

你需要在每次循环时清除你的参数:

cmd.Parameters.Clear

它的用法如下:

For Each part In parts
    cmd.Parameters.Clear()
    cmd.Parameters.Add(New SqlParameter("@StepId", part))
    cmd.Parameters.Add(New SqlParameter("@DateCalculationRule", myRule))
    cmd.Parameters.Add(New SqlParameter("@Result", 0))
    cmd.Parameters("@Result").Direction = ParameterDirection.Output
    Try
        cmd.ExecuteNonQuery()
        ok = IIf(IsDBNull(cmd.Parameters("@Result").Value), 1, cmd.Parameters("@Result").Value)
        RadGrid1.Rebind()
    Catch ex As Exception
        ok = 1
    End Try
Next

你可以在循环之外创建参数，因为它们不会改变，然后在每次循环时赋值:

cmd.Parameters.Add(New SqlParameter("@StepId", 0))
cmd.Parameters.Add(New SqlParameter("@DateCalculationRule", ""))
cmd.Parameters.Add(New SqlParameter("@Result", 0))
cmd.Parameters("@Result").Direction = ParameterDirection.Output
For Each part In parts
    cmd.Parameters("@StepId").Value = part
    cmd.Parameters("@DateCalculationRule").Value = myRule
    Try
        cmd.ExecuteNonQuery()
        ok = IIf(IsDBNull(cmd.Parameters("@Result").Value), 1, cmd.Parameters("@Result").Value)
        RadGrid1.Rebind()
    Catch ex As Exception
        ok = 1
    End Try
Next