problem to delete more then one row using datagridview checkbox

Question

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

Hans Olsson · Accepted Answer · 2010-12-17 09:07:23Z

1

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

answered Dec 17, 2010 at 9:07

Hans Olsson

55.1k16 gold badges99 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hans Olsson Over a year ago

@Gulu: It's because rather than appending strings together I'm using String.Format, and {0} signifies that the second parameter to String.Format should be inserted there, and if there was a {1} the third parameter should go there etc. See here for more details: msdn.microsoft.com/en-us/library/system.string.format.aspx

Collectives™ on Stack Overflow

problem to delete more then one row using datagridview checkbox

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