Hi Andy,
Here's some code that should get you going. It grabs data from the SQL
database as a datareader and pushes it out as a CSV. No storage of the file
required. You should see Excel open with the data.
Does this help?
Ken
Microsoft MVP [ASP.NET]
Imports System.Data.SqlClient
Imports System.IO
Public Class csv
Inherits System.Web.UI.Page
Protected WithEvents Button1 As _
System.Web.UI.WebControls.Button
Protected WithEvents SqlConnection1 As _
System.Data.SqlClient.SqlConnection
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> _
Private Sub InitializeComponent()
Me.SqlConnection1 = _
New System.Data.SqlClient.SqlConnection
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = _
"data source=P4320;initial catalog=" & _
"Northwind;password="""";persist security info=Tru" & _
"e;user id=sa;workstation id=P4320;packet size=4096"
End Sub
Private Sub Page_Init _
(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles MyBase.Init
'CODEGEN: This method call is
'required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Button1_Click _
(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles Button1.Click
'Set the appropriate ContentType.
Dim filename As String = "orderdetails.csv"
Dim myCommand As New SqlCommand _
("select * from [order details] ", SqlConnection1)
myCommand.Connection.Open()
Dim myReader As SqlDataReader = _
myCommand.ExecuteReader _
(CommandBehavior.CloseConnection)
Dim i As Integer
Dim sb As New System.Text.StringBuilder
For i = 0 To myReader.FieldCount - 1
If i < (myReader.FieldCount - 1) Then
sb.Append(Chr(34) & myReader.GetName(i) & _
Chr(34) & ",")
Else
sb.Append(Chr(34) & myReader.GetName(i) & _
Chr(34) & vbCrLf)
End If
Next
While myReader.Read()
For i = 0 To myReader.FieldCount - 1
If i < (myReader.FieldCount - 1) Then
sb.Append(Chr(34) & _
myReader.GetValue(i).ToString & Chr(34) & ",")
Else
sb.Append(Chr(34) & _
myReader.GetValue(i).ToString & Chr(34) & vbCrLf)
End If
Next
End While
myReader.Close()
SqlConnection1.Close()
Response.ContentType = "Application/x-msexcel"
Response.AddHeader _
("content-disposition", "attachment; filename=""" & _
filename & """")
'Write the file directly to the HTTP output stream.
Response.Write(sb.ToString)
Response.End()
End Sub
End Class
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="csv.aspx.vb"
Inherits="p4320work.csv"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>csv</title>
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema"
content="
http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="FlowLayout">
<form id="Form1" method="post" runat="server">
<asp:Button id="Button1" runat="server" Text="Launch"></asp:Button>
</form>
</body>
</HTML>
