Fixing the CrystalReportViewer “Next Page” Navigation Problem

Note: This is an update to my last post.

I discovered that the version of the SAP CrystalReportViewer for VS2010 (13.0.2000.0) that I am using appears to have a bug that prevents the user from proceeding past page 2 when using the Next Page button. Pages can still be directly accessed by typing in the page number, but clicking the Next Page button keeps you on page 2.

I read quite a few possible solutions to this, such as moving the code that loads the Crystal Report from Page_Load to Page_Init, but this did not work for me. Also, there is an update to 13.0.2000.0 that might fix this issue, but I am not in a position to update the version of the SAP Crystal Reports software at present.

Fortunately, I was able to code around this bug. First, I disabled the navigation buttons on the viewer itself. Next, I put my own navigation buttons above the viewer component on the ASP.NET Web Form. Lastly, I wrote the code that handles the click events for these buttons.

Here is the code for the Reports.aspx page:

<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Reports.aspx.vb" Inherits="Pages_Reports" %>

<%@ Register Assembly="CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"
    Namespace="CrystalDecisions.Web" TagPrefix="CR" %>

<!DOCTYPE html>

<html>
    <head runat="server">
        <title></title>
        <meta http-equiv="X-UA-Compatible" content="IE=9,chrome=1" />
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:ScriptManager ID="ScriptManager1" runat="server" AsyncPostBackTimeout="0" EnableHistory="True">
            </asp:ScriptManager>
            <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
                <ContentTemplate>
                    <asp:Label ID="Label1" runat="server" Text="Page navigation:   "></asp:Label>
                    <asp:Button runat="server" ID="btnFirst" 
                        Text="|<--" ToolTip="Go to first page"/>
                    <asp:Button runat="server" ID="btnPrev" 
                        Text="<<" ToolTip="Go to previous page"/>
                    <asp:Button runat="server" ID="btnNext" 
                        Text=">>" ToolTip="Go to next page"/>
                    <asp:Button runat="server" ID="btnLast" 
                        Text="-->|" ToolTip="Go to last page"/>
                    <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="true"                     
                    HasCrystalLogo="False" GroupTreeStyle-ShowLines="True" 
                        Width="100%" Height="100%" 
                    EnableDatabaseLogonPrompt="False" ReuseParameterValuesOnRefresh="True" 
                        HasPageNavigationButtons="False" ShowAllPageIds="True" 
                        EnableParameterPrompt="False" />                    
                </ContentTemplate>
            </asp:UpdatePanel>
        </div>
        </form>
    </body>
</html>

…and the code file Reports.aspx.vb…

Imports CrystalDecisions.Shared
Imports System.IO
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Web

