We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Crystal subreport with asp.net

ceema
ceema asked
on
Medium Priority
8,222 Views
Last Modified: 2012-06-27
Hello,

   I am using one report developed in crystal 9 with my asp.net application. I am using subreports also. I have to use one select statement with some condition for retrieving the data. The following code I am using for getting the report in aspx page. My problem is , I am not getting the subreport in my page while I am running it. The result is coming in xml page and query analizer(sql) is giving the exact result. Could any one tell me what is wrong with the following code? I am new in crystal report, so I don't know, whether it's like this I have to call subreport with a querystring!


crpt is my main report, repdoc is subreport name. when I am trying to print subreport name it's printing, and it's going to second forloop also, which shows it's taking subreport.

If you can't sort out the problem with this code, please give me a sample code for calling subreport with asp.net(VB code behind).

Thanks
Ceema


Imports System.Data
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports System.Data.SqlClient

    Dim crpt As Admini
    Dim myTable As CrystalDecisions.CrystalReports.Engine.Table
    Dim myLogin As CrystalDecisions.Shared.TableLogOnInfo

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        crpt = New Form1A
        Dim cmd As New SqlCommand
        Dim Da As New SqlDataAdapter
        Dim Ds As New DataSet
        Dim SchoolID As String
        Dim ClassID As String
        SchoolID = "Ab0"
        ClassID = "KG1-A"

' Main report logon

        For Each myTable In crpt.Database.Tables
            myLogin = myTable.LogOnInfo
            myLogin.ConnectionInfo.ServerName = "IB"
            myLogin.ConnectionInfo.DatabaseName = "FS"
            myLogin.ConnectionInfo.Password = "1"
            myLogin.ConnectionInfo.UserID = "sa"
            myTable.ApplyLogOnInfo(myLogin)
        Next
 Dim myConnection As SqlConnection
        Dim mySqlDataAdapter As SqlDataAdapter
        Dim PName As String
        myConnection = New SqlConnection("server=IB; uid=sa; pwd=1; database=FS")
        Da = New SqlDataAdapter("Select * From TmpForm1A where SchoolID='"& SchoolID & "'", myConnection)

'sub report logon

        Dim RepObject As CrystalDecisions.CrystalReports.Engine.ReportObject
        Dim SubRepDoc As CrystalDecisions.CrystalReports.Engine.SubreportObject
        Dim RepDoc As CrystalDecisions.CrystalReports.Engine.ReportDocument
        For Each RepObject In crpt.ReportDefinition.ReportObjects

            If RepObject.Kind = CrystalDecisions.Shared.ReportObjectKind.SubreportObject Then
               
                SubRepDoc = CType(RepObject, CrystalDecisions.CrystalReports.Engine.SubreportObject)
                RepDoc = crpt.OpenSubreport(SubRepDoc.SubreportName)
                For Each myTable In RepDoc.Database.Tables
                    myLogin = myTable.LogOnInfo
                    myLogin.ConnectionInfo.ServerName = "IB"
                    myLogin.ConnectionInfo.DatabaseName = "FS"
                    myLogin.ConnectionInfo.UserID = "sa"
                    myLogin.ConnectionInfo.Password = "1"
                    myTable.ApplyLogOnInfo(myLogin)
                Next

  Da.Fill(Ds.table(0))

            End If
        Next


             
        Me.CrystalReportViewer1.Visible = True
       
        RepDoc.SetDataSource(Ds)

        CrystalReportViewer1.ReportSource = crpt
        Ds.WriteXml("c:\backups\testfile.xml")

    End Sub
Comment
Watch Question

Author

Commented:
Hello Experts,
 
 I have increased the points to maximum, please help me to solve this issue.

Thanks
Ceema
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
The problem with the delay is when you asked your question.  It was about 1am here and on the weekend.  Also all EE experts are volunteers, there isn't somebody watching for questions all day.  

You aren't tying the subreport login to the subreport in the main report

Look at these samples

http://support.businessobjects.com/library/kbase/articles/c2010275.asp

http://support.businessobjects.com/communityCS/FilesAndUpdates/vbnet_web_samples.exe.asp

mlmcc

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Dear mlmcc,

   I am sorry, I haven't thought like that. I am sorry once again.

   Now I tried both the links and I copied the same code of the first link, but it's not displying the data for my subreport. I am pasting the code here. And one more thing, I have to use dataadapter, just like my previous code since, I have to fetch the data accroding to my query. The problem is, my client is having crystal reports already made, and we have to use the same thing only. So, we can't do anything on crystal report.

