Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-09-09
8
Medium Priority
?
216 Views
Last Modified: 2014-09-10
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
            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

        Catch ex As Exception
            ' error handling code omitted, but no error is raised.

        End Try
    End Sub

Open in new window

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?
0
Comment
Question by:petekipe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 40314746
When you view the report if you click REFRESH what happens?

mlmcc
0
 

Author Comment

by:petekipe
ID: 40314752
By the way, miRepairOrderNumber is defined as an Integer, as is @RepairOrderNumber in the SP.
0
 

Author Comment

by:petekipe
ID: 40314769
I Forgot to mention that before the empty report appears, I'm getting the good old "Load report failed." message twice, then the empty report appears.  If I click Refresh, I'm prompted for a parameter value.  When I supply a valid value, the report is displayed correctly.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 40314804
I haven't built a viewing application in .Net that allows for parameters.  Your code looks correct.

Do the error logs show anything useful for the load errors you see?

mlmcc
0
 

Author Comment

by:petekipe
ID: 40314817
Does Crystal produce an error log?  If so, where?  I'm logging errors that are raised in VB, but in this case, no error is being raised.  BTW, thanks for sticking with me on this -- much appreciated.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40315038
No Crystal doesn't create its own.  It would be either in the database error log or the system error log.

mlmcc
0
 

Author Comment

by:petekipe
ID: 40315666
Problem solved.  To try to debug the problem, I created a brand new project with one form, put a viewer control on it, and included the code from my original post in the form -- and the report worked perfectly.  So I went back to my original project, removed the viewer control from my report viewer form, put a new control on the form, and the problem went away!  Go figure...

Thanks for your help, mlmcc.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 40315711
I doubt I would have suggested that idea.

mlmcc
0

Featured Post

Vim Reference Guide

Vim is a powerful text editor favored by many sysadmins and developers - here are some commands that you'll want to keep in your back pocket!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question