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

Posted on 2008-11-06
Last Modified: 2012-05-05

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

Question by:geraintcollins

    Author Comment

    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.
    LVL 96

    Expert Comment

    by:Bob Learned
    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.

    Author Comment

    Could you please provide some sample code for me to look at ( vb). thx.
    LVL 96

    Accepted Solution

    Simple example:

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

    Author Closing Comment


    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
    Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now