Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

Crystal Report From More Than One Table in ASP.NET - Method 2

Article:
Viewed:  2582 
Posted On:  19/09/2014 09:27:30 
How we can create a Crystal Report from more than one table using Stored Procedure ? 

 

In this article I am going to show how we can display crystal report from more then one table.

In my previous article Crystal Report From More Than One Table in ASP.NET I also explained same thing but in this article I am going to use Stored Procedure.

Now we will learn this step by step:

Below is my SQL server table, for which I will show crystal report.

Below 3 tables I am using to create a Crystal Report.


1.
 
Customer


Customer Product Order.png


Image 1.

2. Product

Product.png

Image 2.

3. Customer Product Order

Customer.png

Image 3.

I also create a Stored Procedure here GetCustProdOrderDetail

CREATE PROCEDURE  GetCustProdOrderDetail
AS
BEGIN
           SELECT O.Cust_Prod_Order_ID,C.Customer_Name,
           P.Product_Name,O.Quantity,O.Order_Date
           FROM Cust_Prod_Order As O
           INNER JOIN Customer AS C
           ON O.Customer_ID=C.Customer_ID
           INNER JOIN Product AS P
           ON O.Product_ID= P.Product_ID   
END

Now Open Visual Studio -> File -> New Web Site.

4.png

Image 4.

Now Right Click on Solution Explorer -> Add New Item -> Select DataSet

5.png

Image 5.

Click Yes.

6.png

Image 6.

Now Right click -> Add -> Data Table

7.png

Image 7.

Now Right Click on Header -> Add ->Column

Make sure column name and their Data type should be same as in your data table. You can change data type by selecting your added column ->Right click and select Data type property

8.png

Image 8.

9.png

Image 9.

Now Right click on solution explorer -> Add New Item -> Select Crystal Report

10.png

Image 10.

11.png

Image 11.

Select Project Data -> ADO.NET Data Sets-> Your Created Data Set -> Your Table -> Click Next

12.png

Image 12.

Now select column for report and click on finish.

13.png

Image 13.

Now Crystal Report will look like below.

14.png

Image 14.

Now open your Default.aspx page and add a Report Viewer here like below.

15.png

Image 15.

Now on your aspx page:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<%@ Register Assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
    Namespace="CrystalDecisions.Web" TagPrefix="CR" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Display Crystal Report Using Stored Procedure</title>
</head>
<body>
    <form id="form1" runat="server">
    <table cellpadding="10" cellspacing="10" width="70%" height="300px" align="center" style="bordersolid 2px gray;">
        <tr>
            <td align="center" style="background-colorSkyBlue;">
                <span style="font-familyTimes New Romanfont-size18ptcolorGreen;">Customer
                    Product Order Detail Report</span>
            </td>
        </tr>
        <tr>
            <td align="center">
                <asp:Panel ID="pnlReport" runat="server" Height="400px">
                    <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true" />
                </asp:Panel>
            </td>
        </tr>
    </table>
    </form>
</body>
</html>

Now aspx.cs code is:

using
 System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
 
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        GetData();
    }
 
    protected void GetData()
    {
        string connStr = @"Data Source=MyPC\SqlServer2k8;Integrated Security=True;Initial Catalog=Business";
        SqlConnection con = new SqlConnection(connStr);        
        SqlCommand cmd = new SqlCommand();
        DataSet ds = null;
        SqlDataAdapter adapter;
        try
        {
            con.Open();
            cmd.CommandText = "GetCustProdOrderDetail";
            cmd.CommandType = CommandType.StoredProcedure;            
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
            ds = new DataSet();
            adapter = new SqlDataAdapter(cmd);
            adapter.Fill(ds);
 
            //Assigning Data To report
            ReportDocument rd = new ReportDocument();
            rd.Load(Server.MapPath("CrystalReport.rpt"));
            rd.SetDataSource(ds.Tables[0]);
            CrystalReportViewer1.ReportSource = rd;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            cmd.Dispose();
            if (con.State != ConnectionState.Closed)
            {
                con.Close();
            }
        }
    }
}
 
Now run the application:

16.png

Image 16.

  Comment:
 
By Madhav  On  19/01/2016 03:04:49
this can not be a final solution,here u simply using joins and storing that result in one table so it means u are using a single table for crystal report creation,i want you to actually drag more than one table from server explorer and generate report from these tables, if u have that solution then please do reply on my mail gunjalmadhav25@gmail.com Thanks And regards Madhav gunjal
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us