Click here to Skip to main content
15,895,256 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
This is my first time using Crystal reports.I have a store procedure and What i am trying to do is when i highlight a row in datagridview and click report button, that only the highlighted row gets sent to the report viewer page.I have tried some solutions here but no luck.

Report button code
VB
Imports System.Data.SqlClient
Imports System.IO
Imports CrystalDecisions.CrystalReports.Engine

Public Class frmView
    Dim cn As New SqlConnection("Data Source=.;Initial Catalog=DBSAS;Integrated Security=True")
    Dim cmd As New SqlCommand
    Dim da As New SqlDataAdapter
    Dim dt As New DataTable
    Dim i As Integer
    Dim a As New OpenFileDialog

    Private Sub btnRep_Click(sender As Object, e As EventArgs) Handles btnRep.Click 
            cn.Open()
        Dim report As New ReportDocument
        da.SelectCommand = New SqlCommand("EXEC usplatestDateEnrolled ", cn)
        report.RecordSelectionFormula = "{@studID}" & dgv1.SelectedRows(0).Cells(0).Value
        report.Load("C:\users\agent_edx44\My Documents\Visual studio 2012\projects\SASApp\Rep.rpt")
        frmReport.CrystalReportViewer1.ReportSource = report
        frmReport.CrystalReportViewer1.Refresh()
        frmReport.Show()
            cn.Close()
    End Sub

End Class


Store procedure code

SQL
USE [DbSAS]
GO
/****** Object:  StoredProcedure [dbo].[uspLatestDateEnrolled]    Script Date: 02/07/2016 12:35:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[uspLatestDateEnrolled]
    -- Add the parameters for the stored procedure here

@studID INT = NULL


AS
BEGIN

    SET NOCOUNT ON;
SELECT sh.SchoolYear,
        sh.Levels,sh.Section, sh.DateEnrolled ,
        SI.StudentID,SI.Surname,SI.FirstName,SI.MiddleName, SI.StudAddress , 
        SI.BirthDay,SI.Gender, SI.Nationality, SI.BirthPlace,
        SI.TelNum,SI.SchoolWhereGraduated , 
        SI.DatesWhenGraduated, SI.SchoolLastAttended,
        SI.SchoolAddress, SI.Note,SI.StudImage,
        PI.Father_FirstName,PI.Father_LastName,
        PI.Father_MI,PI.Father_Occupation, 
        PI.Father_TelNUm, PI.Mother_FirstName, PI.Mother_LastName,
        PI.Mother_MI,PI.Mother_Occupation,PI.Mother_TelNum,
        PI.Contact_FirstName,PI.Contact_LastName,PI.Contact_MI,
        PI.Contact_Mobile,PI.Contact_TelNum,PI.Contact_Address  
        FROM StudentInformation SI 
        JOIN StudentHistory SH  
            ON SI.StudentID = SH.StudentID
        JOIN ParentInformation PI
        ON PI.ParentID = SI.ParentID
        WHERE si.StudentID = @studID
        ORDER BY DateEnrolled DESC

            SELECT * FROM StudentHistory WHERE StudentID = @studID
                ORDER BY DateEnrolled DESC
            SELECT TOP 1 SchoolYear,
                Levels,Section, DateEnrolled as LatestDate
                FROM StudentHistory
                WHERE studentID = @studID
                ORDER BY DateEnrolled DESC


What I have tried:

When i run this code it says that

Quote:
Invalid report file path in this line

report.RecordSelectionFormula = "{@studID}" & dgv1.SelectedRows(0).Cells(0).Value

Can anyone help me to fix my code. I'm stuck here for almost an hour. Thanks in advance
Posted

1 solution

First problem is that you are setting the RecordSelectionFormula without loading the report.
Load the Report first.
VB
Dim rptDoc as New ReportDocument
rptDoc.Load("path to my Report document")

Delete the SQL Command & Data Adapter as they are not required - Crystal will handle connecting to the database for you.

Secondly, you will need to use
VB
report.SetParameterValue("paramName", paramValue)
to populate the Stored Procedure parameter instead of RecordSelectionFormula

Please note: Crystal Reports does not support multiple tables as a result from a Stored Procedure - refer;
Crystal Reports - Stored Procedure with multiple result sets[^]

Kind Regards
 
Share this answer
 
v2
Comments
Diether Silverious 7-Feb-16 20:04pm    
@anOther1 Can you help me to fix my codes please so that I can move on?
an0ther1 7-Feb-16 22:35pm    
Sorry Diether, I believe in the teach a person to fish philosophy.
A little research on your part will help you more than me writing the code.
I have updated my answer for you though to assist.

Kind Regards
Diether Silverious 8-Feb-16 1:30am    
Thanks another1 for your comment. Update: I tried to use this code and it runs
Dim report As New ReportDocument
report.Load("C:\users\agent_edx44\Documents\Visual studio 2012\projects\SASApp\SASApp\Rep.rpt")
frmReport.CrystalReportViewer1.ReportSource = report
frmReport.CrystalReportViewer1.Refresh()
However i want to populate the stored procedure, so I add this code:
report.SetParameterValue("@studID", dgv1.SelectedRows(0).Cells(0).Value)
But it produces same error..
LoadSaveReportException was unhandled - Invalid report file path in the code that I add.
an0ther1 8-Feb-16 17:37pm    
What type of application is this? Windows Forms, ASP, MVC, WPF?
Do you receive the error on the line report.Load("path")? If not, what line is the error on?
If you do get the error on this line, the issue is the name & path to the report file.
What type of authentication are you using in the Report? Integrated or Username and Password?
If you do not add in the line report.SetParameter does the ReportViewer show the report?
Diether Silverious 9-Feb-16 6:04am    
Windows Form Application is my application.. I do not receive an error on the line report.load("path"). If i do not add the line report.SetParameterValue("@studID", dgv1.SelectedRows(0).Cells(0).Value), it shows the report. But that is not the result that i expected. thanks mate for the reply

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900