locked
Binding to GridView onclick and export to csv RRS feed

  • Question

  • User-1664665373 posted

    Hello!

    I am binding different SqlDataSource (select statements and views) to GridView2 with button click methods. Each button loads the results of a different SQL statement into GridView2 and provides and row count...

            protected void Table1_Click(object sender, EventArgs e)
                {
                GridView2.DataSource = SqlDataSource1;
                GridView2.DataBind();
                RowCount.Text = GridView2.Rows.Count.ToString();
            }
    
                protected void Table2_Click(object sender, EventArgs e)
                {
                GridView2.DataSource = SqlDataSource2;
                GridView2.DataBind();
                RowCount.Text = GridView2.Rows.Count.ToString();
            }
    
                protected void Table3_Click(object sender, EventArgs e)
                {
                GridView2.DataSource = SqlDataSource3;
                GridView2.DataBind();
                RowCount.Text = GridView2.Rows.Count.ToString();
            }
    
                protected void Table4_Click(object sender, EventArgs e)
                {
                GridView2.DataSource = SqlDataSource4;
                GridView2.DataBind();
                RowCount.Text = GridView2.Rows.Count.ToString();
            }

    I also have a button to export content loaded into GridView2 to CSV...

            protected void ExportToCSV(object sender, EventArgs e)
            {
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition","attachment;filename=GridExport.csv");
                Response.Charset = "";
                Response.ContentType = "application/text";
    
                GridView2.AllowPaging = false;
                GridView2.DataBind();
    
                StringBuilder sb = new StringBuilder();
                for (int k = 0; k < GridView2.Columns.Count; k++)
                {
                    //add separator
                    sb.Append(GridView2.Columns[k].HeaderText + ',');
                }
                //append new line
                sb.Append("\r\n");
                for (int i = 0; i < GridView2.Rows.Count; i++)
                {
                    for (int k = 0; k < GridView2.Columns.Count; k++)
                    {
                        //add separator
                        sb.Append(GridView2.Rows[i].Cells[k].Text + ',');
                    }
                    //append new line
                    sb.Append("\r\n");
                }
                Response.Output.Write(sb.ToString());
                Response.Flush();
                Response.End();
            }

    My issue is, the export only works correctly when I have only 1 of the Table_Click methods in my .cs file. IE, if I comment out all but 'protected void Table1_Click', the ExportToCSV works and the data is there in the spreadsheet. When I uncomment the rest, the csv does export but it is empty.

    I am confused about this because the databind works when I click each button. In my mind, the corresponding SqlDataSource will be bound GridView2 when I click the corresponding button, and then the ExportToCSV button should just export whatever is bound a to GridView2 at that time...?

    The goal (and I am so close!) is to be able to bind SQL data to GridView2 with a button A, export that data to CSV, and have the ability to then bind a different SqlDataSouce to the same GridView2 with button B, and export that data, etc.

    Right now, the export button generates a csv download in the browser, but it is empty. Just a blank spreadsheet. If I comment out all but one of my Table_Click buttons, it works and the csv is populated.

    Any advice is much appreciated. Thank you!

    Monday, February 6, 2017 2:08 PM

Answers

  • User-707554951 posted

    Hi m0lochwalker,

    Could you show me your gridview2 ?

    Do you set AutoGenerateColumns to false in your gridview2 ? If you set it to true, the GridView2.Columns.Count in the ExportToCSV() is 0. That will cause the file is empty.

    And please remove the GridView2.DataBind() in the ExportToCSV(), it will cause GridView2.Rows.Count in the ExportToCSV() to be 0.

    For example:

    In aspx: 

     <form id="form1" runat="server">
            <div>
                <asp:Button ID="Button1" runat="server" OnClick="Table1_Click" />
                <asp:Button ID="Button2" runat="server" OnClick="Table2_Click" />
               <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="false">
                    <Columns>
                        <asp:BoundField DataField="City" HeaderText="City" />
                        <asp:BoundField DataField="Country" HeaderText="Country" />
                    </Columns>
                </asp:GridView>
                <asp:Label ID="RowCount" runat="server" />
                <asp:Button ID="Button5" runat="server" OnClick="ExportToCSV" Text="export" />
            </div>
            <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Northwind.mdf;Integrated Security=True;Connect Timeout=30" SelectCommand="SELECT  City,Country FROM [Employees]"></asp:SqlDataSource>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Northwind.mdf;Integrated Security=True;Connect Timeout=30" SelectCommand="SELECT  City,Country FROM [Customers]"></asp:SqlDataSource>
        </form>


    In behind code:

    protected void Table1_Click(object sender, EventArgs e)
            {
               
              
                GridView2.DataSource = SqlDataSource1;
                GridView2.DataBind();
                RowCount.Text = GridView2.Rows.Count.ToString();
            }
            protected void Table2_Click(object sender, EventArgs e)
            {
                GridView2.DataSource = SqlDataSource2;
                GridView2.DataBind();
                RowCount.Text = GridView2.Rows.Count.ToString();
            }
            protected void ExportToCSV(object sender, EventArgs e)
            {
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment;filename=GridExport.csv");
                Response.Charset = "";
                Response.ContentType = "application/text";
                GridView2.AllowPaging = false;
                //GridView2.DataBind();
                StringBuilder sb = new StringBuilder();
                for (int k = 0; k < GridView2.Columns.Count; k++)
                {
                    //add separator
                    sb.Append(GridView2.Columns[k].HeaderText + ',');
                }
                //append new line
                sb.Append("\r\n");
                for (int i = 0; i < GridView2.Rows.Count; i++)
                {
                    for (int k = 0; k < GridView2.Columns.Count; k++)
                    {
                        //add separator
                        sb.Append(GridView2.Rows[i].Cells[k].Text + ',');
                    }
                    //append new line
                    sb.Append("\r\n");
                }
                Response.Output.Write(sb.ToString());
                Response.Flush();
                Response.End();
            }

    Best Regards

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 7, 2017 6:42 AM

