Question Data to CSV File Using StreamWriter

[billsut]

I'm trying to put data from the database into a CSV file using StreamWriter. The problem seems to be in two lines (Bolded) that prompt the user where to put the file. The file that gets created is the rendered html from the page and not the data. If I hard code a path instead of prompting, the file is created successfully. Here is the code:

Sub DownloadResults(sender As Object, e As CommandEventArgs)

Dim strGetQuestions As String = ""
Dim strGetSession As String = ""
Dim strSelected As String = ""
Dim i As Integer = 0

Try
strSelected = e.CommandArgument

If strSelected = "Number" then
strGetQuestions = "Select questionid,fieldtype " & _
"from tbl_questions " & _
"where surveyid = '" & SurveyID.Value & "' " & _
"and fieldtype not in (6,7,8,10) " & _
"order by QOrder"
Else
strGetQuestions = "Select questionid,question,fieldtype " & _
"from tbl_questions " & _
"where surveyid = '" & SurveyID.Value & "' " & _
"and fieldtype not in (6,7,8,10) " & _
"order by QOrder"
End If

openConnection()

dim daGetQuestions as SqlDataAdapter = new SqlDataAdapter(strGetQuestions, objConnection)
dim dsGetQuestions as DataSet = new DataSet

daGetQuestions.Fill(dsGetQuestions, "Questions")

closeConnection()

strGetSession = "select sessionid,convert(varchar(20),dateadded,100) " & _
"from tbl_session where surveyid = " & SurveyID.Value & " order by dateadded"

openConnection()

Dim daGetSession As SqlDataAdapter = New SqlDataAdapter(strGetSession, objConnection)
Dim dsGetSession As DataSet = New DataSet

daGetSession.Fill(dsGetSession, "Sessions")

closeConnection()

Dim filename As String = "Results_" & cstr(Mid(System.Guid.NewGuid.ToString(),1,8)) & ".csv"

Response.ContentType = "text/csv"
Response.AddHeader("Content-Disposition", "attachment;filename=" & filename)
Dim sw1 As StreamWriter = File.CreateText(filename)

sw1.Write("""Date Stamp""")

If strSelected = "Number" then
For i = 0 to dsGetQuestions.Tables(0).Rows.Count - 1
sw1.Write(",")
sw1.Write("""" & i & """")
Next
Else
For i = 0 to dsGetQuestions.Tables(0).Rows.Count - 1
sw1.Write(",")
sw1.Write("""" & dsGetQuestions.Tables(0).Rows(i).Item(1).ToString & """")
Next
End If

sw1.WriteLine()

For i = 0 to dsGetSession.Tables(0).Rows.Count - 1
Dim strSQL As String = "select a.answer " & _
"from tbl_questions q left join tbl_answers a on q.questionid = a.questionid " & _
"and q.surveyid = " & SurveyID.Value & _
" where a.sessionid = " & dsGetSession.Tables(0).Rows(i).Item(0).ToString & _
" order by q.QOrder"

openConnection()

Dim cmdNewRow As New SqlCommand(strSQL, objConnection)
Dim drNewRow As SqlDataReader = cmdNewRow.ExecuteReader
If drNewRow.HasRows then
sw1.Write("" & dsGetSession.Tables(0).Rows(i).Item(1).ToString & "")
End If
While drNewRow.Read
sw1.Write(",")
sw1.Write("""" & drNewRow("answer") & """")
End While
If drNewRow.HasRows then
sw1.WriteLine()
End If

closeConnection()

Next

sw1.Close()

Catch ex As Exception
SaveError("DownloadResults", strGetQuestions, ex.ToString)
Response.Write("<script language='javascript'>")
Response.Write("alert('Download Results Failed');")
Response.Write("</scr" & "ipt>")
End Try
End Sub

 

[MattP]

Try:

Response.ContentType = "text/csv"
Response.AddHeader("Content-Disposition", "attachment; filename=""" & filename & """")

 

[billsut]

Thanks for the reply Matt but I'm still getting html in the excel spreadsheet instead of the data from my data tables.

 

[JohnH]

You have to end the response:

Response.End()

Your current code just writes to a file at server without returning anything to client, so before you end response you have to return the file:

Response.WriteFile(filename)

As an alternative you don't need to first write a file at server and then write that file back to client, you can write to the response stream directly:

Dim writer As New IO.StreamWriter(Response.OutputStream)

The filename part of the content header is just a suggested filename for client to use for the returned content, there don't need to be an actual file at server end.

Don't do this:

If strSelected = "Number" then
strGetQuestions = "Select questionid,fieldtype " & _
"from tbl_questions " & _
"where surveyid = '" & SurveyID.Value & "' " & _
"and fieldtype not in (6,7,8,10) " & _
"order by QOrder"
Else
strGetQuestions = "Select questionid,question,fieldtype " & _
"from tbl_questions " & _
"where surveyid = '" & SurveyID.Value & "' " & _
"and fieldtype not in (6,7,8,10) " & _
"order by QOrder"
End If

Learn how to use parameters in query Even if no injection is possible here, nor other related parameter problems, it is a good coding practise and will improve db server performance.

You don't need to open and close db connection when filling with SqlDataAdapter, it will handle this by itself.

 

[billsut]

Thanks for the response John. I had already tried some of what you suggested. I tried the rest but am still getting html in the spreadsheet. I found several other people who have had the same problem and the only ay they solved it was to hard code a path.

 

[MattP]

Here's some code that I use to download files. I have a page called download.aspx with this as it's code behind.

Imports System.IO

    Partial Class Agreements_FileDownload
        Inherits System.Web.UI.Page

    Protected Sub Page_Load _
        (ByVal sender As Object, ByVal e As System.EventArgs) _
        Handles Me.Load

        Dim filepath As String = Request.Params("file")
        If Not filepath Is Nothing Then
            If File.Exists(filepath) Then
                Dim filename As String = Path.GetFileName(filepath)
                Response.Clear()
                Response.ContentType = "application/octet-stream"
                Response.AddHeader("Content-Disposition", _
                  "attachment; filename=""" & filename & """")
                Response.Flush()
                Response.WriteFile(filepath)
            End If
        End If
    End Sub
    End Class

Here's the code I use to bind the links in my datagrid and direct them to the download.aspx page. The file parameter ends up looking like \\server\path\filename.ext

    Protected Sub dgFiles_ItemDataBound _
        (ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) _
        Handles dgFiles.ItemDataBound

        If (e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem) Then
            Dim hl As HyperLink = e.Item.Cells(0).Controls(0)
            hl.NavigateUrl = "~\Agreements\filedownload.aspx?file=" + DataBinder.Eval(e.Item.DataItem, "FullName").ToString()
        End If
    End Sub