Carlos Martins Carlos Martins - 1 month ago 6
Vb.net Question

Procedure only updates the first value

I have this SQL procedure for updating records given a @StepId value.
I got the values from a hidden field as an array and I loop them.
The problem is that the procedure only updates the first value.
The loop happen but do not update more than 1 value.
my array contains something like {1000,2000,3000,}
my parameters are
@StepId (int)
@DateCalculationRule (char)
@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

You will probably need to clear down your parameters each time you loop. 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

Without setting this up on my dev system I'm unsure if it will work or not but you could create the parameters outside the loop (since they don't change) and then just assign the value each time you loop:

    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

Or to something to this effect anyway.

Comments