Building an Invoice Application with ASP.NET and Crystal Reports - Part 1
page 7 of 9
by Vince Varallo
Feedback
Average Rating: 
Views (Total / Last 10 Days): 103821/ 201

Step 8: Viewing the report in an ASP.NET page

Now that you have built the report you can build a web page to display it. I will create a simple page that lets the user select a customer and then preview any invoice for that customer. We will do all of this in this Default.aspx page that is part of the AdventureWorksReports web site we created.

1.    Open the Default.aspx page and view its markup. Add the following Register directive after the Page directive.

Listing 5

<%@ Register assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, 
PublicKeyToken=692fbea5521e1304" namespace="CrystalDecisions.Web" tagprefix="CR" %>

This allows you to use the Crystal Reports Viewer control that comes with Visual Studio.

2.    Add the following code between the div tags.

Listing 6

Select a customer:<asp:DropDownList ID="ddlCustomer" runat="server">
</asp:DropDownList>
    
<asp:Button ID="btnPreview" runat="server" onclick="btnPreview_Click" 
  Text="Preview" />
 
<br />
<br />
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" 
  AutoDataBind="true" />

This adds a drop down list that will be populated with the list of customers in the Page_Load event. The Preview button will get all the data for the selected customer and bind the data to the report. 

3.    Add the following using statements in the code behind.

Listing 7

//Custom using statements
using System.Data.SqlClient;
using System.Configuration;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

4.    Add the following code to the Page_Load event.

Listing 8

   if (!IsPostBack)
   {
      //Load the drop down list with customers.
      SqlConnection cn = new   SqlConnection(
        ConfigurationManager.ConnectionStrings["AdventureWorks"].ConnectionString);
      cn.Open();
 
      SqlCommand cmd = new SqlCommand("SELECT DISTINCT LastName + ', ' + 
                                              FirstName AS Name, 
                                              Person.Contact.ContactID " +
                                        "FROM Sales.SalesOrderHeader " +
                                  "INNER JOIN Person.Contact " +
                                          "ON Sales.SalesOrderHeader.ContactID = 
                                              Person.Contact.ContactID " +
                                    "ORDER BY LastName + ', ' + FirstName", cn);
      SqlDataReader dr = cmd.ExecuteReader();
 
      ddlCustomer.DataSource = dr;
      ddlCustomer.DataTextField = "Name";
      ddlCustomer.DataValueField = "ContactId";
      ddlCustomer.DataBind();
 
      CrystalReportViewer1.Visible = false;
   }
   else
   {
      if (CrystalReportViewer1.Visible == true)
      {
         //Rebind the report.
         BindReport();
      }
   }

This code loads the drop down list with the customers in the database. You need to add the connection string to your database in the web.config file for this to work. In the <appSettings> section you should add the following:

Listing 9

<connectionStrings>
<add name="AdventureWorks" connectionString="Data Source=YOURSERVER;User 
  ID=aspalliance;Password=aspalliance;Initial Catalog=AdventureWorks;"/>
</connectionStrings>

This code assumes you have added a SQL Login called "aspalliance" with the password "aspalliance" and given them permissions to select data from the database.  Your SQL Server needs to be setup for Mixed Authentication mode so SQL Logins are allowed.

5.    Add the following code for the Preview button's click event.

Listing 10

protected void btnPreview_Click(object sender, EventArgs e)
{
    BindReport();
    CrystalReportViewer1.Visible = true;
}

This code calls a custom method called BindReport() and then shows the Crystal Report Viewer Control.

6.    Now add the following custom methods.

Listing 11

private void BindReport()
{
    ReportDocument report = new ReportDocument();
    report.Load(Server.MapPath("Invoice.rpt"));
 
    SetTableLocation(report.Database.Tables);
 
    CrystalReportViewer1.ReportSource = report;
 
    report.DataDefinition.RecordSelectionFormula = 
               "{SalesOrderHeader.ContactID} = " + ddlCustomer.SelectedItem.Value; 
}
 
