Remove Columns from Datatable

Question

User766478441 posted

Hi, I want to export the results of a database query to a csv file which works fine but I also need to remove certain columns from the Datatable before the csv file is exported.  Which columns that get removed depends on the value of a dropdownlist on the page named ddlReportType.  My problem is that it doesn't matter which value is selected in ddlReportType and all columns get exported regardless of the value in ddlReportType. Thanks

Protected Sub btnExcelExport_Click(sender As Object, e As EventArgs)

        Dim MyConnection As SqlConnection
        MyConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
        Dim cmd As New SqlCommand("p_Report_PCARebuttal", MyConnection)

        With cmd
            .CommandType = CommandType.StoredProcedure
            'Parameters here
            cmd.Parameters.AddWithValue("@PCAID", SqlDbType.VarChar).Value = ddlPCAID.SelectedValue
            cmd.Parameters.AddWithValue("@ReviewPeriodMonth", SqlDbType.VarChar).Value = ddlReviewPeriodMonth.SelectedValue
            cmd.Parameters.AddWithValue("@ReviewPeriodYear", SqlDbType.VarChar).Value = ddlReviewPeriodYear.SelectedValue
        End With

        Dim da As New SqlDataAdapter(cmd)
        Dim myDataTable As DataTable = New DataTable()

        For index As Integer = myDataTable.Columns.Count - 1 To 0 Step -1
            If ddlReportType.SelectedValue = "Internal" Then
                'Dont remove any columns
            ElseIf ddlReportType.SelectedValue = "Final" Then
                myDataTable.Columns.RemoveAt(21)
                myDataTable.Columns.RemoveAt(22)
                myDataTable.Columns.RemoveAt(23)
                myDataTable.Columns.RemoveAt(24)
            ElseIf ddlReportType.SelectedValue = "Preliminary" Then
                myDataTable.Columns.RemoveAt(19)
                myDataTable.Columns.RemoveAt(20)
                myDataTable.Columns.RemoveAt(21)
                myDataTable.Columns.RemoveAt(22)
                myDataTable.Columns.RemoveAt(23)
                myDataTable.Columns.RemoveAt(24)
            End If
        Next

        da.Fill(myDataTable)
            Try
                MyConnection.Open()
                Response.Clear()
                Response.ClearHeaders()
                Dim writer As New CsvWriter(Response.OutputStream, ","c, Encoding.Default)
                writer.WriteAll(myDataTable, True)
                writer.Close()

                Dim FileDate As String = Replace(FormatDateTime(Now(), DateFormat.ShortDate), "/", "")
                Response.AddHeader("Content-Disposition", "attachment;filename=Call_Monitoring_" & FileDate & ".csv")
                Response.ContentType = "application/vnd.ms-excel"
                Response.End()
            Finally
                If MyConnection.State <> ConnectionState.Closed Then MyConnection.Close()
                MyConnection.Dispose()
                MyConnection = Nothing
                myDataTable.Dispose()
                myDataTable = Nothing
            End Try
    End Sub

Answer 1

User1120430333 posted

Hi, I want to export the results of a database query to a csv file which works fine but I also need to remove certain columns from the Datatable before the csv file is exported. Which columns that get removed depends on the value of a dropdownlist on the page named ddlReportType. My problem is that it doesn't matter which value is selected in ddlReportType and all columns get exported regardless of the value in ddlReportType. Thanks

Well then,  the a datatable should be created based on selection with the appropriate columns in the datatable. Then a copy/map of data from old datatable to a new datatable with appropriate columns in the datatable.  That eliminates some kind of column remove logic that's not working. 

Answer 2

User1724605321 posted

Hi evanburen ,

You put the datatable remove column function inside a loop : `For index As Integer = myDataTable.Columns.Count - 1 To 0 Step -1` , that cause the problem.

Suppose there are 4 columns(A,B,C,D) in datatable . First loop : index =3 , if the `ddlReportType.SelectedValue` equals `Final` , then if :

myDataTable.Columns.RemoveAt(2)

Column C will be removed . Next loop , index=2 , then column D will be removed ....

So just put the datatable remove column function outside the loop , `myDataTable.Columns.RemoveAt` will remove entire column of the datatable .

Best Regards,

Nan Yu