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