p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

crystal_reports thread: Incorrect paging in CR.Net with parameters


Message #1 by "Michael McDonald" <mmcdonald@n...> on Wed, 30 Oct 2002 09:15:14 -0500
Hi list,

I have inherited a report which is displayed in the browser using the CR
Viewer. The original report had custom paging added to it and worked
fine. We then had a requirement to pass in 3 parameters to the report to
be displayed in the Report Header section. As soon as we added these in
the code, our custom paging code no longer worked properly. If we remove
the parameter stuff, everything works fine. I inserted the relevant
lines of code.

The actions that now occur are as follows:

Clicking Next Page: Never moves beyond page 2 of the report.

<snip>
Private Sub lnkNextPage_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles lnkNextPage.Click
	PopulateReport()	'Populates the dataset to which the
report binds
	Me.CrystalReportViewer1.ShowNextPage()
end sub
</snip>

Clicking Previous Page when on the last page" Returns you to page 1 of
the report, and not the previous page.

<snip>
Private Sub lnkPrevPage_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles lnkPrevPage.Click
	PopulateReport()	'Populates the dataset to which the
report binds
	Me.CrystalReportViewer1.ShowPreviousPage()
End Sub
</snip>

<snip>
 Private Sub PopulateReport()
        Dim cnConnection As New SqlClient.SqlConnection()
        Dim cmdCommand As New SqlClient.SqlCommand()
        Dim daSQLAdapter As New SqlClient.SqlDataAdapter()
        Dim dsKPIDataset As New dsKPI()
        Dim KPIRept As New KPIRpt()
        Dim prmReportParams As New ParameterFields()
        Dim prmTempParam As New ParameterField()
        Dim prmVal As New ParameterDiscreteValue()
        Dim dtSelected As DateTime
        Dim intDays As Integer
        Dim sDatePeriod As String

        Try
            cnConnection.ConnectionString =3D AppSettings("dsn")
            cmdCommand.Connection =3D cnConnection
            cmdCommand.CommandText =3D "usp_Report_KPI"
            cmdCommand.CommandType =3D CommandType.StoredProcedure

            cmdCommand.Parameters.Add("@nFacID", SqlDbType.Int).Value 
=3D
LblFacId.Text
            'cmdCommand.Parameters.Add("@nFacID", SqlDbType.Int).Value 
=3D
4904

            If drpPeriod.Visible Then
                dtSelected =3D CDate(drpPeriod.SelectedItem.Value)
                cmdCommand.Parameters.Add("@Actual",
SqlDbType.DateTime).Value =3D dtSelected
                cmdCommand.Parameters.Add("@Lower",
SqlDbType.DateTime).Value =3D DateAdd(DateInterval.Day, -35, dtSelected)
                cmdCommand.Parameters.Add("@UpperPrev",
SqlDbType.DateTime).Value =3D DateAdd(DateInterval.Day, -14, dtSelected)
                cmdCommand.Parameters.Add("@Upper",
SqlDbType.DateTime).Value =3D DateAdd(DateInterval.Day, -7, dtSelected)
                cmdCommand.Parameters.Add("@nPeriodDays",
SqlDbType.Int).Value =3D 7
                sDatePeriod =3D DateAdd(DateInterval.Day, -35,
dtSelected).ToShortDateString & " - " & dtSelected.ToShortDateString
            Else
                dtSelected =3D CDate(drpMonth.SelectedItem.Value)
                cmdCommand.Parameters.Add("@Actual",
SqlDbType.DateTime).Value =3D dtSelected
                cmdCommand.Parameters.Add("@Lower",
SqlDbType.DateTime).Value =3D DateAdd(DateInterval.Month, -5, 
dtSelected)
                cmdCommand.Parameters.Add("@UpperPrev",
SqlDbType.DateTime).Value =3D DateAdd(DateInterval.Month, -2, 
dtSelected)
                cmdCommand.Parameters.Add("@Upper",
SqlDbType.DateTime).Value =3D DateAdd(DateInterval.Month, -1, 
dtSelected)
                cmdCommand.Parameters.Add("@nPeriodDays",
SqlDbType.Int).Value =3D DateTime.DaysInMonth(Year(dtSelected),
Month(dtSelected))
                sDatePeriod =3D DateAdd(DateInterval.Month, -5,
dtSelected).ToShortDateString & " - " & dtSelected.ToShortDateString
            End If

            cmdCommand.Parameters.Add("@nMonthDays",
SqlDbType.Int).Value =3D DateTime.DaysInMonth(Year(dtSelected),
Month(dtSelected))
            cmdCommand.Parameters.Add("@WAM_ID", SqlDbType.Int).Value 
=3D
CInt(rdWAM.SelectedItem.Value)

            daSQLAdapter.SelectCommand =3D cmdCommand
            KPIRept.Load()


            'set the parameters
            'Report period
            prmTempParam.ParameterFieldName =3D "ReportPeriod"
            prmVal.Value =3D sDatePeriod
            prmTempParam.CurrentValues.Add(prmVal)
            prmReportParams.Add(prmTempParam)

            'Measurement unit
            prmTempParam =3D New ParameterField()
            prmTempParam.ParameterFieldName =3D "MeasureUnit"
            prmVal =3D New ParameterDiscreteValue()
            prmVal.Value =3D rdWAM.SelectedItem.Text
            prmTempParam.CurrentValues.Add(prmVal)
            prmReportParams.Add(prmTempParam)

            'Facility name
            prmTempParam =3D New ParameterField()
            prmTempParam.ParameterFieldName =3D "FacilityName"
            prmVal =3D New ParameterDiscreteValue()
            prmVal.Value =3D lblFacName.Text
            prmTempParam.CurrentValues.Add(prmVal)
            prmReportParams.Add(prmTempParam)

            'Supress logo
            prmTempParam =3D New ParameterField()
            prmTempParam.ParameterFieldName =3D "SupressLogo"
            prmVal =3D New ParameterDiscreteValue()
            prmVal.Value =3D "True"
            prmTempParam.CurrentValues.Add(prmVal)
            prmReportParams.Add(prmTempParam)


            CrystalReportViewer1.ParameterFieldInfo() =3D 
prmReportParams
            daSQLAdapter.Fill(dsKPIDataset, "KPI")
            If dsKPIDataset.Tables(0).Rows.Count > 0 Then
                KPIRept.SetDataSource(dsKPIDataset)
                CrystalReportViewer1.ReportSource =3D KPIRept
                pnlReport.Visible =3D True
                CrystalReportViewer1.Visible =3D True
            Else
                pnlReport.Visible =3D False
                CrystalReportViewer1.Visible =3D False
                lblMsg2.Text =3D "There was no data to report on."
                lblMsg2.Visible =3D True
                lnkNewSearch.Visible =3D True
            End If

        Catch ex As Exception
            ExceptionManager.Publish(ex)
        Finally
            dsKPIDataset =3D Nothing
            KPIRept =3D Nothing
            daSQLAdapter =3D Nothing
            cmdCommand =3D Nothing
            cnConnection =3D Nothing
        End Try

    End Sub
</snip>



Has anyone else encountered this issue?

I searched on the Crystal forums, and while I found others with the same
problem, I didn't find a resolution.

Thanks!
Michael 'Mick' McDonald
Developer
www.NuSoftSolutions.com
mmcdonald@n...

| [ngfx-crystal] member mmcdonald@n... =3D YOUR ID
| http://www.aspfriends.com/aspfriends/ngfx-crystal.asp =3D JOIN/QUIT

  Return to Index