Click here to Skip to main content
14,575,378 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi,
I want to read and append the data from SQL Server table to Excel.
The values are properly reading,but the data's will not be appending in the excelsheet.
It says Error "Unable to read file"
I have more than 1000 Rows for the SQL Statement..
What is the problem...?
See my code
------------
ASPX.VB Code
------------
Partial Class AvailabilityReport
    Inherits System.Web.UI.Page
    Public Shared con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("Emp").ConnectionString)
    Public Shared cmd As SqlCommand
    Dim dt As DataTable
    Private Sub LoadData()
        Dim dsMaster As DataSet
        Dim sbViewDetails As New StringBuilder
        Dim drDetails As SqlDataReader
        Try
            With sbViewDetails
                .Append(BuildReportHeader())
                drDetails = ExecuteReaderWithOutParam("SpEmpReport")
                If drDetails .HasRows Then
                    While drDetails .Read()
                        .Append("<tr>")
                        .Append("<td>" & drDetails ("SrNo").ToString & "</td>")
                        .Append("<td>" & drDetails ("EmpNo").ToString & "</td>")
                        .Append("<td>" & drDetails ("Name").ToString & "</td>")
                        .Append("<td>" & drDetails ("Salary").ToString & "</td>")
                        .Append("<td>" & drDetails ("Join_Date").ToString & "</td>")
                        .Append("</tr>")
                    End While
                Else
                    .Append("<tr><td colspan="9" align="center">No record found</td></tr>")
                End If
                .Append("")
                divAvailabilityReport.InnerHtml = sbViewDetails.ToString()
                ExportToExcel("Emp Report")
            End With
        Finally
            If con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Sub
    Private Function BuildReportHeader() As String
        Dim Header As String
        Header = "<table id="tblEmp" style="border:1px solid black;" width="100%" border="1" cellspacing="0" cellspading="1">"
        Header &= "<tr style=" background-color: YELLOW;color: RED; removed: pointer; font-family: verdana; font-weight: boldfont-size: 10pt;">"
        Header &= "<th> Sl.No </th>"
        Header &= "<th> EmpNo </th>"
        Header &= "<th> Name </th>"
        Header &= "<th> Salary</th>"
        Header &= "<th> Join Date </th>"
       
        Header &= "</tr>"
        Return Header
    End Function
    Protected Sub ExportToExcel(ByVal reportName As String)
        Dim attachment As String = "attachment; filename=" & reportName & ".xls"
        Response.ClearContent()
        Response.AddHeader("content-disposition", attachment)
        Response.ContentType = "application/ms-excel"
    End Sub
    Public Shared Function ExecuteReaderWithOutParam(ByVal spName As String) As SqlDataReader
        cmd = New SqlCommand(spName, con)
        cmd.CommandType = CommandType.StoredProcedure
        Dim dr As SqlDataReader
        con.Open()
        dr = cmd.ExecuteReader()
        Return dr
    End Function
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            LoadData()
        Catch ex As Exception
        End Try
    End Sub
End Class</table>


[edit]Code block added, "Treat my content as plain text..." option disabled - OriginalGriff[/edit]
Posted
Updated 7-Sep-11 23:16pm
v4

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

First of all it has nothing to do with ASP.NET.
It says Error "Unable to read file"

From where are you running the application ? Did you try to debug it out, in which line its throwing the exception. Put a breakpoint and check if the file path is valid.
   
Comments
gani7787 8-Sep-11 5:54am
   
Hi,I have checked in the code one by one.

if the record > 64, then it will not update anything in the excel file. When i 'am trying to open it say's "Unable to read File"

If the Record Less than < 60, then it will append the data in the Excel File and has displaying the data..

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100