Partial Class Pages_Reports
    Inherits System.Web.UI.Page

    Protected strQueryString As String
    Protected intLastPage As Integer
    Protected intCurPage As Integer

    Protected Sub Page_Init(sender As Object, e As System.EventArgs) Handles Me.Init
        strQueryString = Request.QueryString.ToString()
        Page.Title = Replace(Request.QueryString("rpt").ToString(), ".rpt", "")
        If Not Page.IsPostBack Then
            'Do nothing
        ElseIf Session(strQueryString) IsNot Nothing Then
            CrystalReportViewer1.ReportSource = Session(strQueryString)
        End If
    End Sub

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Dim strReportName As String = Request.QueryString("rpt")
            Dim strSingleDate As String = Request.QueryString("single")
            Dim strBeginDate As String = Request.QueryString("begin")
            Dim strEndDate As String = Request.QueryString("end")

            Dim bSingleDate As Boolean = False
            Dim bMultiDate As Boolean = False

            Dim arrSingleDate() As String = _
               Utilities.AppSettingsFunction.getValue("SingleDate").Split(",")
            Dim arrMultiDate() As String = _
               Utilities.AppSettingsFunction.getValue("MultiDate").Split(",")

            If Array.IndexOf(arrSingleDate, strReportName) <> -1 Then
                bSingleDate = True
                bMultiDate = False
            ElseIf Array.IndexOf(arrMultiDate, strReportName) <> -1 Then
                bSingleDate = False
                bMultiDate = True
            End If

            Dim connInfo As New ConnectionInfo
            Dim rptDoc As New ReportDocument

            'setup the connection
            connInfo = Functions.GetConnectionInfo()

            'load the Crystal Report
            rptDoc.Load(Server.MapPath( _
               Utilities.AppSettingsFunction.getValue("ReportFolder") _
               & strReportName))

            'apply logon information
            For Each tbl As CrystalDecisions.CrystalReports.Engine.Table In rptDoc.Database.Tables
                Dim repTblLogonInfo As TableLogOnInfo = tbl.LogOnInfo
                repTblLogonInfo.ConnectionInfo = connInfo
                tbl.ApplyLogOnInfo(repTblLogonInfo)
            Next

            'add required parameters
            If bSingleDate Then
                rptDoc.SetParameterValue("REPORT_DATE", strSingleDate & " 23:59:59")
            End If

            If bMultiDate Then
                rptDoc.SetParameterValue("BEGIN_DATE", strBeginDate & " 00:00:00")
                rptDoc.SetParameterValue("END_DATE", strEndDate & " 23:59:59")
            End If

            'Set, bind, and display Crystal Reports Viewer data source
            Session(strQueryString) = rptDoc
            CrystalReportViewer1.ReportSource = Session(strQueryString)
            CrystalReportViewer1.ShowLastPage()
            CrystalReportViewer1.ShowFirstPage()
            Session(strQueryString + "_pagenum") = 1
            Session(strQueryString + "_lastpagenum") = GetLastCRPageNumber()
        ElseIf Session(strQueryString) IsNot Nothing Then
            CrystalReportViewer1.ReportSource = Session(strQueryString)
        End If
        GetPageNums()
    End Sub


    Private Sub GetPageNums()
        intCurPage = Session(strQueryString + "_pagenum")
        intLastPage = Session(strQueryString + "_lastpagenum")
        ButtonsCheck()
    End Sub

    Private Sub SetCurPageNum(intPage As Integer)
        GetPageNums()
        Session(strQueryString + "_pagenum") = intPage
        ButtonsCheck()
    End Sub

    Protected Sub btnNext_Click(sender As Object, e As System.EventArgs) Handles btnNext.Click
        GetPageNums()
        If intCurPage < intLastPage Then
            SetCurPageNum(intCurPage + 1)
            CrystalReportViewer1.ShowNthPage(intCurPage + 1)
        Else
            CrystalReportViewer1.ShowNthPage(intCurPage)
        End If
    End Sub

    Protected Sub btnPrev_Click(sender As Object, e As System.EventArgs) Handles btnPrev.Click
        GetPageNums()
        If intCurPage > 1 Then
            SetCurPageNum(intCurPage - 1)
            CrystalReportViewer1.ShowNthPage(intCurPage - 1)
        Else
            CrystalReportViewer1.ShowNthPage(intCurPage)
        End If
    End Sub

    Private Function GetCRPageNumber() As Integer
        Dim vi As ViewInfo = CrystalReportViewer1.ViewInfo
        Return vi.PageNumber
    End Function

    Private Function GetLastCRPageNumber() As Integer
        Dim vi As ViewInfo = CrystalReportViewer1.ViewInfo
        Return vi.LastPageNumber
    End Function

    Protected Sub ButtonsCheck()
        If intCurPage = 1 Then
            btnFirst.Enabled = False
            btnPrev.Enabled = False
        Else
            btnFirst.Enabled = True
            btnPrev.Enabled = True
        End If

        If intCurPage = intLastPage Then
            btnLast.Enabled = False
            btnNext.Enabled = False
        Else
            btnLast.Enabled = True
            btnNext.Enabled = True
        End If
    End Sub

    Protected Sub CrystalReportViewer1_Navigate(source As Object, e As CrystalDecisions.Web.NavigateEventArgs) Handles CrystalReportViewer1.Navigate
        SetCurPageNum(e.NewPageNumber)
    End Sub

    Protected Sub btnFirst_Click(sender As Object, e As System.EventArgs) Handles btnFirst.Click
        CrystalReportViewer1.ShowFirstPage()
        GetPageNums()
    End Sub

    Protected Sub btnLast_Click(sender As Object, e As System.EventArgs) Handles btnLast.Click
        CrystalReportViewer1.ShowLastPage()
        GetPageNums()
    End Sub
