none
Deleting Columns in a DataTable based on null values. RRS feed

  • Question

  • I have figured out how to delete columns in a datatable, but I can't figure out how to delete columns if the cells in them are null.

    Background:

    I have a datagridview for which I am dumping the data into a datatable, then I am modifying the datatable to prevent possible database modification. There will only be one row. I need to go through that row and delete any columns that are null or not checked. Is there a way to do this?

    Monday, July 30, 2012 2:57 PM

Answers

  • I figured it out.

     Me.TableAdapter.(Me.DataSet.tablename, TextBox1.Text)
            Dim CountInteger As Integer = 0
            Dim IndexInteger As Integer = 22
            Try
                Do Until CountInteger = IndexInteger
                    If IsDBNull(DataGridView.Rows(0).Cells(CountInteger).Value) Then
                        DataGridView.Columns.RemoveAt(CountInteger)
                        IndexInteger -= 1
                    ElseIf TypeOf DataGridView.Rows(0).Cells(CountInteger).Value Is String Then
                        If DataGridView.Rows(0).Cells(CountInteger).Value = "" Then
                            DataGridView.Columns.RemoveAt(CountInteger)
                            IndexInteger -= 1
                        Else
                            CountInteger += 1
                        End If
                    ElseIf TypeOf DataGridView.Rows(0).Cells(CountInteger).Value Is Boolean Then
                        If DataGridView.Rows(0).Cells(CountInteger).Value = False Then
                            DataGridView.Columns.RemoveAt(CountInteger)
                            IndexInteger -= 1
                        Else
                            CountInteger += 1
                        End If
                    Else
                        CountInteger += 1
                    End If
    
                    If CountInteger > IndexInteger Then
                        CountInteger = IndexInteger
                    End If
                Loop
            Catch ex As Exception
                MessageBox.Show("An error occured while querying your information.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub

    • Marked as answer by lj09IT Thursday, August 2, 2012 2:43 PM
    Wednesday, August 1, 2012 8:36 PM

All replies

  • You can use the DataTable.Columns property that exposes the DataColumnCollection that table is using and its remove methods. For example:

    table.Columns.RemoveAt(0);
    
    table.Columns.Remove("Name");
    
    DataColumn col = table.Columns[0];
    table.Columns.Remove(col);

    • Edited by MasaSam Monday, July 30, 2012 5:24 PM
    • Proposed as answer by Alexander Sun Wednesday, August 1, 2012 6:25 AM
    Monday, July 30, 2012 5:23 PM
  • The problem is that I don't know which columns will be null. I just know that there will be null columns and I also know that there will only ever be one row.
    Wednesday, August 1, 2012 12:11 PM
  • Find the Null values column in the table like this and do your operation inside the condition as per your requirement.

    public static bool HasNull(this DataTable table) 
    {     
    foreach (DataColumn column in table.Columns)     
    {         
       if (table.Rows.OfType<DataRow>().Any(r => r.IsNull  (column)))             
         return true;     
    }      return false; 
    }


    With Thanks and Regards
    Sambath Raj.C
    click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you
    Happy Programming!

    Wednesday, August 1, 2012 1:42 PM
  • The problem is that I don't know which columns will be null. I just know that there will be null columns and I also know that there will only ever be one row.

    Then you can get each DataRow and check columns for null values with IsNull method. Something that Sambath has provided helper method. Of course removing columns based on null might become more problematic if for example in row 1 column 1 has null value but in row 2 column 1 has no null or vice versa.
    Wednesday, August 1, 2012 6:08 PM
  • As I have said before, there will not be a problem with multiple rows. However, I have tried

    For Each Column in Dt.columns

       If IsDBNull(column) Then

          Dt.columns.RemoveAt(column)

       End If

    Next

    The problem then becomes an enumeration error in which the column count has changed.

    Wednesday, August 1, 2012 6:39 PM
  • That is expected as you can not change the collection in the middle of the for each iteration. You must use for loop to remove column at index or store references to all the columns you are about to change in some other collection and remove them after null check detection.

    Wednesday, August 1, 2012 7:01 PM
  • How would I do that in vb? I will have 22 columns to start.
    Wednesday, August 1, 2012 7:03 PM
  • I figured it out.

     Me.TableAdapter.(Me.DataSet.tablename, TextBox1.Text)
            Dim CountInteger As Integer = 0
            Dim IndexInteger As Integer = 22
            Try
                Do Until CountInteger = IndexInteger
                    If IsDBNull(DataGridView.Rows(0).Cells(CountInteger).Value) Then
                        DataGridView.Columns.RemoveAt(CountInteger)
                        IndexInteger -= 1
                    ElseIf TypeOf DataGridView.Rows(0).Cells(CountInteger).Value Is String Then
                        If DataGridView.Rows(0).Cells(CountInteger).Value = "" Then
                            DataGridView.Columns.RemoveAt(CountInteger)
                            IndexInteger -= 1
                        Else
                            CountInteger += 1
                        End If
                    ElseIf TypeOf DataGridView.Rows(0).Cells(CountInteger).Value Is Boolean Then
                        If DataGridView.Rows(0).Cells(CountInteger).Value = False Then
                            DataGridView.Columns.RemoveAt(CountInteger)
                            IndexInteger -= 1
                        Else
                            CountInteger += 1
                        End If
                    Else
                        CountInteger += 1
                    End If
    
                    If CountInteger > IndexInteger Then
                        CountInteger = IndexInteger
                    End If
                Loop
            Catch ex As Exception
                MessageBox.Show("An error occured while querying your information.", "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Sub

    • Marked as answer by lj09IT Thursday, August 2, 2012 2:43 PM
    Wednesday, August 1, 2012 8:36 PM