Solved

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

Posted on 2014-09-09
8
207 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
  • 4
  • 4
8 Comments
 
LVL 100

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
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 100

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 100

Expert Comment

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

mlmcc
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The object model of .Net can be overwhelming at times – so overwhelming that quite trivial tasks often take hours of research. In this case, the task at hand was to populate the datagrid from SQL Server database in Visual Studio 2008 Windows applica…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

914 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now