End Class

Spawning a New Window for Each CrystalReportViewer

Update to this post: I have found a bug in the CrystalReportViewer that I address here.

In creating a ASP.NET Web Forms site for viewing Crystal Reports, I needed to open multiple dates for the same report to compare values. The previous setup I used would not allow this; only one report could be open at a time. To remedy this, I changed the way reports would be loaded, and made a separate window for each one.

Excerpt of the home page code:

<table style="border-width:0px; border-spacing:1px; border-collapse:collapse;">
    <tr style="vertical-align:top;"> 
        <td class="fcel1">
            <p class="f0"><strong>Report</strong><br />
                <asp:DropDownList id="ddlReports" runat="server" AutoPostBack="True" />								        
            </p>
        </td>						            
       <asp:panel id="pnlreportdate" runat="server">
            <td class="fcel1">
                <p class="f0"><strong>Report Date</strong><br />
                    <asp:TextBox id="txtSingleDate" runat="server"></asp:TextBox>
                    <ajaxToolkit:CalendarExtender
                        ID="CalendarExtender1" runat="server" TargetControlID="txtSingleDate">
                    </ajaxToolkit:CalendarExtender>
                </p>
            </td>
        </asp:panel>
        <asp:panel id="pnlstartenddates" runat="server">
            <td class="fcel1">
                <p class="f0"><strong>Begin Date</strong><br />
                    <asp:TextBox id="txtBeginDate" runat="server"></asp:TextBox>
                    <ajaxToolkit:CalendarExtender
                        ID="CalendarExtender2" runat="server" TargetControlID="txtBeginDate">
                    </ajaxToolkit:CalendarExtender>
                </p>
            </td>
            <td class="fcel1">
                <p class="f0"><strong>End Date</strong><br />
                    <asp:TextBox id="txtEndDate" runat="server"></asp:TextBox>
                    <ajaxToolkit:CalendarExtender
                        ID="CalendarExtender3" runat="server" TargetControlID="txtEndDate">
                    </ajaxToolkit:CalendarExtender>
                </p>
            </td>
        </asp:panel>                              
        <td class="fcel1" style="vertical-align:middle;">
            <asp:Button id="btnGenerate" runat="server" Text="Generate"></asp:Button>
        </td>
    </tr>
</table>           

…and its code behind:

