Mutually exclusive checkbox columns in datagridview

[jtammyg]

Hi!

I need your help one more time, experts!

I have a bound datagridview pulling data in from SQL Server 2008 db. I have 4 columns in my database that allow for bit values.

When the datagridview is populated, it shows the checkboxes for those 4 columns. I need to make them mutually exclusive, i.e. if someone clicks on checkbox from column 15, then they cannot select another one, if they do, it will uncheck column 15 and check the one they choose next.


I found this code online, but it makes the checkbox mutually exclusive for the whole column, i.e. if I click on row 1, I cannot click on row 2 or cleans the row 1. This is great, but I need to accommodate across the columns, not the rows.

Any help will be greatly appreciated.

Thanks a lot!

Tammy

    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick

        '  make the checkboxes mutually exclusive
        If TypeOf DataGridView1.Columns(e.ColumnIndex) Is DataGridViewCheckBoxColumn Then
            Dim state As Boolean = Convert.ToBoolean(DirectCast(DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex), DataGridViewCheckBoxCell).EditingCellFormattedValue)
            Dim intDemarcationSetID As Integer = Convert.ToInt32(DirectCast(DataGridView1.Rows(e.RowIndex).Cells(0), DataGridViewTextBoxCell).EditedFormattedValue)
            If state Then
                '  unselect all the other ones
                For Each dgvr As DataGridViewRow In DataGridView1.Rows
                    If Convert.ToInt32(dgvr.Cells(0).Value) <> intDemarcationSetID Then
                        DirectCast(dgvr.Cells(e.ColumnIndex), DataGridViewCheckBoxCell).Value = False
                    End If
                Next

            End If
        End If


    End Sub

[sparbag]

Hello Tammy,

I noticed that you had received no responses so decided to read why.

Whenever I read the 'mutually exclusive' in the fields of a database I tend to focus on the database design.

Why do you need three of the four fields to be NULL? This is not good database design as you will be spending most of your time ensuring that three of the four fields remain NULL at all times.
Major issue: you start getting records where there are only two NULL fields for the records! Which is the correct fields value for the record. No idea. What are you going to do? Data integrity is completely lost for this record.

I think you need to take a step back and assess why you need NULL fields for the evaluation of bits. What other table design is available that will ensure data integrity.

What are you trying to achieve with your database?

Kind regards

Steve

[jtammyg]

Hi Steve!

This was done in Access by someone else. The end users liked the way it was designed and those are the same requirements they have for the new application.

Basically there are records with patient info coming from the db, the clinical team will have to select if the test for the records where 'foundandentered','foundnotentered','notfound' and lastly 'duplicate'. In Access this was achieved by the way of 4 columns that had checked boxes in them and they could only choose 1 of the check boxes.

I am trying to mimic this behavior now in VB but am not being successful.

I was thinking of having one column only with a dropdown in the datagridview, but I am sure that they end users will be resistant to this change, since their manager emphasized the need to keep everything the same.

How would you go about it, Steve?

Thanks!

Tammy

[sparbag]

Hello Tammy,

From a logic perspective I would create four separate Boolean variables to hold the state of each checkbox. As the user selects a row the Booleans will have to be set to mimic the records current values.
as the user selected a checkbox you would have to reset the Booleans for the checkboxes to their correct status (false or true) This would be called as a function every time a selection is made. This ensures that no two ticked checkboxes may exist depending on their selection.
write this to the record
Update the datagrid view to show the latest data.

The reason why I would use Booleans and would reset checkboxes would be to minimise the ability of the malicious user in damaging records. I would use the four Booleans and the four would be passed as Values to the function
Function ensures that only a single Boolean has a true value. The function then passes control to the sub that writes the record if only one Boolean is true other wise return to the user with an error. Technically errors should never occur as you will be setting all four Booleans when the checkbox is selected, the latest selection being the only true Boolean.

Kind regards

Steve

[techgnome]

Ugh. Everything you need is right there... I get the impression you don't undertsand what the code is doing or why... best way to understand it, is to set some breakpoints and step through the code. IF you have you would have see the line: For Each dgvr As DataGridViewRow In DataGridView1.Rows and gone "OH! IT's looping through all the rows in the grid and resetting them!" ... then you would have understood why it's marking the WHOLE grid rather than just the row. The solution should have then been quite simple.

Meanwhile all you need to do is get the value of the current cell that just changed... and if it's checked true, re-set the values of the other cols. - becareful because doing so will cause the event to fire multiple times, so it should ONLY SET OTEHR COLS IF THE CURRENT ONE BEING CHANGED IS BEING SET TO TRUE... otherwise you'll end up in an infinite loop.

-tg