Solved

Passing store procedures to crystal reports using vb.net

Posted on 2013-06-15
3
2,257 Views
Last Modified: 2013-06-19
I'm calling a crystal report that has a store procedure using vb.net 2010 and I get  a invalid report file path error on this line of code - cryReport.SetDataSource(sdaCrystal)  Full code below.

Private Sub btnGoalsReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGoalsReport.Click
        Dim cryReport As New ReportDocument
        Dim command2 As New SqlCommand
        Dim commandCrystal As New SqlCommand
        Dim paramCrystal As SqlParameter
        Dim RepLocation, strID, strScope As String
        RepLocation = "C:\Temp\JJagoe\goal report.rpt"
        strID = "EPARCAH"
        strScope = "11"

        connectionStringCrystal = "Data Source=DRAWER;Initial Catalog=Live_Report_Production;User ID=GT_App;Password=xxxxx"
        connectionCrystal = New SqlConnection(connectionStringCrystal)
        commandCrystal.Connection = connectionCrystal
        commandCrystal.CommandType = CommandType.StoredProcedure
        commandCrystal.CommandText = "SP_EBO_Select_Hierarchy"

        paramCrystal = New SqlParameter("@p_network_id", strID)
        paramCrystal.Direction = ParameterDirection.Input
        paramCrystal.DbType = DbType.String
        commandCrystal.Parameters.Add(paramCrystal)
        paramCrystal = New SqlParameter("@p_ScopeType", strScope)
        paramCrystal.Direction = ParameterDirection.Input
        paramCrystal.DbType = DbType.String
        commandCrystal.Parameters.Add(paramCrystal)

        Dim sdaCrystal As New SqlDataAdapter
        Dim objGetDataSetCrystal As DataSet = New DataSet()

        Try
            connectionCrystal.Open()
            objGetDataSetCrystal.AcceptChanges()
            sdaCrystal.SelectCommand = commandCrystal
            sdaCrystal.Fill(objGetDataSetCrystal)
            commandCrystal.Dispose()
            connectionCrystal.Dispose()

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        cryReport.SetDataSource(sdaCrystal) - I get a invalid report file path error on this line
        CrystalReportViewer1.ReportSource = cryReport
        cryReport.Load(RepLocation)
        CrystalReportViewer1.Refresh()
0
Comment
Question by:gilweber3
3 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 167 total points
ID: 39250683
Why are you trying to pass a dataset to the report.  it is built against a stored procedure so you can't change it to a dataset.

Also I dont see where you set the report object to a rpt file.

mlmcc
0
 
LVL 10

Assisted Solution

by:adriankohws
adriankohws earned 167 total points
ID: 39251344
Using Stored Procedure and pass dataset to Crystal? So what you trying to achieve? If you wish to pass dataset to Crystal, then it doesn't matter you get data from text file or Excel or SQL statement or even Stored Procedure.

But if you wish to use stored procedure, I thought a better way is writing the stored procedure in the "Command" of Crystal Report and create the parameters you wish to pass over, that's about it. You direction is wrong.
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 166 total points
ID: 39251448
what mlmcc means is to retrieve the data from your SP in a dataset in your application and push this dataset to your report. Check http://emoreau.com/Entries/Articles/2006/09/Feeding-Crystal-Reports-from-your-application.aspx
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
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 is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

822 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