Solved

Passing store procedures to crystal reports using vb.net

Posted on 2013-06-15
3
2,212 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
Comment Utility
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
Comment Utility
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 69

Accepted Solution

by:
Éric Moreau earned 166 total points
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
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…
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…

772 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

11 Experts available now in Live!

Get 1:1 Help Now