none
Check for empty cells in datagridview RRS feed

  • Question

  • Hello,

    Found a code to check for an empty's cell in a datagridview.

    i works i think.

    But when you hit backspace in a cell on the datagridview and check i get an error.

      Dim isEmpty As Boolean
            isEmpty = True
            For Each row As DataGridViewRow In DataGridView1.Rows
                For Each cell As DataGridViewCell In row.Cells
                    If Not String.IsNullOrEmpty(cell.Value) Then
                        ' Check if the string only consists of spaces
                        If Not String.IsNullOrEmpty(Trim(cell.Value.ToString())) Then
                            isEmpty = False
                        Else
                            MsgBox("Empty Cell !")
                            Exit For
                        End If
                    End If
                Next
            Next
    

    The error

    Message = The conversion of type DBNull to type String is invalid.
    Is there a way to avoid this error or does the code needs some ajustments ?

    Tuesday, September 11, 2018 7:30 PM

Answers

  • Thanks Les,

    That is what i am looking for, is there a way to get the Collumn name where the empty cell is ?

    Hi

    Here is the same code I posted above, but, with Column Name instead of Index.

    Option Strict On
    Option Explicit On
    Public Class Form1
      Dim myTable As New DataTable
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        With myTable
          .Columns.Add("One")
          .Columns.Add("Two")
          .Columns.Add("Three")
          .Columns.Add("Four")
    
          .Rows.Add(1, 2, 3, Nothing)
          .Rows.Add(1, Nothing, 3, 4)
          .Rows.Add(1, 2, Nothing, 4)
          .Rows.Add(1, Nothing, 3, 4)
          .Rows.Add(Nothing, 2, 3, 4)
        End With
        DataGridView1.DataSource = myTable
      End Sub
    
      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim lst As New List(Of Point)
        For Each r As DataGridViewRow In DataGridView1.Rows
          For Each c As DataGridViewCell In r.Cells
            If Not c.RowIndex = DataGridView1.NewRowIndex AndAlso Trim(c.Value.ToString) = Nothing Then
              lst.Add(New Point(c.ColumnIndex, c.RowIndex))
            End If
          Next
        Next
        If lst.Count > 0 Then
          Dim s As String = "Blank Cells (or SPACES only)"
          For Each p As Point In lst
            s &= vbCrLf & "Coll: " & DataGridView1.Columns(p.X).Name.ToString & "   Row: " & p.Y.ToString
          Next
          MessageBox.Show(s)
        End If
      End Sub
    End Class
    


    Regards Les, Livingston, Scotland

    • Marked as answer by KeesBlunder Wednesday, September 12, 2018 2:13 PM
    Wednesday, September 12, 2018 10:31 AM