Imports AjaxControlToolkit
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports CrystalDecisions.Web
Imports System.IO

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Init(sender As Object, e As System.EventArgs) Handles Me.Init
        If Not Page.IsPostBack Then
            txtSingleDate.Text = Now.Date()
            txtBeginDate.Text = Now.Date()
            txtEndDate.Text = Now.Date()
        End If
    End Sub

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Dim dirpath As String = Utilities.AppSettingsFunction.getValue("ReportFolder")
            Dim thedir As DirectoryInfo = New DirectoryInfo(Server.MapPath(dirpath))
            Dim filenames As FileInfo() = thedir.GetFiles()

            ddlReports.DataSource = filenames
            ddlReports.DataBind()

            For Each report As ListItem In ddlReports.Items
                Dim reportFileName As String = report.Value
                Dim reportText As String = reportFileName

                reportText = Replace(reportText, ".rpt", "")

                report.Text = reportText
            Next
        End If
        ShowPanels()
    End Sub

    Protected Function SetParameters() As String
        Dim strOnClientClick As String
        Dim BeginDate As String
        Dim EndDate As String
        Dim SingleDate As String
        Dim RptName As String

        BeginDate = txtBeginDate.Text
        EndDate = txtEndDate.Text
        SingleDate = txtSingleDate.Text
        RptName = ddlReports.SelectedValue
        strOnClientClick = "window.open('Pages/Reports.aspx?guid=" & newGuid().ToString & "&rpt=" & RptName
        If pnlstartenddates.Visible Then
            strOnClientClick += "&begin=" & BeginDate & "&end=" & EndDate
        ElseIf pnlreportdate.Visible Then
            strOnClientClick += "&single=" & SingleDate
        End If
        strOnClientClick += "','_blank');"
        Return strOnClientClick
    End Function

    Protected Sub ShowPanels()
        Dim strSelectedValue As String
        Dim bSingleDate As Boolean
        Dim bMultiDate As Boolean

        bSingleDate = False
        bMultiDate = False

        strSelectedValue = ddlReports.SelectedValue.ToString()

        Dim arrSingleDate() As String = Utilities.AppSettingsFunction.getValue("SingleDate").Split(",")
        Dim arrMultiDate() As String = Utilities.AppSettingsFunction.getValue("MultiDate").Split(",")

        If Array.IndexOf(arrSingleDate, strSelectedValue) <> -1 Then
            bSingleDate = True
            bMultiDate = False
        ElseIf Array.IndexOf(arrMultiDate, strSelectedValue) <> -1 Then
            bSingleDate = False
            bMultiDate = True
        End If

        pnlreportdate.Visible = bSingleDate
        pnlstartenddates.Visible = bMultiDate
    End Sub

    Protected Function newGuid() As Guid
        Dim guid As Guid
        guid = guid.NewGuid()
        Return guid
    End Function

    Protected Sub btnGenerate_Click(sender As Object, e As System.EventArgs) Handles btnGenerate.Click
        Dim strOnClientClick As String = SetParameters()
        Page.ClientScript.RegisterStartupScript(Me.GetType(), newGuid().ToString(), strOnClientClick, True)
    End Sub
End Class

The reports.aspx page that is used for all reports:

<%@ Page Language="VB" AutoEventWireup="true" CodeFile="Reports.aspx.vb" Inherits="Pages_Reports" %>

<%@ Register Assembly="CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"     Namespace="CrystalDecisions.Web" TagPrefix="CR" %>





<meta http-equiv="X-UA-Compatible" content="IE=9,chrome=1" />

</pre>
<form id="form1">
<div>






</div>
</form>
<pre>

…and the code behind for reports.aspx:

Imports CrystalDecisions.Shared
Imports System.IO
Imports CrystalDecisions.CrystalReports.Engine

