[RESOLVED] After sorting DataGridView lose checks in CheckBox Column

[squatman]

Hi there,

Within my code I create a new DataGridViewCheckBoxColumn.

Dim CheckCol As New DataGridViewCheckBoxColumn
CheckCol.Name = "CheckBox"
dgv_DocumentListCP.Columns.Add(CheckCol)

If I check this cell in certain rows, and then sort the DGV by clicking a column header, all of the CheckBoxes reset to not checked.

The rest of the columns are populated programmatically from a database into a dataset/table/view then binded to the DGVs DataSource.

To get the CheckBoxes to stay checked after a sort, do I need to have it write some data into a column in the database..?

If so is there another way than this..?

Thanks in advance.

[dunfiddlin]

What are the checkboxes there for? If they are row specific then you need to include them, if not in the database, at least in the relevant datatable in your program. If you add a Boolean column to the table after it is filled, you can use that to keep a record of checked and unchecked which will then be retained in the DGV sort.

[squatman]

Thanks for the reply dunfiddlin.

The check box column will be used just to select individual rows, rather than selecting them with Ctrl/Shift.
I will then want to refer to the rows that are checked and grab information from them.

Which method do you think would be best for achieving this..?

Thanks again.

[dunfiddlin]

So what you're really asking for is a way to retain selections through a new sort which is whole different kettle of fish and not easily achieved (if indeed it's possible at all). Is there a particular reason for this because it's not immediately obvious why you would need this behaviour?

[kareninstructor]

The following similates loading data into a DataTable from a database then adds a column for allowing users to check the rows they want. A language extension is used to get the selected rows. Also there is code to keep the current row selected when you sort via the DataGridView column headers.

Requires a DataGridView and a Button. Note the one section of code must be in a code module and not a class or form.
For the code in Button1 the display goes to the IDE output window. Also note we are dealing with the data rather than the rows of the DataGridView.

Code module

<System.Diagnostics.DebuggerStepThrough()> _
<Runtime.CompilerServices.Extension()> _
Public Function GetChecked(ByVal sender As DataGridView, ByVal ColumnName As String) As List(Of DataGridViewRow)
    Return (From Rows In sender.Rows.Cast(Of DataGridViewRow)() Where CBool(Rows.Cells(ColumnName).Value) = True).ToList
End Function

Form code

WithEvents bsData As New BindingSource
Private RowIdentifier As String = ""
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim dt As New DataTable
    dt.Columns.AddRange(New DataColumn() _
                        {
                            New DataColumn("Identifier", GetType(System.String)),
                            New DataColumn("C1", GetType(System.String)),
                            New DataColumn("C2", GetType(System.String)),
                            New DataColumn("C3", GetType(System.Decimal)),
                            New DataColumn("C4", GetType(System.String))
                        }
                    )

    dt.Rows.Add(New Object() {1, "A", "1", 3.0, "AAAA"})
    dt.Rows.Add(New Object() {2, "B", "2", 10.5, "BBBB"})
    dt.Rows.Add(New Object() {3, "C", "3", 4567.66, "CCCC"})

    dt.Columns.Add(New DataColumn("Process", GetType(System.Boolean)))
    dt.Columns("Process").SetOrdinal(0)
    dt.Columns("Identifier").ColumnMapping = MappingType.Hidden

    For Each row As DataRow In dt.Rows
        row("Process") = False
    Next
    dt.AcceptChanges()

    bsData.DataSource = dt
    DataGridView1.DataSource = bsData

End Sub
Private Sub bsData_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles bsData.PositionChanged
    RowIdentifier = CType(bsData.Current, DataRowView).Item("Identifier").ToString()
