[RESOLVED] DataGridViewCheckBoxColumn from SQL bit type

[Fedaykin]

I am looking for a method to convert a SQL bit column (0, 1, NULL) queried into a DataGridViewCheckBoxColumn without using a loop. I tried:

Dim cell As DataGridViewCell = New DataGridViewCheckBoxCell()
 
        With DataGridView2
            With .Columns("FLY")
                .CellTemplate = cell
            End With
        End With

which resulted in the error: "An unhandled exception of type 'System.InvalidCastException' occurred in System.Windows.Forms.dll. Additional information: Value provided for CellTemplate must be of type System.Windows.Forms.DataGridViewTextBoxCell or derive from it."

I believe this has to do with the 3 state value of the bit type in the SQL column (0, 1, NULL). I also thought maybe to CAST the SQL value to a text value, but wasn't sure of the syntax. What is the best way to go about this and avoid the loop? Perhaps if the data only had 0,1 then VB.net would automatically convert this to TRUE/FALSE and display the Checkbox in the DataGridView?

[dday9]

The column needs to be declared as a DataGridViewCheckBoxColumn and it also needs to specify that its ThreeState property is true. How is the DataGridView initially created (e.g. via the designer or via code somewhere else)?

[Fedaykin]

Thank you for the quick reply. The DataGridView is created in Designer and populated with an SQL query. I was attempting to declare the DataGridViewCheckBoxColumn with code above but I need the data loaded before I can identify the "FLY" column.

[Fedaykin]

Okay, I tried:

Dim colu As DataGridViewCheckBoxColumn = DataGridView2.Columns("FLY")
        With colu
            .ThreeState = True
        End With

And got:

An unhandled exception of type 'System.InvalidCastException' occurred in pgr.exe

Additional information: Unable to cast object of type 'System.Windows.Forms.DataGridViewTextBoxColumn' to type 'System.Windows.Forms.DataGridViewCheckBoxColumn'.

[dday9]

So to clarify, the DataGridView is created in the designer but the DataGridViewColumns are not? E.g. you're relying on the AutoGenerateColumns property to let the DataGridView automatically create the respective columns?

[Fedaykin]

Your assumptions are correct, DataGridView is created in Designer and DataGridViewColumns are autogenerated. I think the NULL values in the database bit column are what are killing me. Maybe if I updated all of the NULLs to 0 then the column will auto-generate as a CheckBoxColumn?

[dday9]

One option that you have is to do the following after the data is populated and the columns are automatically generated:

1. Get the index of the Fly column in relation to the DataGridView's columns
2. Create a new DataGridViewCheckBoxColumn using the same properties of the Fly column
3. Specify the ThreeState property of the newly created DataGridViewCheckBoxColumn
4. Remove the Fly column by its index
5. Insert the newly created DataGridViewCheckBoxColumn at the same index where Fly was

[Fedaykin]

One option that you have is to do the following after the data is populated and the columns are automatically generated:

1. Get the index of the Fly column in relation to the DataGridView's columns
2. Create a new DataGridViewCheckBoxColumn using the same properties of the Fly column
3. Specify the ThreeState property of the newly created DataGridViewCheckBoxColumn
4. Remove the Fly column by its index
5. Insert the newly created DataGridViewCheckBoxColumn at the same index where Fly was

Okay, but how do I get the data into the new column without looping? Is that possible?

[dday9]

Yeah, if you specify the DataPropertyName of the newly created DataGridViewCheckBoxColumn it will show the data of the Fly field. That's part of step 2: ... using the same properties of the Fly column

[Fedaykin]

Ahhhh, gotcha. Okay let me try.

[Fedaykin]

Okay that works!? It makes zero sense to me how it works, but it works. Here is the working code:

'after the DataGridView datasource is set:
 
        Dim FlyIndex As Integer = DataGridView2.Columns.Item("FLY").Index
        DataGridView2.Columns.RemoveAt(FlyIndex)
 
        Dim PFC As New DataGridViewCheckBoxColumn
        PFC.HeaderText = "FLY"
        PFC.Name = "FLY"
        PFC.DataPropertyName = "FLY"
        PFC.ThreeState = True
        DataGridView2.Columns.Insert(FlyIndex, PFC)

Somehow this magically remembers the data for the column that was removed just by naming it the same?? What sorcery is this? If we remove the column it is 'gone', but obviously its held in memory somehow. By the way, I tried only setting PFC.HeaderText and PFC.Name without setting the PFC.DataPropertyName and this still worked. I just went ahead and set the DataPropertyName because that's what you said to do. Somehow this seems unclean to me but I'll take it until I educate myself more about it.

Thank you very much!

[si_the_geek]

The data is remembered because it not stored in the grid columns, it is stored in a DataTable (via the DataSource property of the grid). The grid just displays the relevant parts of the DataTable when they are on screen.

The DataPropertyName specifies what column of the data (in the DataSource) the column should display... in basic theory it shouldn't work without setting that, but I assume that if it is not set then the Name property gets tried instead - which in this case happens to have the same value, so it works. It is best to specify DataPropertyName tho, as the behaviour you've seen might not be guaranteed.

[Fedaykin]

Oh my goodness, of course! We are removing only the displayed column, not the datatable column! Thank you, I'm not sure why I didn't connect that dot. I agree that it probably defaults to the .Name property, but better to set the .DataPropertyName. Outstanding, thank you everyone for your input. I could not find this solution anywhere else.