Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Passing store procedures to crystal reports using vb.net

Posted on 2013-06-15
3
Medium Priority
?
2,497 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 101

Assisted Solution

by:mlmcc
mlmcc earned 501 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 501 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 498 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

972 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