none
how to Multiple deletion of DataGridView RRS feed

  • Question

  • Use a code to delete a row by selecting the database
    1- I want a code to delete a number of rows when selected
    2- I want a code to delete multiple rows if there is a checking cell for each row

    code deleting 1 row by sellect

     Try
                If DataGridView1.RowCount <= 0 Then
                    Exit Sub
                End If
                Dim I As Integer = DataGridView1.CurrentCell.RowIndex
                con.Open()
                Dim cmd = New OleDbCommand("DELETE FROM tb1 where ID_mail like " & DataGridView1.Rows(I).Cells(0).Value & " and clien_mail like '" & DataGridView1.Rows(I).Cells(1).Value.ToString & "'", con)
                cmd.ExecuteNonQuery()
                con.Close()
                DataGridView1.Rows.RemoveAt(DataGridView1.CurrentRow.Index)
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
    Friday, January 12, 2018 9:10 PM

Answers

  • Whenever you load data from a database table into a DataGridView you should use a BindingSource and DataTable. From here all operations on the data should not be at the DataGridView level but instead at the DataSource level.

    Full source in VS2017. Includes a MS-Access .accdb

    So I'm not using the same condition in the SQL WHERE clause yet the idea here is to show how to do a delete no matter what the condition. You can do a like against the selected row or rows and use the primary key is best. If you don't have a primary auto-incrementing key you need one.

    Next up, it's much easier to select many rows with a DataGridViewCheckBox column as shown below, check some rows, press the first button or to delete the current row single click on the row to remove and click the second button. What happens is we make a call to remove the row(s), if successful at the database level remove the row(s) in the DataGridView via the DataSource.

    The project has a data class, an exception base class, extension module and a form.

    The base exception class

    Public Class BaseExceptionProperties
    
        Protected mHasException As Boolean
        ''' <summary>
        ''' Indicate the last operation thrown an exception or not
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property HasException() As Boolean
            Get
                Return mHasException
            End Get
        End Property
        Protected mLastException As Exception
        ''' <summary>
        ''' Provides access to the last exception thrown
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property LastException() As Exception
            Get
                Return mLastException
            End Get
        End Property
        ''' <summary>
        ''' If you don't need the entire exception as in LastException this 
        ''' provides just the text of the exception
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property LastExceptionMessage As String
            Get
                Return mLastException.Message
            End Get
        End Property
        ''' <summary>
        ''' Indicate for return of a function if there was an exception thrown or not.
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property IsSuccessFul As Boolean
            Get
                Return Not mHasException
            End Get
        End Property
    End Class
    

    Extensions

    Public Module ExtensionMethods
        <Runtime.CompilerServices.Extension()>
        Public Function GetRowsChecked(ByVal pTable As DataTable, ByVal pColumnName As String) As List(Of DataRow)
            Return pTable.AsEnumerable.Where(Function(row) row.Field(Of Boolean)(pColumnName)).ToList
        End Function
        <DebuggerStepThrough()>
        <Runtime.CompilerServices.Extension()>
        Public Sub ExpandColumns(ByVal pDataGridView As DataGridView)
            For Each col As DataGridViewColumn In pDataGridView.Columns
                col.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            Next
        End Sub
    End Module
    

    Data class

    Imports System.Data.OleDb
    
    Public Class DataOperations
        Inherits BaseExceptionProperties
    
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
        }
    
    
        Public Function LoadCustomers() As DataTable
    
            Dim CustomersDataTable = New DataTable
            CustomersDataTable.Columns.Add(New DataColumn With {.ColumnName = "Process", .DataType = GetType(Boolean)})
    
            Try
    
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "SELECT Identifier, CompanyName, ContactTitle FROM Customers"
    
                        cn.Open()
    
                        CustomersDataTable.Load(cmd.ExecuteReader)
                        CustomersDataTable.Columns("Identifier").ColumnMapping = MappingType.Hidden
    
                        ' values are null by default
                        For Each row As DataRow In CustomersDataTable.Rows
                            row.SetField(Of Boolean)("Process", False)
                        Next
    
                    End Using
                End Using
    
    
            Catch ex As Exception
                mLastException = ex
                mHasException = True
            End Try
    
            Return CustomersDataTable
    
        End Function
        Public Function DeleteCustomers(ByVal pDataRows As List(Of DataRow)) As Boolean
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.Parameters.Add(New OleDbParameter With {.DbType = DbType.Int32, .ParameterName = "@Identifier"})
                    cmd.CommandText = "DELETE FROM Customers WHERE Identifier = @Identifier"
    
                    Try
    
                        cn.Open()
    
                        For indexer As Integer = 0 To pDataRows.Count - 1
                            cmd.Parameters("@Identifier").Value = pDataRows(indexer).Field(Of Integer)("Identifier")
                            cmd.ExecuteNonQuery()
                        Next
    
                        Return True
    
                    Catch ex As Exception
                        mLastException = ex
                        mHasException = True
                        Return False
                    End Try
    
                End Using
            End Using
        End Function
        Public Function DeleteSingleCustomer(ByVal pDataRow As DataRow) As Boolean
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
    
                    cmd.CommandText = "DELETE FROM Customers WHERE Identifier = @Identifier"
    
                    Try
    
                        cn.Open()
                        cmd.Parameters.AddWithValue("@Identifier", pDataRow.Field(Of Integer)("Identifier"))
                        cmd.ExecuteNonQuery()
    
                        Return True
    
                    Catch ex As Exception
                        mLastException = ex
                        mHasException = True
                        Return False
                    End Try
    
                End Using
            End Using
        End Function
    
    End Class
    

    Form code

    Public Class Form1
    
        Dim ops As New DataOperations
        Dim bsCustomers As New BindingSource
        ''' <summary>
        ''' Load our data
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            bsCustomers.DataSource = ops.LoadCustomers
            DataGridView1.DataSource = bsCustomers
            DataGridView1.Columns("Process").Width = 30
            DataGridView1.Columns("Process").HeaderText = ""
            DataGridView1.Columns("CompanyName").HeaderText = "Name"
            DataGridView1.Columns("ContactTitle").HeaderText = "Title"
            DataGridView1.ExpandColumns
        End Sub
        ''' <summary>
        ''' Get selected rows via the DataGridViewCheckBoxColumn
        ''' pass the rows to the data class, if the rows are removed
        ''' then remove the rows from the DataTable
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub cmdDeleteSelected_Click(sender As Object, e As EventArgs) Handles cmdDeleteSelected.Click
    
            Dim dt As DataTable = CType(bsCustomers.DataSource, DataTable)
            Dim rows As List(Of DataRow) = dt.GetRowsChecked("Process")
    
            If rows.Count > 0 Then
                If ops.DeleteCustomers(rows) Then
                    Dim row As DataRow = Nothing
                    Dim xInd As Integer = 0
                    For indexer As Integer = 0 To rows.Count - 1
                        xInd = indexer
                        dt.Rows.Remove(dt.AsEnumerable.FirstOrDefault(
                            Function(xrow) xrow.Field(Of Integer)("Identifier") = rows(xInd).Field(Of Integer)("Identifier")))
                    Next
                Else
                    MessageBox.Show($"Encountered errors: {ops.LastExceptionMessage}")
                End If
            Else
                MessageBox.Show("No rows selected")
            End If
        End Sub
        ''' <summary>
        ''' Delete the selected row from the database table, if successful delete the row
        ''' from the DataGridView via the BindingSource cast to a DataRow
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub cmdDeleteCurrent_Click(sender As Object, e As EventArgs) Handles cmdDeleteCurrent.Click
            Dim currentRow As DataRow = CType(bsCustomers.Current, DataRowView).Row
            If ops.DeleteSingleCustomer(currentRow) Then
                bsCustomers.RemoveCurrent()
            Else
                MessageBox.Show($"Encountered errors: {ops.LastExceptionMessage}")
            End If
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by monemas Saturday, January 13, 2018 6:23 PM
    Friday, January 12, 2018 10:37 PM
    Moderator