All replies

  • User-707554951 posted

    Hi m0lochwalker,

    Could you show me your gridview2 ?

    Do you set AutoGenerateColumns to false in your gridview2 ? If you set it to true, the GridView2.Columns.Count in the ExportToCSV() is 0. That will cause the file is empty.

    And please remove the GridView2.DataBind() in the ExportToCSV(), it will cause GridView2.Rows.Count in the ExportToCSV() to be 0.

    For example:

    In aspx: 

     <form id="form1" runat="server">
            <div>
                <asp:Button ID="Button1" runat="server" OnClick="Table1_Click" />
                <asp:Button ID="Button2" runat="server" OnClick="Table2_Click" />
               <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="false">
                    <Columns>
                        <asp:BoundField DataField="City" HeaderText="City" />
                        <asp:BoundField DataField="Country" HeaderText="Country" />
                    </Columns>
                </asp:GridView>
                <asp:Label ID="RowCount" runat="server" />
                <asp:Button ID="Button5" runat="server" OnClick="ExportToCSV" Text="export" />
            </div>
            <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Northwind.mdf;Integrated Security=True;Connect Timeout=30" SelectCommand="SELECT  City,Country FROM [Employees]"></asp:SqlDataSource>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Northwind.mdf;Integrated Security=True;Connect Timeout=30" SelectCommand="SELECT  City,Country FROM [Customers]"></asp:SqlDataSource>
        </form>


    In behind code:

    protected void Table1_Click(object sender, EventArgs e)
            {
               
              
                GridView2.DataSource = SqlDataSource1;
                GridView2.DataBind();
                RowCount.Text = GridView2.Rows.Count.ToString();
            }
            protected void Table2_Click(object sender, EventArgs e)
            {
                GridView2.DataSource = SqlDataSource2;
                GridView2.DataBind();
                RowCount.Text = GridView2.Rows.Count.ToString();
            }
            protected void ExportToCSV(object sender, EventArgs e)
            {
                Response.Clear();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment;filename=GridExport.csv");
                Response.Charset = "";
                Response.ContentType = "application/text";
                GridView2.AllowPaging = false;
                //GridView2.DataBind();
                StringBuilder sb = new StringBuilder();
                for (int k = 0; k < GridView2.Columns.Count; k++)
                {
                    //add separator
                    sb.Append(GridView2.Columns[k].HeaderText + ',');
                }
                //append new line
                sb.Append("\r\n");
                for (int i = 0; i < GridView2.Rows.Count; i++)
                {
                    for (int k = 0; k < GridView2.Columns.Count; k++)
                    {
                        //add separator
                        sb.Append(GridView2.Rows[i].Cells[k].Text + ',');
                    }
                    //append new line
                    sb.Append("\r\n");
                }
                Response.Output.Write(sb.ToString());
                Response.Flush();
                Response.End();
            }

    Best Regards

    Cathy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 7, 2017 6:42 AM
  • User-1664665373 posted

    Hello Cathy!

    Thank you! From your suggestions I see that I need to add columns to my GridView2. At the time I posted, it looked like this..

    <asp:GridView ID="GridView2" runat="server" CssClass="Grid">

    ...and that was it. I thought that because it worked when binding datasource directly to it, I did not need to add columns since I was binding the datasource with...

            protected void Table1_Click(object sender, EventArgs e)
                {
                GridView2.DataSource = SqlDataSource1;
                GridView2.DataBind();
                RowCount.Text = GridView2.Rows.Count.ToString();
            }

    I guess when I bind that way, I have to add BoundColumns to my gridview. 

    The BoundFields should get me going. 

    Thank you!!

    Tuesday, February 7, 2017 1:30 PM