Partial Class Pages_Reports
    Inherits System.Web.UI.Page

    Protected strQueryString As String

    Protected Sub Page_Init(sender As Object, e As System.EventArgs) Handles Me.Init
        strQueryString = Request.QueryString.ToString()
        Page.Title = Replace(Request.QueryString("rpt").ToString(), ".rpt", "")
        If Not Page.IsPostBack Then
            'Do nothing
        ElseIf Session(strQueryString) IsNot Nothing Then
            Me.CrystalReportViewer1.ReportSource = Session(strQueryString)
            CrystalReportViewer1.DataBind()
            UpdatePanel1.Update()
        End If
    End Sub

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Dim strReportName As String = Request.QueryString("rpt")
            Dim strSingleDate As String = Request.QueryString("single")
            Dim strBeginDate As String = Request.QueryString("begin")
            Dim strEndDate As String = Request.QueryString("end")

            Dim bSingleDate As Boolean = False
            Dim bMultiDate As Boolean = False

            Dim arrSingleDate() As String = Utilities.AppSettingsFunction.getValue("SingleDate").Split(",")
            Dim arrMultiDate() As String = Utilities.AppSettingsFunction.getValue("MultiDate").Split(",")

            If Array.IndexOf(arrSingleDate, strReportName) <> -1 Then
                bSingleDate = True
                bMultiDate = False
            ElseIf Array.IndexOf(arrMultiDate, strReportName) <> -1 Then
                bSingleDate = False
                bMultiDate = True
            End If

            Dim connInfo As New ConnectionInfo
            Dim rptDoc As New ReportDocument

            'setup the connection
            connInfo = GetConnectionInfo()

            'load the Crystal Report
            rptDoc.Load(Server.MapPath( _
                  Utilities.AppSettingsFunction.getValue("ReportFolder") _
                  & strReportName))

            'add required parameters
            If bSingleDate Then
                rptDoc.SetParameterValue("REPORT_DATE", strSingleDate & " 23:59:59")
            End If

            If bMultiDate Then
                rptDoc.SetParameterValue("BEGIN_DATE", strBeginDate & " 00:00:00")
                rptDoc.SetParameterValue("END_DATE", strEndDate & " 23:59:59")
            End If

            'apply logon information
            For Each tbl As CrystalDecisions.CrystalReports.Engine.Table In rptDoc.Database.Tables
                Dim repTblLogonInfo As TableLogOnInfo = tbl.LogOnInfo
                repTblLogonInfo.ConnectionInfo = connInfo
                tbl.ApplyLogOnInfo(repTblLogonInfo)
            Next

            'Set, bind, and display Crystal Reports Viewer data source
            Session(strQueryString) = rptDoc
            Me.CrystalReportViewer1.ReportSource = Session(strQueryString)
            CrystalReportViewer1.DataBind()
        ElseIf Session(strQueryString) IsNot Nothing Then
            'Dim rptView As CrystalReportViewer = FindControl("rptViewer_" & report.Text)
            Me.CrystalReportViewer1.ReportSource = Session(strQueryString)
            UpdatePanel1.Update()
        End If
    End Sub

    Protected Function GetConnectionInfo() As ConnectionInfo
        Dim connInfo As New ConnectionInfo

        'setup the connection
        With connInfo
            .ServerName = Utilities.AppSettingsFunction.getValue("ServerName_" & Utilities.AppSettingsFunction.getValue("Environment"))
            .DatabaseName = ""
            .UserID = Utilities.AppSettingsFunction.getValue("UserID_" & Utilities.AppSettingsFunction.getValue("Environment"))
            .Password = Utilities.AppSettingsFunction.getValue("Password_" & Utilities.AppSettingsFunction.getValue("Environment"))
        End With
        Return connInfo
    End Function
End Class

Changing Oracle Databases at Run-time with Crystal Reports Viewers in ASP.NET

As part of the legacy application upgrade I’ve been doing, I decided to move the Crystal Reports to a Web application rather that having the users access them directly on Crystal Reports XI via Citrix, which has been the method they have been using.  This has several advantages, the primary one being speed.  One problem that plagued me was how to change the logon information at run-time, such that the application would automatically point at the correct Oracle database (development, test, or production) based on which server the report was being accessed from.

The solution I found was to set up the Oracle Database connection as a DSN in ODBC, and connecting to the ODBC DSN rather than using the Oracle Client directly.  This is not the only way to do it, but it seems to be the best way for my purposes.

In the code-behind file for the page that contains the Crystal Reports Viewer, I placed the following code that handles the same event that renders the viewer.


Protected Sub btnGenerate_Click(sender As Object, e As System.EventArgs) Handles btnGenerate.Click
Dim connInfo As New ConnectionInfo
Dim rptDoc As New ReportDocument

' setup the connection
With connInfo
.ServerName = "oracledsn" ' ODBC DSN in quotes, not Oracle server or database name
.DatabaseName = "" ' leave empty string here
.UserID = "username" ' database user ID in quotes
.Password = "password"  'database password in quotes
End With

' load the Crystal Report
rptDoc.Load(Server.MapPath(Utilities.AppSettingsFunction.getValue("ReportFolder") & ddlReports.SelectedValue))

' add required parameters
If pnlstartdates.Visible Then
rptDoc.SetParameterValue("REPORT_DATE", txtSingleDate.Text)
End If

' apply logon information

For Each tbl As CrystalDecisions.CrystalReports.Engine.Table In rptDoc.Database.Tables
Dim repTblLogonInfo As TableLogOnInfo = tbl.LogOnInfo
repTblLogonInfo.ConnectionInfo = connInfo
tbl.ApplyLogOnInfo(repTblLogonInfo)
Next

