Removing empty columns, also changes row count with code, Why?

I'm using below code to remove empty columns from datatable before it's databind to datagridview. When I run the sql query,  I have 14135 rows and also steping thru the code it shows 14135 for defaultview.rowfilter when I get thru below code once, but once it hits next and goes thru it the second time to check the second column, it changes the defaultview.rowfilter to 858. Why does it do that, because it only shows 858 rows on datagrid and I need it to show 14135?

Dim Col As DataColumn
For i As Integer = dTable.Columns.Count - 1 to 0 step -1
Col = dTable.Columns(i)
If col.DataType Is GetType("String") Then
    dTable.DefaultView.RowFilter = Col.ColumnName & "<>''"
    If dTable.DefaultView.Count = 0 Then
      dTable.Columns.Remove(col)
   End If
End If
Next
jeremyzitAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
CodeCruiserConnect With a Mentor Commented:
It would be

dt.DefaultView.RowFilter = ""

And why you have

dv = New DataView(dt)

?
0
 
CodeCruiserCommented:
It applies a filter to the dataview (empty column filter) and dataview then only contains rows where that column is empty. Datatable still contains all the rows. So set the datatable as the datasource not dataview.
0
 
nepaluzCommented:
Or remove the filter on the defaultview AFTER running your code to show all the rows.
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
jeremyzitAuthor Commented:
I am setting the datatable as the datasource. How do you remove the filter on the defaultview after running the code.

        Dim Col As DataColumn
        For i As Integer = dt.Columns.Count - 1 To 0 Step -1
            Col = dt.Columns(i)
            If Col.DataType Is GetType(String) Then
                d = dt.DefaultView.Count
                dt.DefaultView.RowFilter = Col.ColumnName & "<>''"
                If dt.DefaultView.Count = 0 Then
                    dt.Columns.Remove(Col)
                End If
            End If
        Next

        dv = New DataView(dt)
        datagrid.DataSource = dt
        DataBind()
0
 
nepaluzConnect With a Mentor Commented:
dt.DefaultView.RowFilter = Nothing
0
 
jeremyzitAuthor Commented:
Do you not need that on web form? "dv = New DataView(dt)" I've always put that in my code, but maybe it's only needed on windows form datagrid.
0
 
CodeCruiserCommented:
Its not needed anywhere unless you need to filter and assign the filtered data to a grid or other control.
0
All Courses

From novice to tech pro — start learning today.