Deleting multiple records from sql bound Datagrid

Question

The procedures below allowed me to delete several records at once by checking the checkbox on my datagrid. The procedure was written on ASP.net but now I am using a winform on VB.net.

I have a datagrid with column name "Delete" where the checkboxes are located. The user would check the records it wants to delete and the would delete those records. I use the "Ticket Number" column values as the parameter for my query.

The issue I have is that since was written for ASP.Net, I cannot find how the winform VB.net equivalent for this line:

Dim chkDelete As CheckBox = DirectCast(grdRoster.Rows(i).Cells(0).FindControl("Delete_Row"), CheckBox)

FindControl is not a member of System.Windows.Forms.DataGridViewCell. Plus I am pretty sure that the whole line is wrong since the checkboxes are located on a datagrid column set as ColumnType: DataGridViewCheckBoxColumn and are not really individual controls.

How can I get the same result on a winform? Here is my entire code.

    Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click

        'Create String Collection to store 
        'IDs of records to be deleted 

        Dim ticketNumberCollection As New StringCollection()
        Dim strTicketNumber As String = String.Empty

        'Loop through GridView rows to find checked rows 

        For i As Integer = 0 To grdRoster.Rows.Count - 1

            Dim chkDelete As CheckBox = DirectCast(grdRoster.Rows(i).Cells(0).FindControl("Delete_Row"), CheckBox)

            If chkDelete IsNot Nothing Then
                If chkDelete.Checked Then
                    strTicketNumber = grdRoster.Rows(i).Cells(1).ToString
                    ticketNumberCollection.Add(strTicketNumber)
                End If
            End If

        Next

        'Call the method to Delete records 
        DeleteMultipleRecords(ticketNumberCollection)

        ' rebind the GridView
        grdRoster.DataBind()

    End Sub

   ' Sub to delete multiple records
   ' @param "idCollection" calls the string collection above
   ' and deletes the selected record separated by ","

    Private Sub DeleteMultipleRecords(ByVal ticketNumberCollection As StringCollection)

        Dim IDs As String = ""

        'Create string builder to store 
        'delete commands separated by ,

        For Each id As String In ticketNumberCollection

            IDs += id.ToString() & ","

        Next

        Try
            Dim strTicketID As String = IDs.Substring(0, IDs.LastIndexOf(","))

            DataSheetTableAdapter.DeleteRecord(strTicketID)

        Catch ex As Exception

            Dim errorMsg As String = "Error in Deletion"
            errorMsg += ex.Message
            Throw New Exception(errorMsg)

        Finally

            Me.Close()


        End Try
    End Sub

Your code above already identifies and processes records to be deleted. What is the error or problem you are getting exactly? Why do you want to translate the ASP.NET code? Where would it go? — NoChance
– NoChance, Commented Jan 14, 2014 at 3:13
@EmmadKareem is going into a winform. The errors is that FindControl is not a member of system. Windows.Forms.Datagrid — Jose M.
– Jose M., Commented Jan 14, 2014 at 3:45
Of course it is not, my question is about what do you want to your program to do, leave the translation issue aside. Why do you need this line? What value is it supposed to serve? — NoChance
– NoChance, Commented Jan 14, 2014 at 4:24

user108 · Accepted Answer · 2016-07-14 09:11:36Z

for deleting multiple records from a data bound gridview you should create the DataGridViewCheckBoxColumn at run time

    Dim chk As New DataGridViewCheckBoxColumn()
            DataGridView1.Columns.Add(chk)
            chk.HeaderText = "Select"

'then bind your datagridview with dataset

 Dim sql As String = "SELECT * FROM table_name"
    ' Dim connection As New SqlConnection(connectionString)
    conn.Open()
    sCommand = New SqlCommand(sql, conn)
    sAdapter = New SqlDataAdapter(sCommand)
    sBuilder = New SqlCommandBuilder(sAdapter)
    sDs = New DataSet()
    sAdapter.Fill(sDs, "table_name")
    sTable = sDs.Tables("table_name")

 DataGridView1.DataSource = sDs.Tables("table_name")

'then traverse through each column and get the checked values

Try
        DataGridView1.EndEdit()
        For j = Me.DataGridView1.Rows.Count - 1 To 0 Step -1
            If Not IsDBNull(DataGridView1.Rows(j).Cells(0).Value) Then
                If DataGridView1.Rows(j).Cells(0).Value = True Then
                    check = True
                    If MessageBox.Show("Do you want to delete these records?", "Delete", MessageBoxButtons.YesNo) = DialogResult.Yes Then
                        For i = Me.DataGridView1.Rows.Count - 1 To 0 Step -1
                            If Not IsDBNull(DataGridView1.Rows(i).Cells(0).Value) Then
                                If DataGridView1.Rows(i).Cells(0).Value = True Then

                                 'remove the checked columns and update datatable
                                    DataGridView1.Rows.RemoveAt(i)
                                    sAdapter.Update(sTable)
                                End If
                            End If
                        Next
                    Else
                        Return
                    End If
                Else
                End If
            End If
        Next
        If check = False Then
            MsgBox("Nothing Selected")
        End If

                Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

Great answer - would be a bit more clear if .Cells(0) was coded as .Cells("Select")

Collectives™ on Stack Overflow

Deleting multiple records from sql bound Datagrid

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