Im developing a vb.net application and I have a dgv which displays a list of employees. The dgv has an unbound checkbox column which is used in selecting employees for a required action. I use rowFilter to filter the list by department or location or whatever the user chooses. If the user filters using Department 1 and checks the checkboxes next to the employees he wants, to select them and then filters to Department 2, to select a few more employees, when we filter back to Department 1, the checked employees aren't checked any more.
How can I make it such that once an employee is checked, you can filter with other parameters with losing the earlier selections. Im thinking of saving the checked state of each employee to the database before the next filter but I feel is possibly a quicker easier way. Is there?
Edit: This is the code I'm using to fill the dgv
sql = "SELECT employee_paysetup.EmployeeNumber AS EmployeeNo, employee_paysetup.FName, employee_paysetup.MName, " _
& "employee_paysetup.LName, (case when employee_paysetup.MName = ' ' then concat(employee_paysetup.FName,' ' , employee_paysetup.LName) else " _
& "concat(employee_paysetup.FName,' ' , employee_paysetup.MName,' ' , employee_paysetup.LName) end) " _
& "AS FullName, costcenters.CostCenterName AS cosName, departments.DepartmentName AS depName, " _
& "positions.PositionName AS posName, locations.LocationName AS locName, CostCenter, Department, Position, EmployeeLocation, PayGroup " _
& "FROM ((((((employee_paysetup LEFT JOIN employees ON employee_paysetup.EmployeeNumber = employees.EmployeeNumber) " _
& "LEFT JOIN costcenters ON employees.CostCenter = costcenters.CostCenterID) " _
& "LEFT JOIN departments ON employees.Department = departments.DepartmentID) " _
& "LEFT JOIN positions ON employees.Position = positions.PositionID) " _
& "LEFT JOIN locations ON employees.EmployeeLocation = locations.LocationID) " _
& "LEFT JOIN paygroups ON employee_paysetup.PayGroup = paygroups.ID) " & filterValue
cmd = New MySqlCommand(sql, conn)
da.SelectCommand = cmd
dt.DefaultView.Sort = "FullName ASC"
dgvEmployees.DataSource = dt.DefaultView
dt.DefaultView.RowFilter = "myFilter"
The problem is that there us no storage provided for the unbound column. A fairly simple way of doing is to use the
DataTable. Add a
Selected column via SQL so that a column is created for it in the
Boolean Alias is a small problem in MySQL since it lacks a way to CAST to Boolean. This SQL adds a column named
Selected to the
Dim sql = "SELECT False As Selected, Id, Name, Descr, Bird, Color, ItemDate FROM Sample"
... default will be to add Col0 as a TextColumn, and it will work fine entering
Selected. For a checkbox, the TextColumn needs to be replaced with a CheckColumn in the DGV. You can do it manually, or use the
If e.Column.Name = "Selected" AndAlso TypeOf e.Column Is DataGridViewTextBoxColumn Then Dim dc As New DataGridViewCheckBoxColumn() dc.HeaderText = "Selected" dc.Name = "Selected" dc.DataPropertyName = "Selected" dc.ValueType = GetType(Boolean) dgv5.Columns.Remove(e.Column) dgv5.Columns.Insert(0, dc) dc.DisplayIndex = 0 End If
The really important thing is to assign the
DataPropertyName so the DGV knows where in the table to store the data. NET knows it is a "virtual" column, so it will not interfere with any
DBCommand objects you may have built for the
DataAdapter (the OP cant have any based on that query, but future readers may). Results:
I check-selected several
Stork records, then filtered and the checks remained. At the core, this and Mr CrowCoder's answer do the same thing just differently: provide somewhere to store the
This method uses the DGV's natuaral ability to save to the DataTable automatically.