How do i export crystal report in asp.net using the export button?

[Sohail1980]

Hi All,

I have a crystal report that loads default values from the database, using SQL server 2005. On my web page, I have 2 databound dropdownlist and upon selectedindexchanged the report must reload data based on selectedindex.

However only the first page of the report refreshes while the other pages remain with the initial data. When I try to export the report to excel using the export button from the crystalreportviewer toolbar it re-load the initial report.

Here is my code:

 
Imports System
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine.ReportDocument
 
Partial Class RptTraining
    Inherits System.Web.UI.Page
    Dim oRpt As New ReportDocument()
    Dim sqlConn As New SqlConnection
    Dim strConnection As String
    Dim MyCommand As New SqlClient.SqlCommand()
    Dim MyDA As New SqlClient.SqlDataAdapter()
    Dim myDS As New DataSet3()
    Dim x As Integer
    Dim AppID As Integer
    Dim yr As String
    Dim c As String
    Dim comp As String
    Dim ParaFields As New ParameterFields()
    Dim p1 As New ParameterField()
    Dim pd1 As New ParameterDiscreteValue()
 
    Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
 
        BindReport()
 
    End Sub
 
    Sub BindReport()
 
        x = ddlTraining.SelectedIndex
        If x = 1 Then
            AppID = 15
        ElseIf x = 2 Then
            AppID = 16
        ElseIf x = 3 Then
            AppID = 17
        ElseIf x = 4 Then
            AppID = 18
        End If
 
        strConnection = ConfigurationManager.ConnectionStrings("spaceConnectionString").ConnectionString
        sqlConn = New SqlConnection(strConnection)
        MyCommand.Connection = sqlConn
        MyCommand.CommandText = "SELECT  dbo.HR_company.CmpName AS CompanyName, dbo.HR_Master.SurName, dbo.HR_Master.ForeName,dbo.HR_Master.EmpType AS HMGrade, dbo.HR_depart.DptName AS Department, dbo.HR_post.PostName AS Post,dbo.HR_Training_Needs.TrainCourse AS Course, dbo.HR_Training_Needs.TrainSupplier AS CourseProvider,dbo.HR_Training_Needs.TrainPriority AS Priority FROM dbo.HR_company INNER JOIN dbo.HR_Master ON dbo.HR_company.Company = dbo.HR_Master.Company AND dbo.HR_company.InstalCode = dbo.HR_Master.InstalCode INNER JOIN dbo.HR_depart ON dbo.HR_Master.DptCode = dbo.HR_depart.Dptcode AND dbo.HR_Master.Company = dbo.HR_depart.Company AND dbo.HR_Master.InstalCode = dbo.HR_depart.InstalCode INNER JOIN dbo.HR_post ON dbo.HR_Master.PostCode = dbo.HR_post.PostCode AND dbo.HR_Master.Company = dbo.HR_post.Company AND dbo.HR_Master.InstalCode = dbo.HR_post.InstalCode INNER JOIN dbo.HR_Training_Needs ON dbo.HR_Master.AcSIPFNo = dbo.HR_Training_Needs.WinLogin WHERE(dbo.HR_Training_Needs.AppraisalID = 16)ORDER BY dbo.HR_Master.SurName, dbo.HR_Master.ForeName, Department"
        MyDA.SelectCommand = MyCommand  
        MyDA.Fill(myDS, "training")
        oRpt.Load("C:\Inetpub\wwwroot\HR\reports\TrainingNeeds.rpt")
        oRpt.SetDataSource(myDS)
        CrystalReportViewer1.ReportSource = oRpt
 
 
        p1 = New ParameterField()
        pd1 = New ParameterDiscreteValue()
        p1.ParameterFieldName = "year"
        pd1.Value = "2008"
        p1.CurrentValues.Add(pd1)
        ParaFields.Add(p1)
        CrystalReportViewer1.ParameterFieldInfo = ParaFields
 
 
 
    End Sub
 
    Protected Sub ddlTraining_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlTraining.SelectedIndexChanged
 
        ConfigureReport()
 
    End Sub
 
    Sub ConfigureReport()
 
        x = ddlTraining.SelectedIndex
        If x = 1 Then
            AppID = 15
        ElseIf x = 2 Then
            AppID = 16
        ElseIf x = 3 Then
            AppID = 17
        ElseIf x = 4 Then
            AppID = 18
        End If
 
        strConnection = ConfigurationManager.ConnectionStrings("spaceConnectionString").ConnectionString
        sqlConn = New SqlConnection(strConnection)
        MyCommand.Connection = sqlConn
        MyCommand.CommandText = "SELECT  dbo.HR_company.CmpName AS CompanyName, dbo.HR_Master.SurName, dbo.HR_Master.ForeName,dbo.HR_Master.EmpType AS HMGrade, dbo.HR_depart.DptName AS Department, dbo.HR_post.PostName AS Post,dbo.HR_Training_Needs.TrainCourse AS Course, dbo.HR_Training_Needs.TrainSupplier AS CourseProvider,dbo.HR_Training_Needs.TrainPriority AS Priority FROM dbo.HR_company INNER JOIN dbo.HR_Master ON dbo.HR_company.Company = dbo.HR_Master.Company AND dbo.HR_company.InstalCode = dbo.HR_Master.InstalCode INNER JOIN dbo.HR_depart ON dbo.HR_Master.DptCode = dbo.HR_depart.Dptcode AND dbo.HR_Master.Company = dbo.HR_depart.Company AND dbo.HR_Master.InstalCode = dbo.HR_depart.InstalCode INNER JOIN dbo.HR_post ON dbo.HR_Master.PostCode = dbo.HR_post.PostCode AND dbo.HR_Master.Company = dbo.HR_post.Company AND dbo.HR_Master.InstalCode = dbo.HR_post.InstalCode INNER JOIN dbo.HR_Training_Needs ON dbo.HR_Master.AcSIPFNo = dbo.HR_Training_Needs.WinLogin WHERE(dbo.HR_Training_Needs.AppraisalID = '" & AppID & "')ORDER BY dbo.HR_Master.SurName, dbo.HR_Master.ForeName, Department"
        MyDA.SelectCommand = MyCommand
        myDS.Clear()
        MyDA.Fill(myDS, "training")
        oRpt.Load("C:\Inetpub\wwwroot\HR\reports\TrainingNeeds.rpt")
        oRpt.SetDataSource(myDS)
        CrystalReportViewer1.ReportSource = oRpt
 
 
        p1 = New ParameterField()
        pd1 = New ParameterDiscreteValue()
        p1.ParameterFieldName = "year"
        If x = 1 Then
            pd1.Value = "2008"
        ElseIf x = 2 Then
            pd1.Value = "2009"
        ElseIf x = 3 Then
            pd1.Value = "2010"
        ElseIf x = 4 Then
            pd1.Value = "2011"
        End If
        p1.CurrentValues.Add(pd1)
        ParaFields.Add(p1)
        CrystalReportViewer1.ParameterFieldInfo = ParaFields
 
 
    End Sub
    Sub RefreshReport()
 
        x = ddlTraining.SelectedIndex
        If x = 1 Then
            AppID = 15
            yr = 2008
        ElseIf x = 2 Then
            AppID = 16
            yr = 2009
        ElseIf x = 3 Then
            AppID = 17
            yr = 2010
        ElseIf x = 4 Then
            AppID = 18
            yr = 2011
        End If
 
        c = ddlsbu.SelectedIndex
        If c = 1 Then
            comp = "ACTIVELINE LTD"
        ElseIf c = 2 Then
            comp = "HAREL MALLAC CO LTD (MAN.)"
        ElseIf c = 3 Then
            comp = "HM ENGINEERING LTD (MAN)"
        ElseIf c = 4 Then
            comp = "HM BUREAUTIQUE (MAN)"
        ElseIf c = 5 Then
            comp = "HAREL MALLAC CO LTD"
        ElseIf c = 6 Then
            comp = "HAREL MALLAC TRAVEL & LEISURE"
        ElseIf c = 7 Then
            comp = "HAREL MALLAC AVIATION LTD"
        ElseIf c = 8 Then
            comp = "HM ENGINEERING LTD."
        ElseIf c = 9 Then
            comp = "HM BUREAUTIQUE LTD."
        ElseIf c = 10 Then
            comp = "MCS EXECUTIVES"
        ElseIf c = 11 Then
            comp = "MAURITIUS COMPUTING SERVICES"
        ElseIf c = 12 Then
            comp = "HM TECHNOLOGIES (MANAGEMENT)"
        ElseIf c = 13 Then
            comp = "HM TECHNOLOGIES"
        End If
        strConnection = ConfigurationManager.ConnectionStrings("spaceConnectionString").ConnectionString
        sqlConn = New SqlConnection(strConnection)
        MyCommand.Connection = sqlConn
        MyCommand.CommandText = "SELECT  dbo.HR_company.CmpName AS CompanyName, dbo.HR_Master.SurName, dbo.HR_Master.ForeName,dbo.HR_Master.EmpType AS HMGrade, dbo.HR_depart.DptName AS Department, dbo.HR_post.PostName AS Post,dbo.HR_Training_Needs.TrainCourse AS Course, dbo.HR_Training_Needs.TrainSupplier AS CourseProvider,dbo.HR_Training_Needs.TrainPriority AS Priority FROM dbo.HR_company INNER JOIN dbo.HR_Master ON dbo.HR_company.Company = dbo.HR_Master.Company AND dbo.HR_company.InstalCode = dbo.HR_Master.InstalCode INNER JOIN dbo.HR_depart ON dbo.HR_Master.DptCode = dbo.HR_depart.Dptcode AND dbo.HR_Master.Company = dbo.HR_depart.Company AND dbo.HR_Master.InstalCode = dbo.HR_depart.InstalCode INNER JOIN dbo.HR_post ON dbo.HR_Master.PostCode = dbo.HR_post.PostCode AND dbo.HR_Master.Company = dbo.HR_post.Company AND dbo.HR_Master.InstalCode = dbo.HR_post.InstalCode INNER JOIN dbo.HR_Training_Needs ON dbo.HR_Master.AcSIPFNo = dbo.HR_Training_Needs.WinLogin WHERE(dbo.HR_Training_Needs.AppraisalID = '" & AppID & "' and HR_company.CmpName = '" & comp & "')ORDER BY HR_company.CmpName, HR_Master.SurName, HR_Master.ForeName, Department"
        MyDA.SelectCommand = MyCommand
        myDS.Tables("finalrating").Clear()
        MyDA.Fill(myDS, "finalrating")
        oRpt.Load("C:\Inetpub\wwwroot\HR\reports\FinalRating.rpt")
        oRpt.SetDataSource(myDS)
        CrystalReportViewer1.ReportSource = oRpt
 
 
        p1 = New ParameterField()
        pd1 = New ParameterDiscreteValue()
        p1.ParameterFieldName = "year"
        If x = 1 Then
            pd1.Value = "2008"
        ElseIf x = 2 Then
            pd1.Value = "2009"
        ElseIf x = 3 Then
            pd1.Value = "2010"
        ElseIf x = 4 Then
            pd1.Value = "2011"
        End If
        p1.CurrentValues.Add(pd1)
        ParaFields.Add(p1)
        CrystalReportViewer1.ParameterFieldInfo = ParaFields
 
 
    End Sub
    Protected Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Unload
 
        oRpt.Close()
        oRpt.Dispose()
    End Sub
 
    Protected Sub ddlsbu_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlsbu.SelectedIndexChanged
 
        RefreshReport()
 
    End Sub
 
End Class
 
 

How do I refresh the whole report on selectedindexchange?

How do I export the current report to excel?

Thanking you in advance for your help