Can you suggest something to check whether my connection is changing by the connection string I am using on the fly?


Form1A is my main report name

Thank you
Ceema

The code is

Imports System.Data
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports System.Data.SqlClient
Imports CrystalDecisions.Web.Design
Public Class MForm1B
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Dim crSections As Sections
    Dim crSection As Section
    Dim crReportObjects As ReportObjects
    Dim crReportObject As ReportObject
    Dim crSubreportObject As SubreportObject
    Dim crDatabase As Database
    Dim crTables As Tables
    Dim crTable As Table
    Dim crLogOnInfo As TableLogOnInfo
    Dim crConnInfo As New ConnectionInfo

    Dim repDoc As New Form1A
    Dim subRepDoc As New ReportDocument

    Protected WithEvents CrystalReportViewer1 As CrystalDecisions.Web.CrystalReportViewer


    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       


        crDatabase = repDoc.Database
        crTables = crDatabase.Tables

        'Loop through each table and set the connection info
        'Pass the connection info to the logoninfo object then apply the
        'logoninfo to the main report

        For Each crTable In crTables
            With crConnInfo
                .ServerName = "IB"
                .DatabaseName = "FS"
                .UserID = "sa"
                .Password = "1"
            End With
            crLogOnInfo = crTable.LogOnInfo
            crLogOnInfo.ConnectionInfo = crConnInfo
            crTable.ApplyLogOnInfo(crLogOnInfo)
        Next
        crTable.Location = "FSchool" & ".dbo." & crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)


        'Set the sections collection with report sections
        crSections = repDoc.ReportDefinition.Sections

        'Loop through each section and find all the report objects
        'Loop through all the report objects to find all subreport objects, then set the
        'logoninfo to the subreport

        For Each crSection In crSections
            crReportObjects = crSection.ReportObjects
            For Each crReportObject In crReportObjects
                If crReportObject.Kind = ReportObjectKind.SubreportObject Then

                    'If you find a subreport, typecast the reportobject to a subreport object
                    crSubreportObject = CType(crReportObject, SubreportObject)

                    'Open the subreport
                    subRepDoc = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)

                    crDatabase = subRepDoc.Database
                    crTables = crDatabase.Tables

                    'Loop through each table and set the connection info
                    'Pass the connection info to the logoninfo object then apply the
                    'logoninfo to the subreport

                    For Each crTable In crTables
                        With crConnInfo
                            .ServerName = "IB"
                            .DatabaseName = "FS"
                            .UserID = "sa"
                            .Password = "1"
                        End With
                        crLogOnInfo = crTable.LogOnInfo
                        crLogOnInfo.ConnectionInfo = crConnInfo
                        crTable.ApplyLogOnInfo(crLogOnInfo)
                    Next
                    'crTable.Location = myDBName & "." & myOwner & "." & crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
                    crTable.Location = "FSchool" & ".dbo." & crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)

                End If
            Next
        Next

        ' Set the ReportDocument to the viewer
        ' The report previews when the form is constructed
        CrystalReportViewer1.ReportSource = repDoc

    End Sub



End Class

Author

Commented:
Hello,

   I have tried the following code to test my connectivity(later some one may find this useful to check their code). This I am using insted of the ....  crTable.Location = "FSchool" & ".dbo." & crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1) code of the above one.  It gives me the exact table,database ect..Still my subreport is not showing the data. Any one having any suggestion !


If (crTable.TestConnectivity()) Then
                               If (crTable.Location.IndexOf(".") > 0) Then
                    crTable.Location = crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
                Else
                        crTable.Location = crTable.Location
                End If
                         Response.Write("Connection is OK " & crTable.Location)
            Else
                Response.Write("Connection failed")
            End If

Author

Commented:
Dear mlmcc,

    Thank you, I have got it, I am posting here code for others reference who can use dataset for data retrievel. Form1A is my main report. And the sites given by mlmcc was so good

Thank yo so much

Ceema

Imports System.Data
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports System.Data.SqlClient
Imports CrystalDecisions.Web.Design
Public Class MForm1B
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
   
    Dim repDoc As New Form1A


    Protected WithEvents CrystalReportViewer1 As CrystalDecisions.Web.CrystalReportViewer


    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
              Dim Da As New SqlDataAdapter
        Dim Ds As New DataSet
        Dim SchoolID As String
        Dim ClassID As String
        SchoolID = "Ab1"
             
        Dim myConnection As SqlConnection
        myConnection = New SqlConnection("server=IB; uid=sa; pwd=1; database=FS")
        Da = New SqlDataAdapter("Select * From TmpForm1A where SchoolID='" & SchoolID & "'", myConnection)
        Da.Fill(Ds)
        repDoc.SetDataSource(Ds.Tables(0))
        CrystalReportViewer1.ReportSource = repDoc

    End Sub



