Solved

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

Posted on 2014-09-09
8
206 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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

758 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

24 Experts available now in Live!

Get 1:1 Help Now