Passing parameters from VB .NET to Crystal Reports 1

[EBECK]

I'm employed at a homeless shelter and have developed a VB .NET database application with several Crystal Reports. I'm having some difficulty with the following Visual Basic .NET code and Stored Procedure used to determine the income type of the homeless men. The VB code and Stored Procedure follows:

Private Sub btnCreateReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateReport.Click
Dim intIncomeTypeID As Integer ' Income Type ID
Dim intIndex As Integer ' For loop index
Dim prmBeginDate As ParameterDiscreteValue ' Parameter value object
Dim prmEndDate As ParameterDiscreteValue ' Parameter value object
Dim prmIncomeTypeID As ParameterDiscreteValue ' Parameter value object
Dim prmParameter As ParameterField ' Parameter object
Dim prmParameterList As ParameterFields ' Parameter collection object

' Validate the input fields.
If (Validate_Data()) Then

' Create the objects.
prmBeginDate = New ParameterDiscreteValue
prmEndDate = New ParameterDiscreteValue
prmIncomeTypeID = New ParameterDiscreteValue
prmParameter = New ParameterField
prmParameterList = New ParameterFields

' Clear the previous report source.
CRViewer.ReportSource = Nothing

' Load the Begin and End Date range.
prmBeginDate.Value = txtBeginDate.Text
prmEndDate.Value = txtEndDate.Text

' Get the Income Type ID for the Income Type selected.
intIncomeTypeID = 0
With dsDataSet.Tables("tblIncomeType")
For intIndex = 0 To .Rows.Count - 1
If (.Rows(intIndex).Item("IncomeType") = cmbIncomeType.Text) Then
intIncomeTypeID = .Rows(intIndex).Item("IncomeTypeID")
End If
Next
End With

' Load the Income Type ID.
prmIncomeTypeID.Value = intIncomeTypeID

' Add the parameter object to the collection.
prmParameter.ParameterFieldName = "@Original_BeginDate"
prmParameter.CurrentValues.Add(prmBeginDate)
prmParameterList.Add(prmParameter)

prmParameter.ParameterFieldName = "@Original_EndDate"
prmParameter.CurrentValues.Add(prmEndDate)
prmParameterList.Add(prmParameter)

prmParameter.ParameterFieldName = "@Original_IncomeTypeID"
prmParameter.CurrentValues.Add(prmIncomeTypeID)
prmParameterList.Add(prmParameter)

' Assign the parameter collection to the report viewer.
CRViewer.ParameterFieldInfo = prmParameterList

Try
' Load the report.
CRViewer.ReportSource = Application.StartupPath & "\..\frmReportMenWithIncomeCR.rpt"

Catch ex As Exception
MsgBox("The Men With Income Report could not be created for the following reason: " & ex.Message)

End Try

End If

End Sub

The Stored Procedure used by the Crystal Reports follows:
CREATE PROCEDURE spMenWithIncomeReport
(
@Original_BeginDate char (10),
@Original_EndDate char (10),
@Original_IncomeTypeID int
)
AS
SET NOCOUNT ON;
SELECT G.GuestMID, G.LastName, G.FirstName, G.MiddleInitial, G.SSNumber, IT.IncomeType, I.AmountPerMonth, GS.GuestMStatus, MAX(A.AttendanceDate) As MostRecentAttendance
FROM tblGuestM G INNER JOIN tblAttendance A ON G.GuestMID = A.GuestMID
INNER JOIN tblGuestMStatus GS ON G.GuestMStatusID = GS.GuestMStatusID
INNER JOIN tblIncome I ON G.GuestMID = I.GuestMID
INNER JOIN tblIncomeType IT ON I.IncomeTypeID = IT.IncomeTypeID
WHERE A.AttendanceDate >= @Original_BeginDate
AND A.AttendanceDate <= @Original_EndDate
AND IT.IncomeTypeID = @Original_IncomeTypeID
AND G.GuestMStatusID=1
GROUP BY G.GuestMID, G.LastName, G.FirstName, G.MiddleInitial, G.SSNumber, IT.IncomeType, I.AmountPerMonth, GS.GuestMStatus
GO


Issue #1 is that even though I pass three parameters to Crystal Reports, it prompts me for the first two parameters (@Original_BeginDate and @Original_EndDate). How do I get the Stored Procedure/Crystal Reports to recognize the three parameters passed from the Visual Basic code?

Issue #2 is that the data type of intIncomeTypeID is integer, but when loaded into prmIncomeTypeID.Value in the statement
prmIncomeTypeID.Value = intIncomeTypeID
its data type (placing the cursor over prmIncomeTypeID.Value in debug mode) is decimal. How can I ensure that prmIncomeTypeID.Value will store an integer?

Any ideas you can think of will be appreciated. Thank you in advance for your time and consideration.

Ed

 

[ThatRickGuy]

Here's the code I use.

First, a structure to hold the parm:

    Public Structure CrystalReportsParameters
      ''' <summary>Name of the Parameter</summary>
      Dim ParameterName As String
      ''' <summary>Value of the Parameter</summary>
      Dim ParameterValue As Object
    End Structure

Then, I create an array of that structure (called Parameters) and set the values according to my report. Then I use the following (excerts):

Private cr As CrystalDecisions.CrystalReports.Engine.ReportDocument

public sub LoadReport(...)
  cr = New CrystalDecisions.CrystalReports.Engine.ReportDocument()
  cr.Load(ReportPath & ReportName)

  Dim Parameter As General.Structures.CrystalReportsParameters

  'check if there are any parameters
  If Not Parameters Is Nothing Then
    'loop through all parameters
    For Each Parameter In Parameters
      Try
        'make sure parameter's name is set and add it to the report
        If Not Parameter.ParameterName Is Nothing Then cr.SetParameterValue(Parameter.ParameterName, Parameter.ParameterValue)
      Catch
        Log.LogItem("Specified Parameter: " & Parameter.ParameterName & " not found on report", Enumerations.enumLogStatusCode.NonFatalErrorLog)
      End Try
    Next
  End If

  CrystalReportViewer1.ReportSource = cr

  '...

The parameter's data type is determined in Crystal. Right click on the parameter field and select properties. There should be a drop down box for the data type.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.