' Set, bind, and display Crystal Reports Viewer data source
Session("rptDoc") = rptDoc
Me.CrystalReportViewer1.ReportSource = Session("rptDoc")
CrystalReportViewer1.DataBind()
UpdatePanel1.Update()
 End Sub

The logon info above can easily be stored in web.config instead of hard-coding it as above.

Incidentally, I chose to put my Crystal Reports Viewer in an ASP.NET AJAX Update Panel, which is why the ReportSource of the viewer is stored in a Session variable.  If you choose to do this, the viewer must be databound in the Init event (not the Load event) to show up properly.


Protected Sub Page_Init(sender As Object, e As System.EventArgs) Handles Me.Init
If Not Page.IsPostBack Then
txtSingleDate.Text = Now.Date()
ElseIf Session("rptDoc") IsNot Nothing Then
Me.CrystalReportViewer1.ReportSource = Session("rptDoc")
CrystalReportViewer1.DataBind()
UpdatePanel1.Update()
End If
 End Sub

ADODB Issues in VB.NET with an Oracle Database

During my recent VB6 to VB.NET Forms upgrade adventure, I had to wade through quite a bit of code that, while still functional, should be upgraded further when time permits.  Most of this code is used for data access, and being that it was written back in the VB6 / Classic ASP days, Active Data Objects (ADO) was used rather than ADO.NET, which is the newer .NET equivalent of ADO.
A common feature of ADO which was widely used in this project was the Recordset.  An ADO Recordset Object is defined by Microsoft as an object that “represents the entire set of records from a base table or the results of an executed command.  At any time, the Recordset object refers to only a single record within the set as the current record.” (Link)

One of the problems in using the recordsets object with VB.NET and Oracle is that if the recordset is not closed and garbage collected once the program is done with it, an Oracle error reporting too many open cursors (ORA-01000) is often thrown.  Many Web sites say that one possible fix to this error is to increase the number of allowed open cursors, but this solution only hides bad code.  Instead, you must make certain that the object is closed, set to Nothing, and garbage collected.

To resolve this problem, I created a module that contains methods to destroy serveral types of objects: ADODB Connections, ADODB Recordsets, Excel applications, and Scripting FileSystemObjects.


Module modDestroyObjects

Public Sub DestroyConnection(ByRef cn As ADODB.Connection)
If cn IsNot Nothing Then
If Not cn.State = 0 Then
cn.Close()
End If
cn = Nothing
CollectGarbage("ADODB.Connection")
End If
End Sub

Public Sub DestroyRecordset(ByRef rs As ADODB.Recordset)
If rs IsNot Nothing Then
If Not rs.State = 0 Then
rs.Close()
End If
rs = Nothing
CollectGarbage("ADODB.Recordset")
End If
End Sub

Public Sub DestroyExcelApp(ByRef xlApp As Microsoft.Office.Interop.Excel.Application)
If Not (xlApp Is Nothing) Then
For i As Integer = xlApp.Workbooks.Count To 1 Step -1
xlApp.Workbooks(i).Close(False)
Next i
xlApp.Quit()
xlApp = Nothing
CollectGarbage("Excel.Application")
End If
End Sub

Public Sub DestroyFileSystemObject(ByRef fso As Scripting.FileSystemObject, ByVal str As String)
If Not (fso Is Nothing) Then
If fso.FileExists(str) Then
fso.DeleteFile(str)
End If
CollectGarbage("FileSystemObject")
End If
End Sub

Private Sub CollectGarbage(ByVal strName As String)
GC.Collect()

'' Uncomment below to see a Message Box each time Garbage Collection is manually invoked.
'MsgBox("Object " & strName & " has been collected.", MsgBoxStyle.OkOnly)
End Sub

End Module

By calling the appropriate method, the object is removed from memory immediately, rather than waiting for automatic garbage collection to take place.  While this does increase system overhead somewhat, I noticed no decrease in performance.  On an older / slower computer, this increase in overhead could be noticeable.  However, this ensures that — at least until I can rewrite the code using ADO.NET — the program can run many queries without throwing the dreaded ORA-01000 error.