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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 ( vb). thx.
Bob LearnedCommented:
Simple example:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
geraintcollinsAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.