All replies

  • Whenever you load data from a database table into a DataGridView you should use a BindingSource and DataTable. From here all operations on the data should not be at the DataGridView level but instead at the DataSource level.

    Full source in VS2017. Includes a MS-Access .accdb

    So I'm not using the same condition in the SQL WHERE clause yet the idea here is to show how to do a delete no matter what the condition. You can do a like against the selected row or rows and use the primary key is best. If you don't have a primary auto-incrementing key you need one.

    Next up, it's much easier to select many rows with a DataGridViewCheckBox column as shown below, check some rows, press the first button or to delete the current row single click on the row to remove and click the second button. What happens is we make a call to remove the row(s), if successful at the database level remove the row(s) in the DataGridView via the DataSource.

    The project has a data class, an exception base class, extension module and a form.

    The base exception class

    Public Class BaseExceptionProperties
    
        Protected mHasException As Boolean
        ''' <summary>
        ''' Indicate the last operation thrown an exception or not
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property HasException() As Boolean
            Get
                Return mHasException
            End Get
        End Property
        Protected mLastException As Exception
        ''' <summary>
        ''' Provides access to the last exception thrown
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property LastException() As Exception
            Get
                Return mLastException
            End Get
        End Property
        ''' <summary>
        ''' If you don't need the entire exception as in LastException this 
        ''' provides just the text of the exception
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property LastExceptionMessage As String
            Get
                Return mLastException.Message
            End Get
        End Property
        ''' <summary>
        ''' Indicate for return of a function if there was an exception thrown or not.
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property IsSuccessFul As Boolean
            Get
                Return Not mHasException
            End Get
        End Property
    End Class
    

    Extensions

    Public Module ExtensionMethods
        <Runtime.CompilerServices.Extension()>
        Public Function GetRowsChecked(ByVal pTable As DataTable, ByVal pColumnName As String) As List(Of DataRow)
            Return pTable.AsEnumerable.Where(Function(row) row.Field(Of Boolean)(pColumnName)).ToList
        End Function
        <DebuggerStepThrough()>
        <Runtime.CompilerServices.Extension()>
        Public Sub ExpandColumns(ByVal pDataGridView As DataGridView)
            For Each col As DataGridViewColumn In pDataGridView.Columns
                col.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
            Next
        End Sub
    End Module
    

    Data class

    Imports System.Data.OleDb
    
    Public Class DataOperations
        Inherits BaseExceptionProperties
    
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
        }
    
    
        Public Function LoadCustomers() As DataTable
    
            Dim CustomersDataTable = New DataTable
            CustomersDataTable.Columns.Add(New DataColumn With {.ColumnName = "Process", .DataType = GetType(Boolean)})
    
            Try
    
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText = "SELECT Identifier, CompanyName, ContactTitle FROM Customers"
    
                        cn.Open()
    
                        CustomersDataTable.Load(cmd.ExecuteReader)
                        CustomersDataTable.Columns("Identifier").ColumnMapping = MappingType.Hidden
    
                        ' values are null by default
                        For Each row As DataRow In CustomersDataTable.Rows
                            row.SetField(Of Boolean)("Process", False)
                        Next
    
                    End Using
                End Using
    
    
            Catch ex As Exception
                mLastException = ex
                mHasException = True
            End Try
    
            Return CustomersDataTable
    
        End Function
        Public Function DeleteCustomers(ByVal pDataRows As List(Of DataRow)) As Boolean
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.Parameters.Add(New OleDbParameter With {.DbType = DbType.Int32, .ParameterName = "@Identifier"})
                    cmd.CommandText = "DELETE FROM Customers WHERE Identifier = @Identifier"
    
                    Try
    
                        cn.Open()
    
                        For indexer As Integer = 0 To pDataRows.Count - 1
                            cmd.Parameters("@Identifier").Value = pDataRows(indexer).Field(Of Integer)("Identifier")
                            cmd.ExecuteNonQuery()
                        Next
    
                        Return True
    
                    Catch ex As Exception
                        mLastException = ex
                        mHasException = True
                        Return False
                    End Try
    
                End Using
            End Using
        End Function
        Public Function DeleteSingleCustomer(ByVal pDataRow As DataRow) As Boolean
            Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn}
    
                    cmd.CommandText = "DELETE FROM Customers WHERE Identifier = @Identifier"
    
                    Try
    
                        cn.Open()
                        cmd.Parameters.AddWithValue("@Identifier", pDataRow.Field(Of Integer)("Identifier"))
                        cmd.ExecuteNonQuery()
    
                        Return True
    
                    Catch ex As Exception
                        mLastException = ex
                        mHasException = True
                        Return False
                    End Try
    
                End Using
            End Using
        End Function
    
    End Class
    

    Form code

    Public Class Form1
    
        Dim ops As New DataOperations
        Dim bsCustomers As New BindingSource
        ''' <summary>
        ''' Load our data
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            bsCustomers.DataSource = ops.LoadCustomers
            DataGridView1.DataSource = bsCustomers
            DataGridView1.Columns("Process").Width = 30
            DataGridView1.Columns("Process").HeaderText = ""
            DataGridView1.Columns("CompanyName").HeaderText = "Name"
            DataGridView1.Columns("ContactTitle").HeaderText = "Title"
            DataGridView1.ExpandColumns
        End Sub
        ''' <summary>
        ''' Get selected rows via the DataGridViewCheckBoxColumn
        ''' pass the rows to the data class, if the rows are removed
        ''' then remove the rows from the DataTable
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub cmdDeleteSelected_Click(sender As Object, e As EventArgs) Handles cmdDeleteSelected.Click
    
            Dim dt As DataTable = CType(bsCustomers.DataSource, DataTable)
            Dim rows As List(Of DataRow) = dt.GetRowsChecked("Process")
    
            If rows.Count > 0 Then
                If ops.DeleteCustomers(rows) Then
                    Dim row As DataRow = Nothing
                    Dim xInd As Integer = 0
                    For indexer As Integer = 0 To rows.Count - 1
                        xInd = indexer
                        dt.Rows.Remove(dt.AsEnumerable.FirstOrDefault(
                            Function(xrow) xrow.Field(Of Integer)("Identifier") = rows(xInd).Field(Of Integer)("Identifier")))
                    Next
                Else
                    MessageBox.Show($"Encountered errors: {ops.LastExceptionMessage}")
                End If
            Else
                MessageBox.Show("No rows selected")
            End If
        End Sub
        ''' <summary>
        ''' Delete the selected row from the database table, if successful delete the row
        ''' from the DataGridView via the BindingSource cast to a DataRow
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        Private Sub cmdDeleteCurrent_Click(sender As Object, e As EventArgs) Handles cmdDeleteCurrent.Click
            Dim currentRow As DataRow = CType(bsCustomers.Current, DataRowView).Row
            If ops.DeleteSingleCustomer(currentRow) Then
                bsCustomers.RemoveCurrent()
            Else
                MessageBox.Show($"Encountered errors: {ops.LastExceptionMessage}")
            End If
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by monemas Saturday, January 13, 2018 6:23 PM
    Friday, January 12, 2018 10:37 PM
    Moderator
  • Yes this is required
    Saturday, January 13, 2018 6:24 PM