Member Avatar for Yellowdog428

Perhaps someone can help me here, I have a method that I am trying to accomplish two things

1. Retrieve a table from a stored procedure passing it one variable
2. Downloading that table in csv format

I am not sure why this method is not working and I am sure it is just a simple syntax error or my logic may be a little funky somewhere so any help would be appreciated.

protected void btn_export_Click(object sender, EventArgs e)
        {
            string filename = this.drpDealers.Text + "_mailfile";

            SqlConnection con = new SqlConnection(connectionString);
            DataTable tblMail = new DataTable();

            SqlCommand cmd = new SqlCommand("web_pull_mail_list", con) { CommandType = CommandType.StoredProcedure };
            cmd.Parameters.AddWithValue("@dealer_id", (Convert.ToInt32(this.drpDealers.Text)));

            cmd.Connection = con;

            con.Open();
            SqlDataAdapter adapt = new SqlDataAdapter(cmd);
            adapt.Fill(tblMail);
            con.Close();


            HttpContext context = HttpContext.Current;
            context.Response.Clear();

            foreach (DataColumn column in tblMail.Columns)
            {
                context.Response.Write(column.ColumnName + ",");
            }

            context.Response.Write(Environment.NewLine);
            foreach (DataRow row in tblMail.Rows)
            {
                for (int i = 0; i < tblMail.Columns.Count; i++)
                {
                    context.Response.Write(row[i].ToString().Replace("," ,string.Empty) + ",");
                }
                context.Response.Write(Environment.NewLine);
            }

            context.Response.ContentType = "text/csv";
            context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + ".csv");
            context.Response.End();
        }

Thanks in advance for any help

  • 2 Contributors
  • 2 Replies
  • 122 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by dnanetwork

Dani AI

Generated

A few practical fixes and checks to make this export robust and CSV-compliant.

The common problems in s example are: headers set after writing the body, data-destructive comma-stripping instead of proper CSV quoting, and no DBNull handling. Also confirm the dealer id is coming from the expected control (use SelectedValue for an ID, not Text). For large result sets prefer streaming with a reader instead of filling a big DataTable. Use using for connections/commands to ensure disposal and avoid Response.End() (it throws a ThreadAbortException; prefer CompleteRequest()).

Correct CSV escaping and proper HTTP headers first:

  • Quote fields that contain commas, quotes or newlines and double any interior quotes per RFC 4180 (RFC 4180).
  • Set ContentType/headers and encoding before writing any bytes; add a UTF-8 BOM if Excel is the consumer.

Example helper and header pattern (illustrative):

string EscapeCsv(string s)
{
    if (s == null) return "";
    s = s.Replace("\"", "\"\"");
    bool quote = s.IndexOfAny(new[] {',','"','\r','\n'}) >= 0;
    return quote ? "\"" + s + "\"" : s;
}
context.Response.Clear();
context.Response.ContentType = "text/csv; charset=utf-8";
context.Response.ContentEncoding = System.Text.Encoding.UTF8;
context.Response.AppendHeader("Content-Disposition", $"attachment; filename=\"{fileName}.csv\"");
context.Response.BinaryWrite(new byte[] { 0xEF, 0xBB, 0xBF }); // UTF-8 BOM for Excel
// stream rows here using EscapeCsv(...)

If switching to streaming, execute the stored proc with a SqlDataReader and write each row immediately to the response to save memory. Avoid AddWithValue for production parameters; prefer typed parameters (see SqlParameterCollection.AddWithValue for pitfalls).

Note on s suggestion: emitting an HTML table with application/vnd.ms-excel will open in Excel, but that is not a true CSV and can cause parsing surprises for non-Excel consumers. Quick checklist: sanitize filename characters, handle DBNulls, set headers/encoding before output, and test the HTTP response with browser dev tools or Fiddler when downloads fail.

Recommended Answers

All 2 Replies

Member Avatar for dnanetwork 
Private Sub ConvertToExcel(ByRef r_dvReport As DataView)
    'first clean up the response.object
    Response.Clear()

    Response.Charset = ""

    'set the response mime type for excel
    Response.ContentType = "application/vnd.ms-excel"

    'create a string writer
    Dim stringWrite As New System.IO.StringWriter

    'create an htmltextwriter which uses the stringwriter
    Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)

    'instantiate a datagrid - used for output
    Dim dgExcelOutput As New DataGrid

    'set the output datagrid datasource to the dataset associated
    ' with the data grid being converted
    dgExcelOutput.DataSource = r_dvReport

    'bind the datagrid
    dgExcelOutput.DataBind()

    'tell the datagrid to render itself to htmltextwriter
    dgExcelOutput.RenderControl(htmlWrite)

    'all that's left is to output the html
    Response.Write(stringWrite.ToString)

    Response.End()
End Sub
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.