• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

Opening a crystal report at a specific record (ASP.Net with VB.Net) linked to SQL backend


I have a crystal report that is linked to a sql server 2000 database table via OLE DB (ADO) what is the best practice for opening the report to show only a specific record (essential that the user cannot see other records)

Is it:
1) Open the report and pass a parameter for the key field. If yes could you please provide an example.
2) Create a dataset with just the record relating to the key field and set the reports datasource to the dataset. This is working for the attached code but if there is a simpler way then I'd be happy to use it rather than having to do this for every report I need to open.
3) Some other way

Dim reportPath As String = Server.MapPath("Test123.rpt")
        Dim myConnection As New SqlConnection
        myConnection.ConnectionString = ConnectionStrings("MyDB").ConnectionString
        Dim MyCommand As New SqlCommand()
        MyCommand.Connection = myConnection
        MyCommand.CommandText = "Select * from TableName where FPlanID = " & intPlanID 'session variable
        MyCommand.CommandType = CommandType.Text
        Dim MyDA As New SqlDataAdapter()
        MyDA.SelectCommand = MyCommand
        Dim myDS As New DataSet()
        Dim rpt As New ReportDocument
        MyDA.Fill(myDS, "TableName")
        rpt.ExportToHttpResponse(ExportFormatType.PortableDocFormat, Response, False, "ExportedReport")

Open in new window

  • 3
  • 2
1 Solution
geraintcollinsAuthor Commented:
I'm having an issue with option 2 as the report now contains sub reports so I'm getting a login error when the report opens as the sub report references a different but related table, hmmm.
Bob LearnedCommented:
I would usually define a RowFilter for a DataTable.DefaultView, and bind to the DataTable.DefaultView.  I would think that you could do that with a Crystal Report.
geraintcollinsAuthor Commented:
Could you please provide some sample code for me to look at (asp.net vb). thx.
Bob LearnedCommented:
Simple example:

Dim dv As New DataView(Me.DataSet1.Tables(0))
dv.RowFilter = "Name = 'Bob' "
Dim rpt As New CrystalReport1()
geraintcollinsAuthor Commented:

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.

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