Click here to Skip to main content
Click here to Skip to main content

Binding parameters to a Crystal Report using SqlDataSource control – A reduced code approach

, 3 Nov 2007
Rate this:
Please Sign up or sign in to vote.
Reduced code approach to parameter binding in Crystal Reports
Source Code: ASPNET_20_SampleProjects.zip

Introduction

There are many articles on the internet about data binding using CrystalReportViewer, CrystalReportSource and Crystal Report designer. Some examples used the reduced code approach where as others have shown how to work with the CR object model to do the same. I show one reduced code approach to bind UI elements to CrystalReportViewer to display the underlying report using SqlDataSource control.

Background

To create a Crystal Report that displays data from a database using filter values from UI elements on the presentation-tier we can use the following methods:

  1. Create a CR using a stored procedure in the database; create a datatable in the code behind for an ASPX page that displays the CR using a CrystalReportViewer control and bind the datatable to the report.
  2. Create a CR using a stored procedure in the database; bind the report parameters to values from UI elements programmatically using the parameters collection in the report object model.
  3. Another approach to bind parameters to a CR is the reduced code approach using a SqlDataSource control as a datasource for the underlying report.

Using the Code

To demonstrate this approach we will use CustOrderHist stored procedure that comes with Northwind database.

Create a new CR to display data using CustOrderHist stored procedure.

  1. Create CustOrderHistory.rpt in Visual Studio 2005 and follow the wizard to choose CustOrderHist stored procedure after you create a new database connection.
  2. Choose ProductName and Total as the report fields to display.

Create an ASPX page and bind CrystalReportViewer control to the Crystal Report

  1. Drag and drop a SqlDataSource control onto the ASPX page, switch to the page designer and use the smart tag to configure the data source. You will see markup generated as shown below after step 1 and 2
  2. Use the query builder and select CustomerID and CompanyName columns from Customers table.
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
    ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" 
    SelectCommand="SELECT [CustomerID], [CompanyName] FROM [Customers]" 
    SelectCommandType="Text"> 
    </asp:SqlDataSource>
  3. Drag and drop a DropDownList control onto the ASPX page, switch to the page designer and use the smart tag to select SqlDataSource created in the previous step as the datasource. Choose CustomerID as DataValueField and CompanyName as DataTextField.

    You will see markup generated as shown below after step 3.

    <asp:DropDownList ID="DropDownList1" runat="server" 
    AutoPostBack="True" DataSourceID="SqlDataSource1" 
    DataTextField="CompanyName" DataValueField="CustomerID"
        AppendDataBoundItems="true"> 
    <asp:ListItem Text="-- Customer Order History --" Value="NULL" /> 
    </asp:DropDownList> 
    
  4. Drag and Drop a CrystalReportSource control and CrystalReportViewer control, switch to the page designer and configure CRS to select CustOrderHistory.rpt as the report source. Then configure CRV to select CrystalReportSource1 control created as the report source.
  5. Open properties window for CrystalReportSource1 control and choose SqlDataSource1 control as one of the datasources. You will see markup generated as shown below after steps 4 and 5.
    <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" 
        AutoDataBind="true" ReuseParameterValuesOnRefresh="True" 
        DisplayGroupTree="False" ReportSourceID="CrystalReportSource1" />
    
    <CR:CrystalReportSource ID="CrystalReportSource1" runat="server" 
        OnDataBinding="CrystalReportSource1_DataBinding" 
        OnInit="CrystalReportSource1_Init" OnLoad="CrystalReportSource1_Load"
        OnPreRender="CrystalReportSource1_PreRender" 
        OnUnload="CrystalReportSource1_Unload"> 
    <Report FileName="Reports\CustOrderHistory.rpt"> 
    <DataSources> 
    <CR:DataSourceRef DataSourceID="SqlDataSource2" 
        TableName="CustOrderHist;1" /> 
    </DataSources> 
    </Report> 
    </CR:CrystalReportSource>
    
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
    ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>" 
    SelectCommand="CustOrderHist" SelectCommandType="StoredProcedure"
        OnSelecting="SqlDataSource2_Selecting"> 
    <SelectParameters> 
    <asp:ControlParameter ControlID="DropDownList1" Name="CustomerID"
        PropertyName="SelectedValue" /> 
    </SelectParameters> 
    </asp:SqlDataSource>
  6. Go into the code behind file and the ASPX page and add the following code.
    using System; 
    using System.Data; 
    using System.Configuration; 
    using System.Collections; 
    using System.Web; 
    using System.Web.Security; 
    using System.Web.UI; 
    using System.Web.UI.WebControls; 
    using System.Web.UI.WebControls.WebParts; 
    using System.Web.UI.HtmlControls; 
    using CrystalDecisions.CrystalReports.Engine; 
    using CrystalDecisions.Enterprise; 
    using CrystalDecisions.ReportSource; 
    using CrystalDecisions.Shared; 
    using CrystalDecisions.Web; 
    public partial class CRParameters : System.Web.UI.Page 
    { 
        protected void Page_Load(object sender, EventArgs e) 
        { 
        } 
        protected void SqlDataSource2_Selecting(object sender, 
            SqlDataSourceSelectingEventArgs e) 
        { 
            if (Page.IsPostBack == false) 
            { 
                e.Command.Parameters["@CustomerID"].Value = DBNull.Value; 
            } 
        }

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Viswanath Majeti
Web Developer
India India
Viswanath Majeti works as a Project Lead in .NET Technologies for a software development company in Hyderabad, India

Comments and Discussions

 
GeneralCan't find SqlDataSource attirbutes in Database Expert to drag and drop onto my report. Pinmemberrandydouglas200416-Apr-09 11:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140709.1 | Last Updated 4 Nov 2007
Article Copyright 2007 by Viswanath Majeti
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid