exporting datagrid to pdf or excel

https://www.experts-exchange.com/questions/20957177/Export-Dataset-Datagrid-into-PDF-format.html

I have tried exporting to excel and i dont like how it works?  My problem the datagrid usually gets built from a SQL stament  with a bunch of select options how can i send these so the crystal report can find the same data as my datagrid.  For example here is what the sql statement might look like but not always, i give  the user  the ability to choose from checkboxes, dropdowns, and textfields to select the appropriate records.

sql statement
SELECT * FROM TimeCards1, clocknumbers WHERE(Date_Time between CDate('10/17/2004') and CDate('11/16/2004') and Clock = '3715' and Planned = 'Y' )
I need to use this select in crystal  and i dont know how.  I know i can save this as a session variable and on the webform containing the crystal report use this parameter, but i am not sure how?

I am using crystal reports to export to pdf, but for some reason my whole database table is being loaded, what is going on.  Here is the code for the webform that holds the report.  Am i forgotting something in my export procedure?

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        If Not Page.IsPostBack Then
            bindreport()
            exportpdf()
        End If
    End Sub
    Sub bindreport()
'fill the report
        Dim ssql As String = Session("stringhold")
        Dim myReport As New timecards
        Dim objDataAdapter As New OleDbDataAdapter(ssql, strConn)
        objDataAdapter.Fill(objDataset, "TimeCards1")
        myReport.SetDataSource(objDataset)
        timeview.ReportSource = myReport
        timeview.DataBind()
    End Sub

    Sub exportpdf()
'exports to pdf
        Dim myexportoptions As CrystalDecisions.Shared.ExportOptions
        Dim mydiskfile As CrystalDecisions.Shared.DiskFileDestinationOptions
        Dim myexportfile As String
        Dim myReport2 As New timecards
        myexportfile = "c:\inetpub\wwwroot\tool\tool128\misc" & Session.SessionID.ToString & ".pdf"
        mydiskfile = New CrystalDecisions.shared.DiskFileDestinationOptions
        mydiskfile.DiskFileName = myexportfile
        myexportoptions = myReport2.ExportOptions
        With myexportoptions
            .DestinationOptions = mydiskfile
            .ExportDestinationType = .ExportDestinationType.DiskFile
            .ExportFormatType = .ExportFormatType.PortableDocFormat
        End With
        myReport2.Export()
        Response.ClearContent()
        Response.ClearHeaders()
        Response.ContentType = "application/pdf"
        Response.WriteFile(myexportfile)
        Response.Flush()
        Response.Close()
        System.IO.File.Delete(myexportfile)
    End Sub

I got the report to export to a pdf on my computer but when i try to export the pdf from the server i get a system has requested a time out error.  If i take out the the export procedure the report will load fine on the server,  I dont know why i am getting this error because, i have 3 reports out there that work fine and export to pdf's

dim myReport as new timecards
 Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here

        If Not Page.IsPostBack Then
            bindreport()
            exportpdf()
        End If
    End Sub
    Sub bindreport()
        Dim ssql As String = Session("stringhold")
        Dim objDataAdapter As New OleDbDataAdapter(ssql, strConn)
        objDataAdapter.Fill(objDataset, "TimeCards1")
        myReport.SetDataSource(objDataset)
        timeview.ReportSource = myReport
        timeview.DataBind()
    End Sub
    Sub exportpdf()

        Dim myexportoptions As CrystalDecisions.Shared.ExportOptions
        Dim mydiskfile As CrystalDecisions.Shared.DiskFileDestinationOptions
        Dim myexportfile As String
        '  Dim myReport2 As New timecards
        myexportfile = "c:\inetpub\wwwroot\tool\tool128\misc" & Session.SessionID.ToString & ".pdf"
        mydiskfile = New CrystalDecisions.shared.DiskFileDestinationOptions
        mydiskfile.DiskFileName = myexportfile
        myexportoptions = myReport.ExportOptions
        With myexportoptions
            .DestinationOptions = mydiskfile
            .ExportDestinationType = .ExportDestinationType.DiskFile
            .ExportFormatType = .ExportFormatType.PortableDocFormat
        End With
        myReport.Export()
        Response.ClearContent()
        Response.ClearHeaders()
        Response.ContentType = "application/pdf"
        Response.WriteFile(myexportfile)
        Response.Flush()
        Response.Close()
        System.IO.File.Delete(myexportfile)
    End Sub

