CODEWITHSUNDEEP
vb.net
Neel Desai
Neel Desai

Reputation: 459

problem to delete more then one row using datagridview checkbox

i used v s 2010 i have one problem..when i delete more then one rows from backend database using datagridview's checkbox....when i delete continus more then one row it maintain a my field NO in backend.......

but suppose when i delete a row 2 and 4 then.......it can not maintain a backend..... my programming is that *When i delete a row then all then getherthan of that row s No field Number is like n=n-1 *

my code is here....can any one solve it.....

Dim i, j, n As Integer
i = 0
j = 0
Dim x As Integer = 0
Dim a(1000) As String
a(x) = ""
Try
    While i < DataGridView1.Rows.Count
         If DataGridView1.Rows(i).Cells(0).Value() Then
              n = DataGridView1.Rows(i).Cells(1).Value()
              Call openconnection()
              'Here It Will Delete your Row From The Database Depending On Your Id....
              str = "DELETE FROM Assets_Detail Where   No=" & n & ""
              cmd = New SqlCommand(str, cn)
              cmd.ExecuteNonQuery()
              a(x) = n.ToString

              cn.Close()
              x = x + 1



         End If
         i = i + 1
    End While
Catch ex As Exception
    MsgBox(ex.ToString())
End Try
While j <= Val(a(x))
    Dim temp As Integer
    temp = Val(a(x))
    Call openconnection()
    str = "update Assets_Detail set No=No-1 where No > " & temp & ""
    cmd = New SqlCommand(str, cn)
    cmd.ExecuteNonQuery()
    cn.Close()

    j = j + 1
End While

Upvotes: 0

Views: 1199

Answers (1)

Hans Olsson
Hans Olsson

Reputation: 55059

The short answer is that I think you're using x when you meant to use j and/or using a value rather than the length of the array. In other words, the following code:

While j <= Val(a(x))
    Dim temp As Integer
    temp = Val(a(x))

Should possibly be 

While j <= a.Count()
    Dim temp As Integer
    temp = Val(a(j))

However, the longer answer is that I think your code is probably a bit more messy than it needs to be and makes more DB calls than it needs. One example is that you use While when you probably should be using For or For Each. The following is a suggest refactoring (though please note that I don't understand how the update Assets_Detail bit is supposed to work, so at least that bit might be quite wrong):

    Dim ids As New List(Of Integer)
    Try
        For Each row As DataRow In DataGridView1.Rows
            If row.Cells(0).Value() Then
                n = row.Cells(1).Value()                    
                ids.Add(CInt(n))
            End If
        Next

        Using cn As New SqlConnection("connectionstringhere")
            cn.Open()
            Dim Str As String = "DELETE FROM Assets_Detail Where No IN {0}"
            Using cmd As New SqlCommand(String.Format(Str, String.Join(", ", ids.ToArray())), cn)
                cmd.ExecuteNonQuery()
            End Using
        End Using

    Catch ex As Exception
        MsgBox(ex.ToString())
    End Try
    Try
        For Each id As Integer In ids
            Using cn As New SqlConnection("connectionstringhere")
                cn.Open()
                Dim str As String = "update Assets_Detail set No=No-1 where No > {0} "
                Using cmd As New SqlCommand(String.Format(str, id), cn)
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        Next
    Catch ex As Exception
        MsgBox(ex.ToString())
    End Try

Upvotes: 1

Related Questions