Saturday 3 August 2019

Crystal Reports Page navigation not working after 2nd Page in ASP.Net

In this article I will explain how to solve the problem of Crystal Reports Page navigation not working after 2nd Page in ASP.Net.
The problem is that when Crystal Report is populated in Page_Load event handler and the ReportSource is set, the Current Page Index of the Crystal Report resets to 1, hence to solve this problem simply copy the code to populate Crystal Reports from Page_Load to Page_PreInit event handler in ASP.Net.
NoteBy default Visual Studio 2010, 2012 and 2013 does not include Crystal Reports hence you need to download the Crystal Reports 13. Refer my following articles.
 
 
Populating Crystal Report from Database
This article uses Strongly Typed DataSet to populate Crystal Reports from Database. For more details, please refer my article Generate Crystal Reports using Strongly Typed DataSet in ASP.Net using C# and VB.Net.
 
 
Enable Paging in Crystal Reports and set Records per page
Once the Crystal Report is populated from database, you will need to enable Paging in Crystal Reports.
In order to set or limit Number of Records per page i.e. PageSize when doing paging in Crystal Reports, you will need to do as shown in the following steps.
 
1. Open Crystal Reports in Design View and right click on the Details Section and then select Section Expert option from the context menu.
Crystal Reports Page navigation not working after 2nd Page in ASP.Net
 
2. Inside the Section Expert Dialog, select Paging Tab and then check the New Page Before CheckBox. Now click on the Formula Button next to the New Page BeforeCheckBox.
Crystal Reports Page navigation not working after 2nd Page in ASP.Net
 
3. Inside the Formula Editor Window, you will need to write a formula as shown below.
Crystal Reports Page navigation not working after 2nd Page in ASP.Net
 
Formula Syntax
IF Remainder (RecordNumber, {RecordsPerPage}) = 0 THEN
    TRUE
ELSE
    FALSE
 
Example
In the following formula, I have set the records per page value to 8. This will force Crystal Report to display maximum 8 records per page.
IF Remainder (RecordNumber, 8) = 0 THEN
    TRUE
ELSE
    FALSE
 
Finally click Save and close button to save the formula.
 
 
Handling Crystal Reports Paging in ASP.Net
There is no special event required to handle Crystal Reports paging in ASP.Net, except that you will need to populate Crystal Report from database inside Page_PreInit event handler instead of Page_Load event handler.
The problem is that when Crystal Report is populated in Page_Load event handler and the ReportSource is set, the Current Page Index of the Crystal Report resets to 1, hence to solve this problem simply copy the code to populate Crystal Reports from Page_Load to Page_PreInit event handler.
C#
protected void Page_PreInit(object sender, EventArgs e)
{
    ReportDocument crystalReport = new ReportDocument();
    crystalReport.Load(Server.MapPath("~/CustomerReport.rpt"));
    Customers dsCustomers = GetData("select * from customers");
    crystalReport.SetDataSource(dsCustomers);
    CrystalReportViewer1.ReportSource = crystalReport;
}
 
private Customers GetData(string query)
{
    string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
 
            sda.SelectCommand = cmd;
            using (Customers dsCustomers = new Customers())
            {
                sda.Fill(dsCustomers, "DataTable1");
                return dsCustomers;
            }
        }
    }
}
 
VB.Net
Protected Sub Page_PreInit(sender As Object, e As EventArgsHandles Me.Load
    Dim crystalReport As New ReportDocument()
    crystalReport.Load(Server.MapPath("~/CustomerReport.rpt"))
    Dim dsCustomers As Customers = GetData("select * from customers")
    crystalReport.SetDataSource(dsCustomers)
    CrystalReportViewer1.ReportSource = crystalReport
End Sub
 
Private Function GetData(query As StringAs Customers
    Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim cmd As New SqlCommand(query)
    Using con As New SqlConnection(conString)
        Using sda As New SqlDataAdapter()
            cmd.Connection = con
 
            sda.SelectCommand = cmd
            Using dsCustomers As New Customers()
                sda.Fill(dsCustomers, "DataTable1")
                Return dsCustomers
            End Using
        End Using
    End Using
End Function
 
 
Screenshot
Crystal Reports Page navigation not working after 2nd Page in ASP.Net

No comments:

Post a Comment