Passing parameters from VB.Net to Crystal Reports

[Stephen]

Hey,

Can anyone help me? I am trying to figure out how to run a report that is based on information given during run time. For example a user is presented with two text boxes one asking for the toDate and the other for the fromDate. When the dates are entered a report is then run only showing data from those two dates.

Even just pointing me in the direction of relivent tutorials would be a huge help as I am struggling to find any on-line.

Thanks!

 

[DavidT_macktool]

Create date parameters in your crystal report and use them select the desired records.
Your record selection formula will look like this: The example uses the Invoices table and the Invoice Date field
{Invoices.Invoice Date} >= {?StartDate} and {Invoices.Invoice Date}<= {?EndDate}

Then you pass the parameters to the report. This example uses public date variables which are set on a date-picker form. (dStartdate & dEndDate).
Code like this:

In the PublicVariables Module:
Public dStartdate As Date
Public dEndDate As Date

On the date picker form:
dStartdate = editStartDate.Text
dEndDate = editEndDate.Text


On the report viewer form (or anywhere you call the report you would have this code:

        Dim MonthlySales As New [COLOR=Purple]rptSalesReport[/COLOR]

        MonthlySales.SetParameterValue("[COLOR=Purple]StartDate[/COLOR]", dStartdate)
        MonthlySales.SetParameterValue("[COLOR=Purple]EndDate[/COLOR]", dEndDate)

        CrystalReportViewer1.ReportSource = MonthlySales
        CrystalReportViewer1.Refresh()

The report name is rptSalesReport and the report parameters are named StartDate & EndDate.

 

[Stephen]

I've tried inputting the code but two things happen, the first after I have pressed the submit button I get a second dialog box asking me to put the dates in again. Then when the report comes up it displays nothing but the report headers and todays date! Any suggestions? Thanks!

 

[DavidT_macktool]

We'll get it fixed.
Can you post your code? RecordSelectStatement also.
When this happens, The report is not getting any or correct values for the parameters, You may need to re-do the select statement.
The parameter type should be DATE and discrete values checked.
Also, under report options I usually convert date/time to date.

I convert dates on the form .toShortDateString before setting the variables,
something like dStartdate = DateTime.Now.ToShortDateString

You can troubleshoot by adding a messagebox to display the values you are passing before they are passed.
Quick way: shows you the value and the format.
msgbox(dStartdate)

We'll get it fixed.

 

[Stephen]

I have double checked the select statement within the report and it is exactly as you posted it, also I have checked the parameters (they are both discrete and set at just simple date format.

The code on the date picker form is:


Private Sub UserInput_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
dStartdate = dtpStartDate.Text
dEndDate = dtpEndDate.Text
MsgBox(dStartdate)
MsgBox(dEndDate)
End Sub
End Class

And on the Crystal Report viewer form it is:

Private Sub Report_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim BloodReport As New BloodReport
BloodReport.SetParameterValue("DateStart", dStartdate)
BloodReport.SetParameterValue("DateEnd", dEndDate)
crBlood.ReportSource = BloodReport
crBlood.Refresh()
MsgBox(dStartdate)
MsgBox(dEndDate)

One thing I did notice is the values displayed in the MsgBoxes said todays date both before the date picket form loaded up and before the report loaded.

RecordSelectStatement = {User_Data.Date} >= {?datestart} and {User_Data.Date}<= {?dateend}

 

[DavidT_macktool]

Sorry, I forgot I use a 3rd party DateTimePicker.

Change:
dStartdate = dtpStartDate.Text
dEndDate = dtpEndDate.Text
To:
dStartdate = dtpStartDate.Value.ToShortDateString
dEndDate = dtpEndDate.Value.ToShortDateString

P.S.
you should be setting your variables when the user submits on the date picker form, you are doing it on the form load, before the user has a chance to change the values. Like an Accept Button.

    Private Sub btnAccept_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAccept.Click
        dStartdate = dtpEndDate.Value.ToShortDateString
        dEndDate = dtpEndDate.Value.ToShortDateString
        Me.Close()
    End Sub

 

[Stephen]

Still exactly the same problem! Does it make any difference if the dStartDate = dtpStartDate.Value.ToShortDateString is in the body of the form or part of the date picker itself? Also would it help any if I just used a text box instead of a date picker?

 

[DavidT_macktool]

You are setting the variables to the default value of the date/time picker on the form load event, you need to set them after the user has changed the values...

 

[Stephen]

Sweet!! Just one last thing if thats ok? Just wondering how to get rid of the dialog box that pops up when I click on Submit? It is essientially asking me to re enter the dates.

 

[DavidT_macktool]

Is it the Crystal reports Dialog box asking for the date valuse? If so, it usually has to do with the record select statement, try rebuilding the statement by double clicking on the parameters and the fields in the top left hand pane, sometimes that will clear the glitch.

If not, post the code dude...

 

[Stephen]

I changed the code for the date pickers to: Private Sub dtpEndDate_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtpEndDate.ValueChanged
dEndDate = dtpEndDate.Value.ToShortDateString
End Sub

But everything else I've pretty much kept the same, if I just click cancle on the crystal reports dialog box then it still works fine!

RecordSelectStatement = {User_Data.Date} >= {?datestart} and {User_Data.Date} <= {?dateend}

 

[DavidT_macktool]

I haven't got a clue why it is happening. I seem to remember it happening to me before an I just recreated a report from scratch to get rid of it.
Try closing VB.Net and resrtarting - if that doesn't clear it try a new report.

Create a new report called BloodReport2.
Add your 2 date parameters.
Create your record select statement.
Change your vb code from
Dim BloodReport AsNew BloodReport
to
Dim BloodReport AsNew BloodReport2

Run that and see what happens.

Sorry I don't have the correct answer for the cause, what you have should work.

 

[Stephen]

I have tried re-creating the report etc but doesn't seem to change anything! I'll ask around see if I can find out what it is. Cheers mate for all your help!