Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2608
  • Last Modified:

Passing store procedures to crystal reports using vb.net

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
gilweber3
Asked:
gilweber3
3 Solutions
 
mlmccCommented:
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
 
adriankohwsCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now