End Sub
Private Sub DataGridView1_Sorted(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.Sorted
    bsData.Position = bsData.Find("Identifier", RowIdentifier)
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim Checked = CType(bsData.DataSource, DataTable).GetChecked("Process")
    For Each row In Checked
        Console.WriteLine(String.Join(",", row.ItemArray))
    Next
End Sub

[squatman]

dunfiddlin - Well once certain rows have been checked a user may want to then sort other columns to help find the information they was looking for. So it makes sense that the checks are retained during this process. Also in the future I may want the checked rows to automatically jump to the top, but not sure yet.

kevininstructor - thanks a lot for your reply and code sample. I made a new module and inserted your "Code Module" code. I then pasted all your "Form Code" in. I get an error in the button code:

Dim Checked = CType(bsData.DataSource, DataTable).GetChecked("Process")

It says 'GetChecked is not a member of 'System.Data.DataTable'.

I know you said this "For the code in Button1 the display goes to the IDE output window"

I'm not really sure what that means, or if I need to do something else..?

Thanks again, it is much appreciated.

[kareninstructor]

kevininstructor - thanks a lot for your reply and code sample. I made a new module and inserted your "Code Module" code. I then pasted all your "Form Code" in. I get an error in the button code:

Dim Checked = CType(bsData.DataSource, DataTable).GetChecked("Process")

It says 'GetChecked is not a member of 'System.Data.DataTable'.

I know you said this "For the code in Button1 the display goes to the IDE output window"

I'm not really sure what that means, or if I need to do something else..?

Thanks again, it is much appreciated.

I gave you the wrong extension, had one for DataGridView, one for DataTable. Any ways I tweaked the one missing and placed it into a demo VS2010 project attached below. Note how I show the data in a child form via a DataTable rather than a List(Of DataRow) via LINQ/Language extension.

[squatman]

kevininstructor,

Thank you very much for your reply and sample. It works perfectly!

I am trying to implement it into my code and I'm struggling a bit as I use a DataView as the DGV.DataSource.

Dim DA As New OleDbDataAdapter
        Dim DS As New DataSet
 
        Dim cn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DatabaseLocation & "Job_" & DocumentListCP_ProjectID_Local & ".accdb;")
        cn.Open()
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Documents WHERE `Lock` = 'off'", cn)
        DA.SelectCommand = cmd
 
        Dim Discipline As String
        Dim DocType1 As String
        Dim DocType2 As String
        Dim Separators() As Char
        Dim Node() As String
        Separators = tvw_CurrentProjects.PathSeparator.ToCharArray
        Try
            Node = tvw_CurrentProjects.SelectedNode.FullPath.ToString.Split(Separators)
        Catch ex As Exception
            dgv_DocumentListCP.DataSource = Nothing
            Exit Sub
        End Try
 
        If Node.Length > 1 Then _
        Discipline = Node(1)
        If Node.Length > 2 Then _
        DocType1 = Node(2)
        If Node.Length > 3 Then _
        DocType2 = Node(3)
 
        Try
            DA.Fill(DS)
            Dim dt As New DataTable("Documents")
            DA.Fill(dt)
 
            If tvw_CurrentProjects.SelectedNode.Level = 0 Then
                Dim dv As New DataView(dt, "[Category = DC Level 4]='" & DocumentListCP_Category_Local & "'", "[Doc No Int] ASC", DataViewRowState.CurrentRows)
                Me.dgv_DocumentListCP.DataSource = dv
            ElseIf tvw_CurrentProjects.SelectedNode.Level = 1 Then
                Dim dv As New DataView(dt, "[Category = DC Level 4]='" & DocumentListCP_Category_Local & "' AND [Discipline = DC Level 5]='" & Discipline & "'", "[Doc No Int] ASC", DataViewRowState.CurrentRows)
                Me.dgv_DocumentListCP.DataSource = dv
            ElseIf tvw_CurrentProjects.SelectedNode.Level = 2 Then
                Dim dv As New DataView(dt, "[Category = DC Level 4]='" & DocumentListCP_Category_Local & "' AND [Discipline = DC Level 5]='" & Discipline & "' AND [Document Type 1 = DC Level 6]='" & DocType1 & "'", "[Doc No Int] ASC", DataViewRowState.CurrentRows)
                Me.dgv_DocumentListCP.DataSource = dv
            ElseIf tvw_CurrentProjects.SelectedNode.Level = 3 Then
                Dim dv As New DataView(dt, "[Category = DC Level 4]='" & DocumentListCP_Category_Local & "' AND [Discipline = DC Level 5]='" & Discipline & "' AND [Document Type 1 = DC Level 6]='" & DocType1 & "' AND [Document Type 2 = DC Level 7]='" & DocType2 & "'", "[Doc No Int] ASC", DataViewRowState.CurrentRows)
                Me.dgv_DocumentListCP.DataSource = dv
            End If
 
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Something Went Wrong", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
        cn.Close()
        cn.Dispose()
        dgv_DocumentListCP.ClearSelection()
 
        PrepareColumns()

I tried this:

Dim DA As New OleDbDataAdapter
        Dim DS As New DataSet
 
        Dim cn As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DatabaseLocation & "Job_" & DocumentListCP_ProjectID_Local & ".accdb;")
        cn.Open()
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Documents WHERE `Lock` = 'off'", cn)
        DA.SelectCommand = cmd
 
        Dim Discipline As String
        Dim DocType1 As String
        Dim DocType2 As String
        Dim Separators() As Char
        Dim Node() As String
        Separators = tvw_CurrentProjects.PathSeparator.ToCharArray
        Try
            Node = tvw_CurrentProjects.SelectedNode.FullPath.ToString.Split(Separators)
        Catch ex As Exception
            dgv_DocumentListCP.DataSource = Nothing
            Exit Sub
        End Try
 
        If Node.Length > 1 Then _
        Discipline = Node(1)
        If Node.Length > 2 Then _
        DocType1 = Node(2)
        If Node.Length > 3 Then _
        DocType2 = Node(3)
 
        Try
            DA.Fill(DS)
            Dim dt As New DataTable("Documents")
            DA.Fill(dt)
 
            dt.Columns.Add(New DataColumn("Process", GetType(System.Boolean)))
            dt.Columns("Process").SetOrdinal(0)
            dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
 
            For Each row As DataRow In dt.Rows
                row("Process") = False
            Next
            dt.AcceptChanges()
 
            If tvw_CurrentProjects.SelectedNode.Level = 0 Then
                Dim dv As New DataView(dt, "[Category = DC Level 4]='" & DocumentListCP_Category_Local & "'", "[Doc No Int] ASC", DataViewRowState.CurrentRows)
                bsData.DataSource = dv
                Me.dgv_DocumentListCP.DataSource = bsData
            ElseIf tvw_CurrentProjects.SelectedNode.Level = 1 Then
                Dim dv As New DataView(dt, "[Category = DC Level 4]='" & DocumentListCP_Category_Local & "' AND [Discipline = DC Level 5]='" & Discipline & "'", "[Doc No Int] ASC", DataViewRowState.CurrentRows)
                bsData.DataSource = dv
                Me.dgv_DocumentListCP.DataSource = bsData
            ElseIf tvw_CurrentProjects.SelectedNode.Level = 2 Then
                Dim dv As New DataView(dt, "[Category = DC Level 4]='" & DocumentListCP_Category_Local & "' AND [Discipline = DC Level 5]='" & Discipline & "' AND [Document Type 1 = DC Level 6]='" & DocType1 & "'", "[Doc No Int] ASC", DataViewRowState.CurrentRows)
                bsData.DataSource = dv
                Me.dgv_DocumentListCP.DataSource = bsData
            ElseIf tvw_CurrentProjects.SelectedNode.Level = 3 Then
                Dim dv As New DataView(dt, "[Category = DC Level 4]='" & DocumentListCP_Category_Local & "' AND [Discipline = DC Level 5]='" & Discipline & "' AND [Document Type 1 = DC Level 6]='" & DocType1 & "' AND [Document Type 2 = DC Level 7]='" & DocType2 & "'", "[Doc No Int] ASC", DataViewRowState.CurrentRows)
                bsData.DataSource = dv
                Me.dgv_DocumentListCP.DataSource = bsData
            End If
 
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Something Went Wrong", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
        cn.Close()
        cn.Dispose()
        dgv_DocumentListCP.ClearSelection()
 
        PrepareColumns()

But the Catch ex As Exception caught the error...

Is there a simple way to implement it into my code..?

Thanks again.

[kareninstructor]

You could load all data as I suggested in regards to the BindingSource then filter via the BindingSource. The example below is altered from my first example, this one loads data from Customers.xml which was extracted from NorthWnd.mdb from Microsoft. Note how the filter code works, in a sense mimics what you are doing for the DataView code. If this does not make sense let me know. I will be away from my computer for about two hours.

