Deleting Columns in a DataTable based on null values.

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?

Answer 1

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);

Answer 2

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.

Answer 3

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!

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

How would I do that in vb? I will have 22 columns to start.

Answer 8

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