All replies

  • You can check if cell.Value Is Nothing

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, September 11, 2018 7:41 PM
    Moderator
  • Hi

    Here is some code that seems to work OK - I can't get your exception to occur.

    Stand alone example: Click Button to check for empty cells.

    ' Form1 with DataGridView1
    ' and Button1
    Option Strict On
    Option Explicit On
    Public Class Form1
      Dim myTable As New DataTable
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        With myTable
          .Columns.Add("One")
          .Columns.Add("Two")
          .Columns.Add("Three")
          .Columns.Add("Four")
    
          .Rows.Add(1, 2, 3, Nothing)
          .Rows.Add(1, Nothing, 3, 4)
          .Rows.Add(1, 2, Nothing, 4)
          .Rows.Add(1, Nothing, 3, 4)
          .Rows.Add(Nothing, 2, 3, 4)
        End With
        DataGridView1.DataSource = myTable
      End Sub
      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim lst As New List(Of Point)
        For Each r As DataGridViewRow In DataGridView1.Rows
          For Each c As DataGridViewCell In r.Cells
            If Not c.RowIndex = DataGridView1.NewRowIndex AndAlso Trim(c.Value.ToString) = Nothing Then
              lst.Add(New Point(c.ColumnIndex, c.RowIndex))
            End If
          Next
        Next
        If lst.Count > 0 Then
          Dim s As String = "Blank Cells (or SPACES only)"
          For Each p As Point In lst
            s &= vbCrLf & "Coll: " & p.X.ToString & "   Row: " & p.Y.ToString
          Next
          MessageBox.Show(s)
        End If
      End Sub
    End Class


    Regards Les, Livingston, Scotland


    • Edited by leshay Tuesday, September 11, 2018 8:21 PM
    Tuesday, September 11, 2018 8:16 PM
  • Perhaps check for DBNull.Value

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim dt As New DataTable
            dt.Columns.Add(New DataColumn() With {.ColumnName = "Id", .DataType = GetType(Integer)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "FirstName", .DataType = GetType(String)})
            dt.Columns.Add(New DataColumn() With {.ColumnName = "LastName", .DataType = GetType(String)})
    
            dt.Rows.Add(New Object() {Nothing, "Karen", "Payne"})
            dt.Rows.Add(New Object() {2, Nothing, "Smith"})
            dt.Rows.Add(New Object() {3, "Mary", Nothing})
            DataGridView1.DataSource = dt
        End Sub
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim sb As New Text.StringBuilder
            For Each rw As DataGridViewRow In DataGridView1.Rows
                For index As Integer = 0 To rw.Cells.Count - 1
                    If rw.Cells(index).Value Is Nothing OrElse rw.Cells(index).Value Is DBNull.Value _
                                            OrElse String.IsNullOrWhiteSpace(rw.Cells(index).Value.ToString()) Then
                        If Not rw.IsNewRow Then
                            sb.AppendLine($"Row: {rw.Index} Col: {index}")
                        End If
                    End If
                Next
            Next
            If sb.Length > 0 Then
                MessageBox.Show(sb.ToString())
            End If
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, September 11, 2018 11:09 PM
    Moderator
  • Hi,

    When getting the value of a Cell in the datagridview, do not use tostring(), but use convert.Tostring().

    Otherwise, it will  give an error.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 12, 2018 2:26 AM
  • Thanks Karen,

    My programm is in framework 3.5 

    NullOrWhiteSpace is not working 
    Wednesday, September 12, 2018 9:42 AM
  • Thanks Les,

    That is what i am looking for, is there a way to get the Collumn name where the empty cell is ?

    Wednesday, September 12, 2018 9:48 AM
  • Thanks Les,

    That is what i am looking for, is there a way to get the Collumn name where the empty cell is ?

    Hi

    Here is the same code I posted above, but, with Column Name instead of Index.

    Option Strict On
    Option Explicit On
    Public Class Form1
      Dim myTable As New DataTable
      Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        With myTable
          .Columns.Add("One")
          .Columns.Add("Two")
          .Columns.Add("Three")
          .Columns.Add("Four")
    
          .Rows.Add(1, 2, 3, Nothing)
          .Rows.Add(1, Nothing, 3, 4)
          .Rows.Add(1, 2, Nothing, 4)
          .Rows.Add(1, Nothing, 3, 4)
          .Rows.Add(Nothing, 2, 3, 4)
        End With
        DataGridView1.DataSource = myTable
      End Sub
    
      Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim lst As New List(Of Point)
        For Each r As DataGridViewRow In DataGridView1.Rows
          For Each c As DataGridViewCell In r.Cells
            If Not c.RowIndex = DataGridView1.NewRowIndex AndAlso Trim(c.Value.ToString) = Nothing Then
              lst.Add(New Point(c.ColumnIndex, c.RowIndex))
            End If
          Next
        Next
        If lst.Count > 0 Then
          Dim s As String = "Blank Cells (or SPACES only)"
          For Each p As Point In lst
            s &= vbCrLf & "Coll: " & DataGridView1.Columns(p.X).Name.ToString & "   Row: " & p.Y.ToString
          Next
          MessageBox.Show(s)
        End If
      End Sub
    End Class
    


    Regards Les, Livingston, Scotland

    • Marked as answer by KeesBlunder Wednesday, September 12, 2018 2:13 PM
    Wednesday, September 12, 2018 10:31 AM
  • Thanks Karen,

    My programm is in framework 3.5 

    NullOrWhiteSpace is not working 

    Not a problem, see revised code.

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim sb As New Text.StringBuilder
        For Each rw As DataGridViewRow In DataGridView1.Rows
            For index As Integer = 0 To rw.Cells.Count - 1
                If rw.Cells(index).Value Is Nothing OrElse rw.Cells(index).Value Is DBNull.Value _
                    OrElse String.IsNullOrEmpty(rw.Cells(index).Value.ToString()) Then
                    If Not rw.IsNewRow Then
                        sb.AppendLine($"Row: {rw.Index} Col: {index}")
                    End If
                End If
            Next
        Next
        If sb.Length > 0 Then
            MessageBox.Show(sb.ToString())
        End If
    End Sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, September 12, 2018 11:07 AM
    Moderator
  • Check the value for DBNull.value. 

            For Each DGVRow As DataGridViewRow In DataGridView1.Rows
                For Each DGVCell As DataGridViewCell In DGVRow.Cells
                    If DGVCell.Value Is DBNull.Value Then
                        DGVCell.Style.BackColor = Color.Yellow
                    End If
                Next
            Next


    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi

    Wednesday, September 12, 2018 1:29 PM
  • Thanks Les,

    Just what i need.

    Wednesday, September 12, 2018 2:14 PM