You are currently offline, waiting for your internet to reconnect

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

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
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.
  1. Start Microsoft Visual Studio .NET.
  2. On the File menu, point to New, and then click Project.
  3. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click ASP.NET Web Application under Templates.
  4. 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 "http://localhost." Click OK.
  5. In the WebForm1.aspx file, drag a HyperLink control from the toolbox to the Web form.
  6. Right-click the WebForm1.aspx file, and then click View Code to display the code-behind page source.
  7. Add the following code to the top of the code page:
    Imports System.DataImports System.Data.CommonImports System.Data.SqlClientImports System.IOImports System.Text					
  8. Add the following code to the Page_Load event:
    Dim i As IntegerDim strLine As String, filePath, fileName, fileExcel, linkDim objFileStream As FileStreamDim objStreamWriter As StreamWriterDim nRandom As Random = New Random(DateTime.Now.Millisecond)Dim fs As Object, myFile As ObjectDim cnn As SqlConnection = New SqlConnection("server=(local);database=pubs;" & _                                             "Integrated Security=SSPI")'Create a pseudo-random file name.fileExcel = "t" & nRandom.Next().ToString() & ".xls"'Set a virtual folder to save the file.'Make sure that you change the application name to match your folder.filePath = Server.MapPath("\ExcelVBTest")fileName = filePath & "\" & 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 = "select au_id,au_lName,au_fname,phone," & _                    "address,city,state,zip,contract from authors"Dim cmd As SqlCommand = New SqlCommand(sql, cnn)Dim dr As SqlDataReaderdr = 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 = ""'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 = ""End While'Clean up.dr.Close()cnn.Close()objStreamWriter.Close()objFileStream.Close()'Show a link to the Excel file.HyperLink1.Text = "Open Excel"HyperLink1.NavigateUrl = fileExcel					
  9. On the File menu, click Save All to save the project files.
  10. On the Build menu in the Visual Studio .NET Integrated Development Environment, click Build to build the project.
  11. 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.
back to the top

Troubleshooting

  • You must change the connection string in the sample code for your environment.
  • 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
back to the top
Properties

Article ID: 308247 - Last Review: 05/21/2003 22:49:38 - Revision: 3.3

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

  • kbdatabase kbhowtomaster kbio KB308247
Feedback