Public Class Form1
    WithEvents bsData As New BindingSource
    Private RowIdentifier As String = ""
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim ds As New DataSet
        ds.ReadXml("Customers.xml")
        Dim dt = ds.Tables(0)
        dt.Columns.Add(New DataColumn("Process", GetType(System.Boolean)))
        dt.Columns("CustomerID").ColumnMapping = MappingType.Hidden
        dt.Columns("Process").SetOrdinal(0)
        For Each row As DataRow In dt.Rows
            row("Process") = False
        Next
        dt.AcceptChanges()

        bsData.DataSource = dt
        DataGridView1.DataSource = bsData
    End Sub
    Private Sub bsData_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles bsData.PositionChanged
        If bsData.Current IsNot Nothing Then
            RowIdentifier = CType(bsData.Current, DataRowView).Item("CustomerID").ToString()
        End If
    End Sub
    Private Sub DataGridView1_Sorted(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.Sorted
        bsData.Position = bsData.Find("CustomerID", RowIdentifier)
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdGetCheckedRows.Click
        Dim f As New Form2
        Try
            f.DataGridView1.DataSource = CType(bsData.DataSource, DataTable).GetChecked("Process")
            f.ShowDialog()
        Finally
            f.Dispose()
        End Try
    End Sub
    Private Sub cmdFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdFilter.Click
        bsData.Filter = "Country = 'Germany' AND City = 'Berlin'"
    End Sub
    Private Sub cmdRemoveFilter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRemoveFilter.Click
        bsData.Filter = ""
    End Sub
End Class

[squatman]

kevin - thanks for the reply.

I see the filter, looks nice and neat!

Even with my filter/DataView section commented out I get the same error, I assume a problem with the DataTable..?

Try
            DA.Fill(DS)
            Dim dt As New DataTable("Documents")
            DA.Fill(dt)
 
            dt.Columns.Add(New DataColumn("CheckBox", GetType(System.Boolean)))
            dt.Columns("CheckBoxMap").ColumnMapping = MappingType.Hidden
            dt.Columns("CheckBox").SetOrdinal(0)
            For Each row As DataRow In dt.Rows
                row("CheckBox") = False
            Next
            dt.AcceptChanges()
 
            bsData.DataSource = dt
            dgv_DocumentListCP.DataSource = bsData
 
            'If tvw_CurrentProjects.SelectedNode.Level = 0 Then
            '    'Dim dv As New DataView(dt, "[Category = DC Level 4]='" & DocumentListCP_Category_Local & "'", "[Doc No Int] ASC", DataViewRowState.CurrentRows)
            '    'Me.dgv_DocumentListCP.DataSource = dv
            '    bsData.Filter = "[Category = DC Level 4]='" & DocumentListCP_Category_Local & "'"
            '    dgv_DocumentListCP.DataSource = bsData
            'ElseIf tvw_CurrentProjects.SelectedNode.Level = 1 Then
            '    Dim dv As New DataView(dt, "[Category = DC Level 4]='" & DocumentListCP_Category_Local & "' AND [Discipline = DC Level 5]='" & Discipline & "'", "[Doc No Int] ASC", DataViewRowState.CurrentRows)
            '    Me.dgv_DocumentListCP.DataSource = dv
            'ElseIf tvw_CurrentProjects.SelectedNode.Level = 2 Then
            '    Dim dv As New DataView(dt, "[Category = DC Level 4]='" & DocumentListCP_Category_Local & "' AND [Discipline = DC Level 5]='" & Discipline & "' AND [Document Type 1 = DC Level 6]='" & DocType1 & "'", "[Doc No Int] ASC", DataViewRowState.CurrentRows)
            '    Me.dgv_DocumentListCP.DataSource = dv
            'ElseIf tvw_CurrentProjects.SelectedNode.Level = 3 Then
            '    Dim dv As New DataView(dt, "[Category = DC Level 4]='" & DocumentListCP_Category_Local & "' AND [Discipline = DC Level 5]='" & Discipline & "' AND [Document Type 1 = DC Level 6]='" & DocType1 & "' AND [Document Type 2 = DC Level 7]='" & DocType2 & "'", "[Doc No Int] ASC", DataViewRowState.CurrentRows)
            '    Me.dgv_DocumentListCP.DataSource = dv
            'End If
 
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Something Went Wrong", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

Your help again will be greatly appreciated.

[squatman]

Interestingly I just added another Try/Catch to the code:

Try
                dt.Columns.Add(New DataColumn("CheckBox", GetType(System.Boolean)))
                dt.Columns("CheckBoxMap").ColumnMapping = MappingType.Hidden
                dt.Columns("CheckBox").SetOrdinal(0)
                For Each row As DataRow In dt.Rows
                    row("CheckBox") = False
                Next
                dt.AcceptChanges()
            Catch ex As Exception
                MessageBox.Show(ex.Message, "Something Went Wrong1", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try

I get the message pop up "Object reference not set to an instance of an object." I click ok, then my DGV is populated and the CheckBox column is there and appears to be working...

Thanks.

[kareninstructor]

In the last reply you create a DataColumn named CheckBox then from what I see change ColumnMappings for a column named CheckBoxMap, does that column exists or did you mean CheckBox rather than CheckBoxMap?

[squatman]

ok, so I've been having a bit of a play. I still get the above error, but getting a bit further I get these errors:

Try
            DA.Fill(DS)
            Dim dt As New DataTable("Documents")
            DA.Fill(dt)
 
            Try
                dt.Columns.Add(New DataColumn("CheckBox", GetType(System.Boolean)))
                dt.Columns("CheckBoxMap").ColumnMapping = MappingType.Hidden
                dt.Columns("CheckBox").SetOrdinal(0)
                For Each row As DataRow In dt.Rows
                    row("CheckBox") = False
                Next
                dt.AcceptChanges()
            Catch ex As Exception
                'MessageBox.Show(ex.Message, "Something Went Wrong1", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
 
            bsData.DataSource = dt
            dgv_DocumentListCP.DataSource = bsData
 
            If tvw_CurrentProjects.SelectedNode.Level = 0 Then
                bsData.Filter = "[Category = DC Level 4]='" & DocumentListCP_Category_Local & "'"
                dgv_DocumentListCP.DataSource = bsData
             End If
 
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Something Went Wrong2", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

Private Sub tmi_Notification_Click(sender As System.Object, e As System.EventArgs) Handles tmi_Notification.Click
        frm_Notification.dgv_NotificationDocuments.DataSource = CType(bsData.DataSource, DataTable).GetChecked("CheckBox")
End Sub

With this code I get the error shown in the attached image. (Capture.PNG)

As I wasn't testing after sorting columns I left the following code out, but I was getting errors I added it in in case:

Private Sub bsData_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles bsData.PositionChanged
        If bsData.Current IsNot Nothing Then
            RowIdentifier = CType(bsData.Current, DataRowView).Item("CheckBoxMap").ToString()
        End If
    End Sub
 
    Private Sub dgv_DocumentListCP_Sorted(ByVal sender As Object, ByVal e As System.EventArgs) Handles dgv_DocumentListCP.Sorted
        bsData.Position = bsData.Find("CheckBoxMap", RowIdentifier)
    End Sub

I now get the error show in attached image (Capture2.PNG) before I even load my original DGV.

I can't thank you enough for your time and effort in this Kevin. It really is appreciated.

[kareninstructor]

Error 1 (is responsible for error 2)
The error is because each row the Boolean column (using code from my example) for/each loop did not complete, if it had then you would not get the Null error

For Each row As DataRow In dt.Rows
    row("Process") = False
Next

Error 2
The other error pointing to CheckBoxMap is from reading your code does not exists (also see my last reply which addresses this).

Any ways you are very close.

[squatman]

Kevin you god!

Where you was using "dt.Columns("CustomerID").ColumnMapping = MappingType.Hidden" I got mixed up, I didn't actually need this. So my previous second error I changed "CheckBoxMap" to a column "ID" that I already have from my database. Now all appears to be working

Only problem now is, I check 4 rows, transfer then to another DGV iy only bring across 3 of them, unless I move the row selection to a different one. It's as if it's not registering the last "Check". Any ideas..?

Thanks again kevin, I have no idea how you knew how to come up with that code and create your own awesome functions. I'd be very interested in some VB.net book recommendations. I know there are tonnes of resources, but it's just nice sometime to sit down with a book. So if you have any recommendations, maybe even some that you've used please let me know.

Thanks a lot.

[kareninstructor]

In regards to checking four rows and only three coming across, this might have something to do with how the data is loaded via the DataAdapter. Try the following

Private Sub DataGridView1_CurrentCellDirtyStateChanged(
    ByVal sender As Object,
    ByVal e As EventArgs) _
Handles DataGridView1.CurrentCellDirtyStateChanged

    If TypeOf DataGridView1.CurrentCell Is DataGridViewCheckBoxCell Then
        DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)
    End If

End Sub

In regards to the code I presented, I have been coding for years and over time have created a) a repository b) a library of code for questions for online forums or for work. What is critical is developing code such as this way before you need it since when you need it there is less time generally to learn as you go especially with my position that when something breaks millions of people are affected. With that kind of pressure there is no time to learn as you go.

Concerning books, have not read anything for .NET but do read MSDN online standard documentation which many times will give you answers or if not using information there can assist searching via Google to get more information that always one to get what they need.

Some resources for learning
Beth Massi Sharing the goodness - you will need to go back in time as her current stuff is on LightSwitch while older articles on VB.NET
Data Over Forms videos (have not view them but heard they are good)
Deborah's Developer MindScape
MSDN LINQ samples
MSDN LINQ Portal

[squatman]

You legend Kevin, that did the trick!

Thanks a lot for all your help, support and time it really is appreciated.

Also thanks for the references, I'll be sure to check them out.

[kareninstructor]

You legend Kevin, that did the trick!

Thanks a lot for all your help, support and time it really is appreciated.

Also thanks for the references, I'll be sure to check them out.

Good to hear you are on track now!

[make me rain]

i was having the same issue & was searching the forum
great piece work kevin
added my thanks too to you

[squatman]

Was I calling you Kevin all that time and you are called Karen? No idea how that happened. My apologies and thank you very much again for your help.