Accessing Crystal Report in ASP.NET using VB.Net

A web developer may or may not be aware of crystal report in depth as compared to desktop application developer.

Couple of months back I was suppose to access crystal reports in asp.net, but I found many problems while working with crystal report using crystalreportviewer in asp.net. I am writing this application for web-developers who want to access crystal reports in asp.net by using multiple tables or views etc. This application might help them and save their development time.

List of problems which I came across while accessing crystal reports in asp.net

• Logon failure
  Refer to following URL
  http://support.businessobjects.com/library/kbase
  /articles/c2010371.asp
• DLL not found
  On a development machine if crystal report is not installed then you might get above error
• Fail to render page
  Basically there could be two possible ways for the solution
  1. This error can occur due to the unsuppressed report header, removing space between the header might solve the problem.
  2. This error can be solved by assigning permission to ASPNET user by default. ASPNET user does not have permission on windows XP and XP professional machine. To explain in detail it creates crystal report image in the local machine's temp directory or in the same directory where application is residing, due to security issue or permission problem. ASP.net unable to render image created in the memory and hence error occur.

Refer to following URL for the detail information

http://support.businessobjects.com/library/kbase/articles/c2013414.asp
http://support.businessobjects.com/library/kbase/articles/c2014843.asp
http://support.businessobjects.com/library/kbase/articles/c2014618.asp

Merge Modules

Once build is ready and wanted to deployed it on the development server. You should add merge module in the build. This is important because you never know that crystal report is installed on the development machine and it is not possible for you to provide crystal report to the client. You can include following modules in the build and it will take care of all the further issues of deployment on the server.

• Crystal_managed2003.msm
• Crystal_database_Access2003.msm
• Crystal_datavase_Access2003.msm
• Crystal_regwiz2003.msm

-- I have used Northwind database of SQL-SERVER
--View  Categorywise_products on product and categories table

create view ProductsList AS
SELECT Categories.CategoryName, Products.ProductName, Products.QuantityPerUnit, Products.UnitsInStock, Products.Discontinued
FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID
WHERE Products.Discontinued <> 1

To create a dataset object from a database

Create a new schema file in the project:

1. In the Solution Explorer, right-click the project name, point to Add, and click Add New Item.
2. In the Categories area of the Add New Item dialog box, expand the folder and select Data.
3. In the Templates area, select Dataset.
4. Accept the default name Dataset1.xsd.

This creates a new schema file (Dataset1.xsd) that will be used to generate a strongly-typed dataset. The schema file will be displayed in the ADO.NET Dataset Designer.

Specify where the database is located:

1. In the Server Explorer, right-click Data Connections and select Add Connection.
2. In the Data Link Properties dialog box, click the Provider tab and select a provider (for example, Microsoft OLE DB Provider for SQL Server).
3. Click the Connection tab and specify the location of your database. Enter server and logon information where necessary.
4. Click OK.

Your database, its tables, and its fields now appear in the Server Explorer under the Data Connections node.

In the Solution Explorer, double-click Dataset1.xsd, if it is not already the active view. Dataset1.xsd should now be displayed in the Dataset tab.

To build a schema for your dataset, drag the desired tables from the Server Explorer to the Dataset tab of Dataset1.xsd. Click Save Dataset1.xsd to save the Dataset1.xsd file. On the Build menu, click Build to generate the dataset object for the project.

There is another reason why I have created xsd (schema file) for report. Let us consider a situation where you want to deploy your application on client's server. Now situation is you have set location of crystal report to your development server and though you set logon information programmatically logon failure occurs. To overcome this problem, I have a view associated in schema file. I can set location of ADO.NET Dataset to the xsd view and this will take care of all the problems for setting location while runtime, since it is independent of any of the server and to access dataset we are applying separate connection string from the web.config file. This way you can get rid of all the problems to access crystal reports in asp.net.

Actual code as below