Solved

Passing store procedures to crystal reports using vb.net

Posted on 2013-06-15
3
2,310 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
[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
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ASP.NET Calendar Control 5 54
Telerik RadEditor Control Save 8 39
Cannot interpret token '"' at position 10. 3 25
CSS styling problem 3 28
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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
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…

738 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