End Class

Author

Commented:
Hello mlmmc,

   One more doubt regarding this section,

       I have tried the above posted code for one report with two subreports, but it's showing logon failure. So, do you have any suggestion for multiple subreports. I have tried the following code, it's working, but my problem is that, I have to allow the user to select some particular records, I don't know howto use dataadapter in the following query.

Thank you

Ceema


Dim Da As New SqlDataAdapter
        Dim Ds As New DataSet
        Dim Dai As New SqlDataAdapter
        Dim Dsi As New DataSet
        Dim SchoolID As String
        Dim ClassID As String
        SchoolID = "Ab1"
        ClassID = "KG1-A"
        crDatabase = repDoc.Database
        crTables = crDatabase.Tables

        'Loop through each table and set the connection info
        'Pass the connection info to the logoninfo object then apply the
        'logoninfo to the main report
        For Each crTable In crTables
            With crConnInfo
                .ServerName = "IBRA"
                .DatabaseName = "FSchool"
                .UserID = "sa"
                .Password = "123"
            End With
            crLogOnInfo = crTable.LogOnInfo
            crLogOnInfo.ConnectionInfo = crConnInfo
            crTable.ApplyLogOnInfo(crLogOnInfo)

            ' check wheter the connectivity is right or wrong
            If (crTable.TestConnectivity()) Then
                If (crTable.Location.IndexOf(".") > 0) Then
                    crTable.Location = crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
                Else
                   
                    crTable.Location = crTable.Location
                End If

                Response.Write(crTable.Location)
            Else
                Response.Write("Connection failed")
            End If

        Next



        'Set the sections collection with report sections
        crSections = repDoc.ReportDefinition.Sections

        'Loop through each section and find all the report objects
        'Loop through all the report objects to find all subreport objects, then set the
        'logoninfo to the subreport

        For Each crSection In crSections
            crReportObjects = crSection.ReportObjects
            For Each crReportObject In crReportObjects
                If crReportObject.Kind = ReportObjectKind.SubreportObject Then

                    '            'If you find a subreport, typecast the reportobject to a subreport object
                    crSubreportObject = CType(crReportObject, SubreportObject)

                    '            'Open the subreport
                    subRepDoc = crSubreportObject.OpenSubreport(crSubreportObject.SubreportName)
                    Response.Write(crSubreportObject.SubreportName)
                    crDatabase = subRepDoc.Database
                    crTables = crDatabase.Tables

                    '            'Loop through each table and set the connection info
                    '            'Pass the connection info to the logoninfo object then apply the
                    '            'logoninfo to the subreport

                    For Each crTable In crTables
                        With crConnInfo
                            .ServerName = "IBRA"
                            .DatabaseName = "FSchool"
                            .UserID = "sa"
                            .Password = "123"
                        End With
                        crLogOnInfo = crTable.LogOnInfo
                        crLogOnInfo.ConnectionInfo = crConnInfo
                        crTable.ApplyLogOnInfo(crLogOnInfo)
                    Next


                    If (crTable.TestConnectivity()) Then
                        If (crTable.Location.IndexOf(".") > 0) Then
                            crTable.Location = crTable.Location.Substring(crTable.Location.LastIndexOf(".") + 1)
                        Else
                            crTable.Location = crTable.Location
                        End If
                       
                        Response.Write(crTable.Location)
                    Else
                        Response.Write("Second Connection failed")
                    End If

                End If
            Next
            Dai.Fill(Dsi)
        Next

       
        CrystalReportViewer1.ReportSource = repDoc
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
SOrry I don't know how to use a dataadapter.

I can't get to this site from work, that's why no response before now.

Glad i could help

mlmcc

Author

Commented:
Hello mlmcc,

  Thank you. If you don't mind, could you please tell me, where I have to post, I mean whether it's in crystal report section or in asp.net section of experts-exchange to get response for this question? I have tried in asp.net and I got a reply that I have to post in crystal reports. I have tried a lot of other sites too, but of no hope.

Regards
Ceema
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
SHould be able to get help here.

mlmcc
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.