private void SetTableLocation(Tables tables)
{
    ConnectionInfo connectionInfo = new ConnectionInfo();
 
    connectionInfo.ServerName = @"LTMTI30\SQL2008";
    connectionInfo.DatabaseName = "AdventureWorks";
    connectionInfo.UserID = "aspalliance";
    connectionInfo.Password = "aspalliance";
 
    foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
    {
        TableLogOnInfo tableLogOnInfo = table.LogOnInfo;
        tableLogOnInfo.ConnectionInfo = connectionInfo;
        table.ApplyLogOnInfo(tableLogOnInfo);
    }
}

The first method creates an instance of the ReportDocument class. This represents the report that you created earlier and allows you to manipulate it at runtime. The SetTableLocation() method sets the table location for each table in the report. Again, this assumes you have created an "aspalliance" SQL Login and have given it access to the database. The Crystal Report Viewer's source is then set to the report object. The next line dynamically filters the report based on the customer selected in the drop down list. You simply create a string just like a where clause in an SQL Statement. You have to use the report fields surrounded by brackets {} when you reference a field.

You can now run the project. If you choose "Abel, Catherine" from the list and click the Preview button you should see the following page.

Figure 20

You can scroll through the report using the navigation buttons at the top of the page or you can click directly to an invoice by clicking the SO number in the group tree on the left hand side of the report.


View Entire Article

User Comments

Title: pranav   
Name: pranav
Date: 2012-11-26 1:22:05 AM
Comment:
helllo
Title: Need the Images ... Please   
Name: KM
Date: 2011-12-25 8:26:20 AM
Comment:
Hi the article doesn't load any images. Please check. Will really appreciate your help
Title: Missing Images   
Name: Johnny come lately
Date: 2011-11-22 10:22:47 AM
Comment:
Where are the pictures!! Its hard to follow without the images
Title: birendra yogi   
Name: bina
Date: 2011-02-02 8:16:21 AM
Comment:
I have used this article to create my own invoices for my business, and now back on to refresh my memory for a client's invoices
Title: This was perfect   
Name: Edward Pinto
Date: 2010-12-11 1:35:06 PM
Comment:
Thanks for the Listing 3 formula. I remember using such a formula a while back and needed it at a client site in a real hurry. this worked great esp some address line 2 or 3 were blank,

Here is my variation.

// Edward M Pinto 12/11/2010
// This formula was created to avoid the nested subreport for the shipping address.
//It evaluates each feild for null values and suppresses them if they are null

local StringVar shipTo := {@ShipAddr1} + chr(13);

if isnull({@ShipAddr2}) = false then
shipTo := shipTo + {@ShipAddr2} + chr(13);

if isnull({@ShipAddr3}) = false then
shipTo := shipTo + {@ShipAddr3} + chr(13);

if isnull({@ShipAddr4}) = false then
shipTo := shipTo + {@ShipAddr4} + chr(13);

//builds the shipping label
shipTo := shipTo + {@ShipCityStateZip}+Chr(13)+{@ShipCountry}
Title: exlent   
Name: latha
Date: 2010-11-08 6:17:00 AM
Comment:
it is useful to all
Title: Fantastic Article   
Name: Phil Smith
Date: 2010-10-25 7:05:46 AM
Comment:
I have used this article to create my own invoices for my business, and now back on to refresh my memory for a client's invoices.
Title: good practice code   
Name: vishwajeet singh
Date: 2010-09-30 6:24:39 AM
Comment:
its realy good practice code i god lots of conceptual knpwledge through this
Title: Miss   
Name: Kanchana Sinha
Date: 2010-09-23 3:08:51 AM
Comment:
Thanxs for your sample code,it is very easy to understand and it helped me to create invoice.
Title: Mr   
Name: Kumar
Date: 2010-03-19 2:53:12 AM
Comment:
Thank you for your sample code, which is really a good stuff for a beginners, and also it is very easy to understand.
Title: Mr.   
Name: Sam
Date: 2010-03-01 5:24:21 AM
Comment:
This article was of great help. Thanks a ton :)
Title: Nice Article   
Name: Rick
Date: 2009-12-01 11:21:23 AM
Comment:
The article was easy to follow and touched on thing I would not normally experiment with. Thanks for taking the time to put the article together
Title: gr8 stuff   
Name: sabata mereeotlhe
Date: 2009-11-25 7:34:42 AM
Comment:
thank you gr8 work
Title: Thank you   
Name: Tabitha
Date: 2009-11-18 10:00:57 AM
Comment:
Thank you very much!!!
Title: Thanks for doing such an awesome article!   
Name: Julius
Date: 2009-10-28 9:16:06 PM
Comment:
Very good article. Really appreciate it.
Title: Awesome tutorial!   
Name: Nivi
Date: 2009-09-13 12:22:10 AM
Comment:
Thanks for the awesome tutorial. It really helped me.
Thanks again
Title: Excellent Article   
Name: sai
Date: 2009-09-01 9:11:15 AM
Comment:
Thankyou so much for such a wonderful article on Crystal Reports.
Title: Landsailor   
Name: Pete Lyons
Date: 2009-08-31 8:34:22 PM
Comment:
Vince- What an amazing tutorial. There are many ways to skin a cat when it comes to reports delivery, but this is by far the most insightful way to tie-in .net with my crystal skills. Thanks again :)))))) ~Pete
Title: Error in Sum formula   
Name: Pavan
Date: 2009-08-25 10:28:17 AM
Comment:
I am not sure where I made mistake but I started from scratch again making sure I check the Preview after each field added to the report and it worked this time.

