Executing update query inside loop

Question

   cmd = New SqlCommand("select enrollment,total_fee,discount,net_fee from stu_dtl", openConnection())
        '  dr = cmd.ExecuteReader
        adpt = New SqlDataAdapter(cmd)
        adpt.Fill(ds, "stu_dtl")
        dt = ds.Tables("stu_dtl")
 For i = 0 To dt.Rows.Count - 1
              cmd = New SqlCommand("update stu_dtl set net_fee = '" & (Val(dt.Rows(i).Item("total_fee")) - Val(dt.Rows(i).Item("discount"))) & "' where enrollment = '" & dt.Rows(i).Item("enrollment") & "'", openConnection())
            cmd.ExecuteNonQuery()
        Next

when I execute this code for more than 150 records "Nothing happens"......what am i doing wrong??is there any other way to update??

Unless I'm missing something, you don't need any of that code. Just creating a SQL update query and doing an ExecuteNonQuery would do the same thing — peterG
– peterG, Commented Dec 23, 2013 at 0:13

Bjørn-Roger Kringsjå · Accepted Answer · 2013-12-22 13:01:15Z

I'm not sure what you are doing wrong. But try this code. If an error occur it ensure rollback of the database. Note that I assume that the datatype of the net_fee and enrollment columns are Integer.

Using connection As SqlConnection = New SqlConnection("TODO: Set connection string.")

    Dim table As DataTable = New DataTable("stu_dtl")
    Dim [error] As Exception = Nothing

    Using command As SqlCommand = connection.CreateCommand()
        command.CommandText = "SELECT [enrollment], [total_fee], [discount], [net_fee] FROM [stu_dtl];"
        Using adapter As New SqlDataAdapter(command)
            adapter.Fill(table)
        End Using
    End Using

    Using transaction As SqlTransaction = connection.BeginTransaction()
        Try

            Dim net_fee As Integer = 0
            Dim enrollment As Integer = 0

            For Each row As DataRow In table.Rows

                net_fee = (CInt(row.Item("total_fee")) - CInt(row.Item("discount")))
                enrollment = CInt(row.Item("enrollment"))

                Using command As SqlCommand = connection.CreateCommand()
                    command.CommandText = "UPDATE [stu_dtl] SET [net_fee] = @net_fee WHERE [enrollment] = @enrollment;"
                    command.Parameters.AddWithValue("@net_fee", net_fee)
                    command.Parameters.AddWithValue("@enrollment", enrollment)
                    command.ExecuteNonQuery()
                End Using

            Next

            transaction.Commit()

        Catch ex As Exception
            [error] = ex
            transaction.Rollback()
        End Try
    End Using

    If (Not table Is Nothing) Then
        table.Dispose()
        table = Nothing
    End If

    If (Not [error] Is Nothing) Then
        Throw [error]
    End If

End Using

Edit

Come to think of it, you might want to change the net_fee column to a computed column. The formula would simply be ([total_fee] - [discount]).

computed column is certainly a better option....still couldn't figure out why the query didn't work. anyways thanks for help

Collectives™ on Stack Overflow

Executing update query inside loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related