locked
Remove Columns from Datatable RRS feed

  • 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

    Wednesday, May 16, 2018 12:23 PM

All replies

  • 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. 

    Wednesday, May 16, 2018 4:53 PM
  • 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

    Thursday, May 17, 2018 3:12 AM