asked on

Remove empty columns from DataTable before binding to gridview

Hi,

I am creating a DT with the below columns. Does someone know how I can iterate through each one and remove the column if all the rows are empty?

AS400DT.Columns.Add(New DataColumn("Date", Type.GetType("System.String")))
        AS400DT.Columns.Add(New DataColumn("IN", Type.GetType("System.String")))
        AS400DT.Columns.Add(New DataColumn("OUT", Type.GetType("System.String")))
        AS400DT.Columns.Add(New DataColumn("Reg. Hours Worked", Type.GetType("System.String")))
        AS400DT.Columns.Add(New DataColumn("Vacation", Type.GetType("System.String")))
        AS400DT.Columns.Add(New DataColumn("Absent", Type.GetType("System.String")))
        AS400DT.Columns.Add(New DataColumn("Overtime", Type.GetType("System.String")))
        AS400DT.Columns.Add(New DataColumn("Overtime X2", Type.GetType("System.String")))
        AS400DT.Columns.Add(New DataColumn("Late", Type.GetType("System.String")))
        AS400DT.Columns.Add(New DataColumn("Early Quit", Type.GetType("System.String")))
        AS400DT.Columns.Add(New DataColumn("Holiday", Type.GetType("System.String")))
        AS400DT.Columns.Add(New DataColumn("MULA", Type.GetType("System.String")))
        AS400DT.Columns.Add(New DataColumn("VULA", Type.GetType("System.String")))
        AS400DT.Columns.Add(New DataColumn("Perfect Attendance", Type.GetType("System.String")))

Open in new window

silemone

what language are you using?

silemone

what you can do is when you are creating the table:

DataTable MyTab = new Table();

you can check for empty rows while its being built...

if you're binding data, you have to override the bindData method...

and do something like this:

bool hasdata = false;

check everycolumn and if data happens for just one row, then hasData = true;
if hasData == true then you add it to the row to the dataTable...

ITHelper80

ASKER

Im writing in VB.NET

silemone

the other thing you could do is keep the rows and create a dataview that filters out empty rows...and only shows rows that has data...

silemone

this link should do help

http://mikemalloy.wordpress.com/2007/02/09/remove-empty-columns-in-datatable/

ITHelper80

ASKER

Thanks I actually tried that before posting here but couldnt get it to work.

ASKER CERTIFIED SOLUTION

silemone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

silemone

did you convert it?

which converter did you use?

what was the errors?

ITHelper80

ASKER

My converter is the one at developerfusion.com but its didnt convert the same as yours. Which one did you use?

If the dataview is the best method, can you provide an example of how to use it?

silemone

the converter

http://converter.telerik.com/

did the code work for you?

ROMA CHAUHAN

I have written a very simple function which will remove the columns not having value in any row.

With this function you can get the result in minimum number of loops. I have attached the function below just pass your table in it, it will return the resulted table to you.

Private Function RemoveBlankColumns(ByVal dtSource As DataTable) As DataTable
        Dim arrColToRemove As New ArrayList 
        'Loop for all the columns of source table
        For Each dtCol As DataColumn In dtSource.Columns
            'Get the rows not having any blank record in the column
            Dim dtRow As DataRow() = dtSource.Select("Convert(" & dtCol.ColumnName & ",'System.String') <> ''")
            If dtRow.Length = 0 Then
                arrColToRemove.Add(dtCol.ColumnName)
            End If
        Next
        For Each ColName As String In arrColToRemove
            dtSource.Columns.Remove(ColName)
        Next
        Return dtSource
    End Function

Open in new window