Answered Detecting Null Values in a DGV

Slabs1960

Member
Joined
Mar 19, 2017
Messages
19
Programming Experience
10+
I struggled with this for a while.

When a Excel Spreadsheet is imported into a DGV, some blanks cells (in Excel) are imported as Null values. This causes exceptions with some code. So, my idea was to replace all Null values with default values, depending on the data type ("" for Text or False for Checkboxes, etc.).

I tried "TypeOf" test to determine the data type. This did not work, since the value in the the cell is Null it has no type. I eventually found "CellType" to determine the date type.

I did as following:
VB.NET:
    Public Sub ClearNullValues(ByRef _DataGridView As DataGridView)

        Dim strCellType As String

        Try
            For y As Integer = 0 To _DataGridView.Rows.Count - 1
                For x As Integer = 0 To _DataGridView.ColumnCount - 1

                    strCellType = _DataGridView.Columns(x).CellType.ToString()

                    If IsDBNull(_DataGridView.Rows(y).Cells(x).Value) Then

                        Select Case strCellType

                            Case "System.Windows.Forms.DataGridViewTextBoxCell"
                                _DataGridView.Rows(y).Cells(x).Value = ""
                            Case "System.Windows.Forms.DataGridViewCheckBoxCell"
                                _DataGridView.Rows(y).Cells(x).Value = False
                            Case Else
                                _DataGridView.Rows(y).Cells(x).Value = "???"

                        End Select

                    End If

                Next
            Next

        Catch ex As Exception
            MessageBox.Show(ex.Message, "Test For Null Values", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

The code scans the whole DGV and replaces Null values with its default type. The "Case Else" replaces all unknown types with "???". This is test code for me so that I can easily detect other types I may have missed and eventually cater for the Type in the Case statement.

My question is this the correct way, or is there a way to simplify the code. Is there a better way to do this?
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,438
Location
Norway
Programming Experience
10+
Instead of using the grid and type of display control I would loop the DataTable and set value depending on datatype of column, setting value to empty string for string type columns and otherwise default value for that data type. The reason is that almost all data types uses the TextBox column type for presentation.
VB.NET:
For Each row As DataRow In table.Rows
    For columnIndex = 0 To table.Columns.Count - 1
        If row.IsNull(columnIndex) Then
            Dim t = table.Columns(columnIndex).DataType
            row(columnIndex) = If(t Is GetType(String), String.Empty, Activator.CreateInstance(t))
        End If
    Next
Next
 

Slabs1960

Member
Joined
Mar 19, 2017
Messages
19
Programming Experience
10+
Instead of using the grid and type of display control I would loop the DataTable and set value depending on datatype of column, setting value to empty string for string type columns and otherwise default value for that data type. The reason is that almost all data types uses the TextBox column type for presentation.
VB.NET:
For Each row As DataRow In table.Rows
    For columnIndex = 0 To table.Columns.Count - 1
        If row.IsNull(columnIndex) Then
            Dim t = table.Columns(columnIndex).DataType
            row(columnIndex) = If(t Is GetType(String), String.Empty, Activator.CreateInstance(t))
        End If
    Next
Next

Thanks for the reply. I tried your code.

The problem is one of the Columns in the DGV is a CheckBox column. Your code does not detect this and sets the data to a String. An Exception is thrown on this column. Seems CheckBoxes do not like been treated as Strings 😁
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,346
Location
Sydney, Australia
Programming Experience
10+
The columns of the grid are irrelevant to JohnH's code, because it works with the bound DataTable exclusively. If your grid contains check boxes then that would generally correspond to Boolean values in the DataTable and, as you can see, that code does NOT treat Booleans as Strings. You need to debug the code and see what it's actually doing in your specific case. What is t when things go awry? If it's not Boolean then why not?
 

Slabs1960

Member
Joined
Mar 19, 2017
Messages
19
Programming Experience
10+
The columns of the grid are irrelevant to JohnH's code, because it works with the bound DataTable exclusively. If your grid contains check boxes then that would generally correspond to Boolean values in the DataTable and, as you can see, that code does NOT treat Booleans as Strings. You need to debug the code and see what it's actually doing in your specific case. What is t when things go awry? If it's not Boolean then why not?

I am aware that is is using the Data Table and not the DGV. I have a fairly good idea what he is doing.

I am an idiot.

After your comment, you got me thinking. I went and looked at my Data Table. The problem was Data Table Column was defined as a String and not a Boolean. I changed it to Boolean an it works great. Much neater solution the my previous one.

Thanks all :cool:


Final Code:
VB.NET:
    Public Sub ClearNullValues(ByRef _DataGridView As DataGridView)

        Dim table As DataTable = FrmConfigurarion.DsProjectData.tblConfigurationFile

        Try

            For Each row As DataRow In table.Rows
                For columnIndex = 0 To table.Columns.Count - 1
                    If row.IsNull(columnIndex) Then
                        Dim t = table.Columns(columnIndex).DataType
                        row(columnIndex) = If(t Is GetType(String), String.Empty, Activator.CreateInstance(t))
                    End If
                Next
            Next


        Catch ex As Exception
            MessageBox.Show(ex.Message, "Test For Null Values", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

    End Sub

Next challenge is to get the Data Table from the DGV. I currently have hard coded it.
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,438
Location
Norway
Programming Experience
10+
How did you bind it to the grid? If a DataTable was assigned to DataSource then you can CType/DirectCast the DataSource as DataTable. If a DataSet was bound then first get that, then the table from its Tables collection as named by DataMember. If a BindingSource was used get it from there, same applies with DataSource/DataMember here.

You should remove ByRef from method declaration, this method does need to not set a different DataGridView object to the callers variable.
 
Top Bottom