[RESOLVED] After sorting DataGridView lose checks in CheckBox Column
Hi there,
Within my code I create a new DataGridViewCheckBoxColumn.
vb Code:
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.
Re: After sorting DataGridView lose checks in CheckBox Column
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.
Re: After sorting DataGridView lose checks in CheckBox Column
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.
Re: After sorting DataGridView lose checks in CheckBox Column
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?
Re: After sorting DataGridView lose checks in CheckBox Column
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
Code:
<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
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
Re: After sorting DataGridView lose checks in CheckBox Column
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:
vb 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.
1 Attachment(s)
Re: After sorting DataGridView lose checks in CheckBox Column
Quote:
Originally Posted by
squatman
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:
vb 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.
Re: After sorting DataGridView lose checks in CheckBox Column
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.
vb Code:
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:
vb Code:
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.
Re: After sorting DataGridView lose checks in CheckBox Column
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.
Code:
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
Re: After sorting DataGridView lose checks in CheckBox Column
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..?
vb Code:
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.
Re: After sorting DataGridView lose checks in CheckBox Column
Interestingly I just added another Try/Catch to the code:
vb 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.
Re: After sorting DataGridView lose checks in CheckBox Column
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?
2 Attachment(s)
Re: After sorting DataGridView lose checks in CheckBox Column
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:
vb Code:
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
vb Code:
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:
vb Code:
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.
Re: After sorting DataGridView lose checks in CheckBox Column
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
Code:
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.
Re: After sorting DataGridView lose checks in CheckBox Column
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.
Re: After sorting DataGridView lose checks in CheckBox Column
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
Code:
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
Re: After sorting DataGridView lose checks in CheckBox Column
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.
Re: After sorting DataGridView lose checks in CheckBox Column
Quote:
Originally Posted by
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.
Good to hear you are on track now!
Re: [RESOLVED] After sorting DataGridView lose checks in CheckBox Column
i was having the same issue & was searching the forum
great piece work kevin
added my thanks too to you
Re: [RESOLVED] After sorting DataGridView lose checks in CheckBox Column
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.
