Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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


View products that this article applies to.

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.

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.Data
    Imports System.Data.Common
    Imports System.Data.SqlClient
    Imports System.IO
    Imports System.Text
    					
  8. 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("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 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 = ""
    
    '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.

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


Keywords: KB308247, kbio, kbhowtomaster, kbdatabase

↑ Back to the top

Article Info
Article ID : 308247
Revision : 6
Created on : 5/21/2003
Published on : 5/21/2003
Exists online : False
Views : 867