Export GridView to Excel cause System.OutOfMemoryException - ASP.NET VB

Recently I was working on a report where I need to display data in Excel sheet instead of PDF etc. So I designed the query which was returning more than 80,000 records for a given date criteria.
I used the technique of exporting data from gridview to Excel. Since the process is slow but most effcient where you are not required to create worksheet object. Things are managed by ASP.NET because Gridview has inbuilt functionality of exporting data to Excel.
Note: I was having MS office 2003 installed on my PC. there Excel 2003
I used the below code

Public Sub ExportDataToExcel()
' A function that bind data to GridView
bindDataToGridViewControl()
Response.Clear()
Response.Buffer = True
Response.Cache.SetExpires(DateTime.Now.AddSeconds(1))
Response.ContentType = "application/vnd.ms-excel"
Response.Charset = ""
Me.EnableViewState = False
Response.Write("")
Response.Write(vbCr & vbLf)
Response.Write("")
Dim tw As New StringWriter
Dim hw As New HtmlTextWriter(tw)
' Gridview that have data
GridView1.RenderControl(hw)
Response.AppendHeader("content-disposition","attachment;filename=Excel.xls")
Response.Write(tw.ToString())
Response.End()

End Sub


Issues:I started getting Exception "System.OutOfMemoryException"
After doing lot is searching on Internet, I came to know that Excel 2003's sheet has a limitation where a sheet can stored maximum of 65,536 records in one sheet.

If client has office 2010 installed or later version, one single sheet can contain more than 10,00,0000 records.

So the solution would be better to breakdown your DataTable records into multiple sets, and bind each set of records with each sheet. For that you need to created Worksheet object and each and ever column will be defnied by you.

Report Viewer - Hide PDF Export

I always like to share simple but interesting solutions to the users who are in urgent need of fixing it.Here I am going to share, how one can achieve hiding some of the options available from the Export options of Report Viewer from SQL Reporting Services.

I did the following to achieve this.

Steps:

1. Go to file "rsreportserver.config".

This file you can find in location "C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\ReportServer"

2. Go to the section <Render>.

3. Add the Visible="false" attribute to the end of each Extension tag to which you wish to hide.

For Example:

Here I am hiding PDF option and keeping all other options enabled.

<Extension Name="PDF" Type="Microsoft.ReportingServices.Rendering.ImageRenderer.PdfReport,Microsoft.ReportingServices.ImageRendering" Visible="false"/>

After applying these settings you will see only Excel option enabled.

Note: Since these settings are applied to the report server configuration file so changes will be reflected to all reports.

:) Is it not easy?

You comments are always appreciated.

SQL Reporting using Visual Studio 2005

Dear all,

I was in need to develop a report using SQL reporting. I started with VS 2005 but could not ind report project to start with.

I was little confuse, I went through some blogs and googled how to add "Business Intelligence Project" templates to VS 2005.

I applied many things but none of them worked, later I found installing with workgroup components from SQL server and now I was able to find it.

I developed the report and it was working fine. I also needed ReportViewer to render the report.

So I added an assembly Microsoft.ReportViewer.WebForm(10.0.0.0) and i got report viewer as well. Soon after this, I started getting run time JavaScript error like.

1. the base class includes the field 'ScriptManager1', but its type (System.Web.UI.ScriptManager) is not compatible with the type of control (System.Web.UI.ScriptManager).

2.I get the 'Sys' is undefined Javascript error whenever I place a ScriptManager object to my page.

3. Client side framework failed to load ASP.NET ajax

The resolution after doing R&D came the application should be Ajax enabled. I was surprised why a ReportViewer requires an Assembly System.Web.Extension(3.5.0.0) and why the application web.config file should have all settings defined as defined for Ajax enabled site.

I am frustrated now, after searching a lot I could not find anything good so I decided to develop a crystal report. That day, I left my office little soon and after reaching home, I discussed this annoying issues with my friends that why a ReportViewer needs System.Web.Extension assembly. They said that they will also try to do it.

It was a fresh morning for me. I asked my lead to give me some time before we switch to Crystal report. Let me analyse the issue.

I undo the changes and started from beginning, The following blog helped me a lot to understand why it was causing issues and what was required to solve all those issues.

So finally if you don't want to use ajax enables website and want to use report viewer to display report. Use 8.0.0.0 version of Microsoft.ReportViewer.WebForms.dll.

Installing the Microsoft.ReportViewer.WebForms assembly for MS Visual Studio 2005 and MS Visual Studio 2008

http://www.kodyaz.com/articles/assembly-Microsoft.ReportViewer.WebForms.aspx

You need to installed Microsoft Report Viewer Redistributable 2005

This redistributable package contains Windows Forms and ASP.NET Web server control versions of the Report Viewer.

And refer the

 Microsoft.ReportViewer.Common.dll(8.0.0.0)

 Microsoft.ReportViewer.WebForms.dll(8.0.0.0)

I hope you will enjoy it.

Installing Microsoft SQL Server 2005 Reporting Services on a non-default web site.

A good presentable article is given by Greg Van Mullem for Installing Microsoft SQL Server 2005 Reporting Services on a non-default web site with complete screen shots.

http://www.mathgv.com/sql2005docs/SQL2005ReportingServicesInstall.htm