I have a quick question, Since we are using pull method, Do we need to use BindReport method? Why cant we just use the reportviewer.selectionformula?

Thanks for the wonderful tutorial!!
Title: Error in Sum formula   
Name: Pavan
Date: 2009-08-25 2:31:29 AM
Comment:
Hi,
Error in formula . 'Sum({SalesOrderDetail.LineTotal}, {SalesOrderHeader.SalesOrderNumber}) ' The result of selection formula must be a boolean.

Help please.
Thanks
Title: Best for beginner   
Name: murugaperumalwin
Date: 2009-08-07 8:30:36 AM
Comment:
hai Vince Varallo...

this is very useful for a people who still have't knowledge

about crystal report.

thanks
MP
Title: superb article   
Name: Raj
Date: 2009-07-30 2:27:41 AM
Comment:
very great article
Title: good article   
Name: john
Date: 2009-07-30 2:25:06 AM
Comment:
very good article.........
Title: Great Article   
Name: Caitriona
Date: 2009-06-11 8:50:55 AM
Comment:
I found this article extreemly useful. Thank you so much for sharing your talents!

Caitriona
Title: Child Nodes not allowed   
Name: Paul
Date: 2009-05-27 11:05:56 AM
Comment:
Hi,
I am getting an error like:-
Child nodes not allowed from the web.config file.
I am using ASP.NET2005 and SQL2005.
Is it because of this I got this error?
Thanks,
Paul
Title: Link for the Adventure Works database   
Name: Vince
Date: 2009-05-27 7:58:34 AM
Comment:
The page that has the download is http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=18407

Click on the SQL2008.AdventureWorks All Databases.x86.msi link.
Title: Thanks so much   
Name: Paul
Date: 2009-05-26 5:23:07 AM
Comment:
Hi Vince,
It is really an excellent article.
I could not find the Adventure database in the downloads.
Where can I find it?
Thanks so much,
Paul
Title: Thanks - How about a version for VB users?   
Name: Art
Date: 2009-05-22 10:07:14 AM
Comment:
Thanks Vince!

I know some people are never satisfied ;-) but how about adding the "version b" listings for those of us who use VB?
Title: dr   
Name: kola
Date: 2009-05-11 11:12:06 AM
Comment:
good
Title: Thanks   
Name: Suresh Kumar Gundala
Date: 2009-05-08 5:04:13 AM
Comment:
Hi Vince,

Nice article. Actually i dont know anything about crystal reports. Now i got an idea.. This is a wonderful article.
Great work yaar. Thank you very much for writting such a nice article
Title: Good Article for a Quick start   
Name: Niki
Date: 2009-05-05 7:44:10 AM
Comment:
Thank you Vince, A very good article for a quick start on crystal report designing.We expect more articles from you in the same space

Product Spotlight
Product Spotlight 



Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-11-20 7:48:43 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search