Mmm not a very easy thing to do have you seen this article, you might be able to adapt some of its code? http://www.developerfusion.com/show/4266/

Does it take longer than 90 seconds to create your report, ASP.Net has a default script timeout of 90 seconds, which you are sure it will take that long to do what you want can be modified.

When i run it on my computer it takes about 5 seconds, to have the report exported.  Right now on the server i have set up 3 crystal reports that get exported to a pdf, they all work fine,  i have never tried building a report from a dataset before, like i am with this one, but that doesnt seem to be the problem because i can comment out the export procedure, and the report will load fine with all of the correct records.

Ok, i see what is happening for some reason the whole database table is being exported not the selected records, and the whole table has over 25000 records and ends up being over 1000 pages in the pdf.  i am not sure why this is happening maybe you could examine my code above.

It works like it is suppose to on my computer, only the selected records get exported

Yeah in the Export to PDF function when are you setting the SQL string to use??

And have you tried first to replace Session("stringhold") with "SELECT * FROM TimeCards1, clocknumbers WHERE(Date_Time between CDate('10/17/2004') and CDate('11/16/2004') and Clock = '3715' and Planned = 'Y' )" to see if that works on your server first??

You might want to look at this someone else has this working for them!
https://www.experts-exchange.com/questions/21152590/How-do-You-Export-Crystal-Report-to-PDF-Without-Writing-to-File-and-With-no-Memory-Issues.html 

The way it is being done above in my view is better because then you don't have to worry about saving the file to disk, the report is exported straight into the response objects output, that way you don't have to worry about files being stored up and having to set up permissions!!

It didnt work, it still tried to export the whole table, somewhere on the server it is loosing the dataset when it exports

Yeah I would suggest possibly trying to do it all in one sub as in the link above?

I tried that also, still isnt working

Could you possibly post the modified code so I can see that you did?

Here is my modified code timecards is the name of the report, the first part works because i can bring up the report, without exporting, and the correct records are shown.


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page her

        bindreport()
    End Sub
    Sub bindreport()
        Dim myReport As New timecards
        Dim ssql As String = Session("stringhold")
        Dim objDataAdapter As New OleDbDataAdapter(ssql, strConn)
        objDataAdapter.Fill(objDataset, "TimeCards1")
        myReport.SetDataSource(objDataset)
        timeview.ReportSource = myReport
        strConn.Close()
        strConn.Dispose()
        objDataset.Dispose()
        'export pdf
        Dim myexportoptions As CrystalDecisions.Shared.ExportOptions
        Dim mydiskfile As New CrystalDecisions.Shared.DiskFileDestinationOptions
        Dim myexportfile2 As String

        myexportfile2 = "c:\inetpub\wwwroot\tool\tool128\crystal\" & Session.SessionID.ToString & ".pdf"
        '  mydiskfile = New CrystalDecisions.shared.DiskFileDestinationOptions
        mydiskfile.DiskFileName = myexportfile2
        myexportoptions = myReport.ExportOptions
        With myexportoptions
            .DestinationOptions = mydiskfile
            .ExportDestinationType = .ExportDestinationType.DiskFile
            .ExportFormatType = .ExportFormatType.PortableDocFormat
        End With
        myReport.Export()
        Response.ClearContent()
        Response.ClearHeaders()
        Response.ContentType = "application/pdf"
        Response.WriteFile(myexportfile2)
        Response.Flush()
        Response.Close()
        System.IO.File.Delete(myexportfile2)
     
    End Sub

I haven't tested so might have made some typeos!

Nope, same issue, it works on my system but not on the server, it tries to load the entire table.

Maybe instead of using a oledbdataset, how can i get this to work?

You could try and use the ADO,  You need to make sure you have ADO included in your references though.
     
Dim myCn As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))

        Dim strSQL As String = "select * from sometable"

        'Fill dataset with Data
        '-----------------------------------------------------------------
        Dim SQLAdapter As New SqlDataAdapter(strSQL, myCn)
        Dim dstData As New DataSet
        SQLAdapter.Fill(dstData)


