Microsoft KB Archive/308247

= HOW TO: Use ASP.NET to Query and Display Database Data in Excel by Using Visual Basic .NET =

Article ID: 308247

Article Last Modified on 5/21/2003

-

APPLIES TO


 * Microsoft ASP.NET 1.1
 * Microsoft ASP.NET 1.0
 * Microsoft Visual Basic .NET 2003 Standard Edition
 * Microsoft Visual Basic .NET 2002 Standard Edition

-



This article was previously published under Q308247



For a Microsoft Visual C# .NET version of this article, see 311194.

For additional information about how to perform this task by using Microsoft Active Server Pages, click the article number below to view the article in the Microsoft Knowledge Base:

189198 HOWTO: Use ASP to Query and Display Database Data in Excel

This article refers to the following Microsoft .NET Framework Class Library namespaces:
 * System.Data
 * System.Data.Common
 * System.Data.SqlClient
 * System.IO
 * System.Text

IN THIS TASK
SUMMARY
 * Build the Sample
 * Troubleshooting



SUMMARY
This step-by-step article describes how to build a tab-delimited text file dynamically from a database that can be opened in Microsoft Excel. The example in this article demonstrates how to connect to a Microsoft SQL Server database, to return a set of data from the Pubs database, and to create a tab-delimited text file with the data.

back to the top

Build the Sample
This article demonstrates how to create a sample ASP.NET Visual Basic page that is named ExcelVB.asp. This page connects to the SQL Server Pubs database and returns the information to a tab-delimited text file by using the FileStream object. A link to the new .xls file then appears on the page to demonstrate the output of the code.  Start Microsoft Visual Studio .NET. On the File menu, point to New, and then click Project. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click ASP.NET Web Application under Templates. In the Name box, type ExcelVBTest. In the Location box, click the appropriate server. If you are using the local server, you can leave the server name as &quot;http://localhost.&quot; Click OK. In the WebForm1.aspx file, drag a HyperLink control from the toolbox to the Web form. Right-click the WebForm1.aspx file, and then click View Code to display the code-behind page source.  Add the following code to the top of the code page: Imports System.Data Imports System.Data.Common Imports System.Data.SqlClient Imports System.IO Imports System.Text </li>  Add the following code to the Page_Load event: Dim i As Integer Dim strLine As String, filePath, fileName, fileExcel, link Dim objFileStream As FileStream Dim objStreamWriter As StreamWriter Dim nRandom As Random = New Random(DateTime.Now.Millisecond) Dim fs As Object, myFile As Object Dim cnn As SqlConnection = New SqlConnection(&quot;server=(local);database=pubs;&quot; & _                                            &quot;Integrated Security=SSPI&quot;)

'Create a pseudo-random file name. fileExcel = &quot;t&quot; & nRandom.Next.ToString & &quot;.xls&quot;

'Set a virtual folder to save the file. 'Make sure that you change the application name to match your folder. filePath = Server.MapPath(&quot;\ExcelVBTest&quot;) fileName = filePath & &quot;\&quot; & fileExcel

'Use FileStream to create the .xls file. objFileStream = New FileStream(fileName, FileMode.OpenOrCreate, FileAccess.Write) objStreamWriter = New StreamWriter(objFileStream)

'Use a DataReader to connect to the Pubs database. cnn.Open Dim sql As String = &quot;select au_id,au_lName,au_fname,phone,&quot; & _ &quot;address,city,state,zip,contract from authors&quot; Dim cmd As SqlCommand = New SqlCommand(sql, cnn) Dim dr As SqlDataReader dr = cmd.ExecuteReader

'Enumerate the field names and records that are used to build the file. For i = 0 To dr.FieldCount - 1 strLine = strLine & dr.GetName(i).ToString & Chr(9) Next

'Write the field name information to file. objStreamWriter.WriteLine(strLine) 'Reinitialize the string for data. strLine = &quot;&quot;

'Enumerate the database that is used to populate the file. While dr.Read For i = 0 To dr.FieldCount - 1 strLine = strLine & dr.GetValue(i) & Chr(9) Next objStreamWriter.WriteLine(strLine) strLine = &quot;&quot; End While

'Clean up. dr.Close cnn.Close objStreamWriter.Close objFileStream.Close

'Show a link to the Excel file. HyperLink1.Text = &quot;Open Excel&quot; HyperLink1.NavigateUrl = fileExcel </li> On the File menu, click Save All to save the project files.</li> On the Build menu in the Visual Studio .NET Integrated Development Environment, click Build to build the project.</li> In Visual Studio .NET Integrated Development Environment Solution Explorer, right-click the WebForm1.aspx Web form, and then click View in Browser to run the code.</li></ol>

back to the top

Troubleshooting
<ul> You must change the connection string in the sample code for your environment.</li> You may have to increase the permissions for the aspnet_wp process to allow the file to be written.For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

317012 INFO: Process and Request Identity in ASP.NET

</li></ul>

back to the top

Keywords: kbhowtomaster kbio kbdatabase KB308247

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.