Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8171
  • Last Modified:

Crystal subreport with asp.net

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
0
ceema
Asked:
ceema
  • 6
  • 3
1 Solution
 
ceemaAuthor Commented:
Hello Experts,
 
 I have increased the points to maximum, please help me to solve this issue.

Thanks
Ceema
0
 
mlmccCommented:
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
0
 
ceemaAuthor 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
0
Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

 
ceemaAuthor 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
0
 
ceemaAuthor 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
0
 
ceemaAuthor 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
0
 
mlmccCommented:
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
0
 
ceemaAuthor 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
0
 
mlmccCommented:
SHould be able to get help here.

mlmcc
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now