Provide value to SQL SP parameter from VB.NET via CrystalReportDocument

[pkipe1]

(I'm having a tough time using this editor -- why can't I paste into it?)

I'm developing a CrystalReportDocument from within Visual Studio for the first time, and cannot get the report to work.  My environment is VB.NET in Visual Studio 2013, Crystal Reports for Visual Studio 2013 SP9, and SQL Server Express 2014.

The report Invoice.rpt appears in the Solution Explorer window of my VB.NET project. It uses a SQL Server SP containing one parameter, @RepairOrderNumber, that needs to be supplied at run time. Here's my code that attempts to invoke the report:

     Private Sub frmReportViewer_Load(sender As Object, e As EventArgs) Handles MyBase.Load

          Try

               Dim rptInvoice As New SD.Invoice          ' this is supposed to reference Invoice.rpt

               Dim crParameterField As New ParameterField

               Dim crParameterFields As New ParameterFields

               Dim crParameterDiscreteValue As New ParameterDiscreteValue

               crParameterField.Name = "@RepairOrderNumber"

               crParameterDiscreteValue.Value = 2        ' hard-coded for now to print invoice number 2 

               crParameterField.CurrentValues.Add(crParameterDiscreteValue)

               crParameterFields.Add(crParameterField)

               crViewer.ParameterField.Info = crParameterFields

               crViewer.ReportSource = rptInvoice

          Catch ex As Exception

               ' error logging code omitted, but no errors were logged.

          End Try

     End Sub

When I run the above code, the Invoice report is displayed in the viewer, however it's empty -- apparently no result set is being returned.  When I preview the report in the designer and supply a parameter value when prompted, the report displays perfectly.

What's wrong with the above code?

Thanks in advance...

[former_member183750 · Accepted solution]

Hi Peter

Re. pasting - I believe this is an SCN issue with IE 11 (something I have to check into) - would be nice if you could confirm that you are using IE 11. IE 10 and Firefox work (I actually prefer Firefox as I can paste in pictures when needed without saving them to HD).

As for the parameter issue. I would recommend updating to SP 10:

Then,before diving too deep into the code, see if a one line test app will work:

CrystalReportViewer1.ReportSource = <path to report>

The report engine should prompt for the parameter and DB logon. Fill those out. Does the report show up in the viewer with the data you expect? I suspect it will, so now I'd like you to modify your code as follows:

  Dim crReportDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument

  crReportDocument.Load(<path to your report>)

  crReportDocument.SetParameterValue("@RepairOrderNumber", 2)

' ' '  Or you should be able to use your param code

CrystalReportViewer1.ReportSource = crReportDocument

Make sure this is in Page Load.

For more info, use the search string 'crystal net parameter' - search in top right corner.

Samples: Crystal Reports for .NET SDK Samples - Business Intelligence (BusinessObjects) - SCN Wiki ( vbnet_win_paramengine.zip, vbnet_win_paramviewer.zip)

Developer Help files:

SAP Crystal Reports .NET SDK Developer Guide

SAP Crystal Reports .NET API Guide

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter

[pkipe1 · Accepted solution]

Hi Ludek,

Yes, I was using IE 11.  I'm on Chrome now.

I didn't mention in my original question that I was getting an "Error loading form." message.  Is more detail being logged somewhere?  The error wasn't raising an error in my VB code.

I'll update to SP 10 and retest, then let you know the outcome.  Thanks for your help on this...it's important that I get this up and running very soon.

Pete

[former_member183750 · Accepted solution]

Yeah. I looked up the bug report for SCN and IE 11, paste is there. Last note is from Aug 10; still unresolved - no ETA...

Umm, if the app becomes a critical issue, you can create a phone case where you will be assigned a dedicated support engineer:

Crystal Single Case Technical Support - SAP Business Objects US Online Store | SAP Online Store

(Just an FYI).

- Ludek

[pkipe1 · Accepted solution]

OK, I put SP10 on, but I'm still seeing the same problem. I tried a couple of different approaches, as follows.

This one caused an empty report to open:

            Dim rptInvoice As New ShopDoc.Invoice

            Dim crParameterField As New ParameterField

            Dim crParameterFields As New ParameterFields

            Dim crParameterDiscreteValue As New ParameterDiscreteValue

            crParameterField.Name = "@RepairOrderNumber"

            crParameterDiscreteValue.Value = miRepairOrderNumber

            crParameterField.CurrentValues.Add(crParameterDiscreteValue)

            crParameterFields.Add(crParameterField)

            crViewer.ParameterFieldInfo = crParameterFields

            rptInvoice.SetParameterValue("@RepairOrderNumber", miRepairOrderNumber)

            crViewer.ReportSource = rptInvoice

When I clicked Refresh in the Viewer, it prompted me for the parameter value, and the correct report was displayed.

The following guess at a solution caused a prompt for the parameter value, and then the correct report was displayed for less than a second, and then the report went empty:

            Dim rptInvoice As New ShopDoc.Invoice

            rptInvoice.SetParameterValue("@RepairOrderNumber", miRepairOrderNumber)

            crViewer.ReportSource = rptInvoice

BTW, the previous one makes the most sense to me -- passing a parameter value shouldn't be that big of a deal, in my estimation...

And finally, this one caused TWO "Load report failed." messages to be displayed, then worked just like the first one above -- the report was displayed empty, and when I clicked Refresh, I was prompted for a parameter value, after which the report was displayed correctly:

            Dim rptInvoice As New ShopDoc.Invoice

            Dim crParameterField As New ParameterField

            Dim crParameterFields As New ParameterFields

            Dim crParameterDiscreteValue As New ParameterDiscreteValue

            crParameterField.Name = "@RepairOrderNumber"

            crParameterDiscreteValue.Value = miRepairOrderNumber

            crParameterField.CurrentValues.Add(crParameterDiscreteValue)

            crParameterFields.Add(crParameterField)

            crViewer.ParameterFieldInfo = crParameterFields

            crViewer.ReportSource = rptInvoice

Any other ideas?

Pete

[former_member183750 · Accepted solution]

You've got some pretty weird behaviors going on there;

Report appears, then disappears(?).

Report prompts for parameter even though you provide the param in code?

Two load report fails?

I really would go to square one as I described in my initial post;

One liner app load report via the viewer - get that to work.

Next add code to load report via the engine - get that to work.

Add parameter code to the load report via engine.

All in a new app. The behavior of the old app does not make sense. If this is a web app, you will have to put the report into a session:

KBA: 1985571 - How to use sessions in web applications using the Crystal Reports viewer (the complete cod...

- Ludek

[pkipe1 · Accepted solution]

You're good, Ludek...there was something wrong with the viewer control on my original form.  When I created a new project with a new control, the report printed correctly.  So I removed the control from my original project and inserted a new one, et voila!

Many thanks!

[former_member183750 · Accepted solution]

Well, to be honest, it was the only shot in the dark we had left LOL

Glad it's done and happy coding,

- Ludek