Have you stepped through the code in Debug mode to see what the string is that it is getting from the Session Variable?

Yep, like i said if i remove the export part of the sub procedure and just load the report it works fine, the correct records are showing up, on the server.  I can only export "correctly" in debug mode.  I am using msacces database so i thought i had to use the oledbadapter.  For some reason when i export on the server myReport is regenrating and loading the entire table.  If i could select the records from the report side, i think this problem would be solved.

I know before when i changed a report with a parameter i had to make sure i included that change again before i exported like i did below.  My basic layout of the timecards.rpt loads all the records from the database table "TimeCards1".  The dataadapter changes the records when i view the report, but when i try to export it is always jumping back to the orginial report and pulling the data from there.

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
        If Not Page.IsPostBack Then
            addparameters()
            exportpdf()
        End If
    End Sub
    Sub addparameters()
        Dim formid2 As Integer
        formid2 = Session("formid")
        Dim paramFields As New ParameterFields
        Dim paramField As New ParameterField
        Dim discreteVal As New ParameterDiscreteValue
        paramField.CurrentValues.Clear()
        paramField.ParameterFieldName = "formid"
        discreteVal.Value = formid2
        paramField.CurrentValues.Add(discreteVal)
        paramFields.Add(paramField)
        toolrequestviewer2.ParameterFieldInfo = paramFields
        toolrequestviewer2.ReportSource = "c:\inetpub\wwwroot\tool\tool128\toolrequest2.rpt"
        toolrequestviewer2.DataBind()
    End Sub
    Sub exportpdf()
        Dim formid2 As Integer
        formid2 = Session("formnum")

        Dim myexportoptions As CrystalDecisions.Shared.ExportOptions
        Dim mydiskfile As CrystalDecisions.Shared.DiskFileDestinationOptions
        Dim myexportfile As String
        Dim myReport As New toolrequest2
        myexportfile = "c:\inetpub\wwwroot\tool\tool128\misc" & Session.SessionID.ToString & ".pdf"
        mydiskfile = New CrystalDecisions.shared.DiskFileDestinationOptions
        mydiskfile.DiskFileName = myexportfile
        myexportoptions = myReport.ExportOptions
        With myexportoptions
            .DestinationOptions = mydiskfile
            .ExportDestinationType = .ExportDestinationType.DiskFile
            .ExportFormatType = .ExportFormatType.PortableDocFormat
        End With
        myReport.SetParameterValue("formid", formid2)
        myReport.Export()
        Response.ClearContent()
        Response.ClearHeaders()
        Response.ContentType = "application/pdf"
        Response.WriteFile(myexportfile)
        Response.Flush()
        Response.Close()
        System.IO.File.Delete(myexportfile)
    End Sub

But surely because you are creating a new Report when you set
Dim myReport As New toolrequest2 what if you changed that to:
Dim myReport As New toolrequest2 toolrequestviewer2.Report

Am really running out of ideas on this one.

I found this site that i believe addresses my issues
http://support.businessobjects.com/library/kbase/articles/c2011912.asp

Arggh so you just need the latest Service Pack then, man what a nightmare!  Suppose that should have been the first thing we tried doing!

Well i was reading, and by what the article said, and should never had worked at all, not even on my system.  So i will see, i am downloading the service pack, i will see if this solves it.

Ok well good luck, let us know what the outcome is!

Well i installed the service pack, same results, I am all out of ideas, i am pretty sure it would work if i did it other then at runtime.

I discovered that i need two merger modules installed on the server that deal with ado.net and dataset, thats why it works on my system, but not the server.

Man what a nightmare, well I am glad that you got it sorted, if there was an article that you found please can you post it so others can know your solution.

I actually found this pdf that walks through deploying a project, and making sure the correct merger modules are installed to run crystal report on a server.  Click on the link to get the pdf.  Here is the site
http://support.businessobjects.com/communityCS/TechnicalPapers/crnet_deployment.pdf.asp

I discovered that i needed these 2 meger modules installed, that deal with ado.net and dataset
VC_User_CRT71_RTL_X86_---.msm
VC_User_STL71_RTL_X86_---.msm
they contain the dlls that the database driver is dependent on.