?
Solved

exporting to pdf is not working too many records

Posted on 2004-11-17
12
Medium Priority
?
406 Views
Last Modified: 2012-05-05
Hello experts, i am using visual studio.net webforms with the bundled version of crystal reports.  I am having trouble trying export just the selected records to a pdf, what is happening is that the entire database table is being exported, not my selected records.  What i am doing is on one page selecting the records that i wish to view, then when i click a button i use a session variable to pass the sql string to the webpage that holds the crystal report, from there i set the report source and export.  This all works fine on my system, but when i put this on the server, the export is loading the entire table.  Here is my code.  My report name is timecards

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        bindreport()
    End Sub

    Sub bindreport()
'use oledbdataapadpter to fill the report with selected records
        Dim strConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\inetpub\wwwroot\tool\tool128\data\mainttimecards.mdb")
        Dim objDataset As New DataSet
        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 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
0
Comment
Question by:tentavarious
  • 7
  • 5
12 Comments
 

Author Comment

by:tentavarious
ID: 12606152
If i cant get the oledbdataadapter to work is there a way i can send a parameter which contains a sql string and have my records get selected based on that string.  I cant specifically set an sql string on the report, because it always changes based on what records the user wants to see. But i could get the sql string from the user and set it in a parameter from there i wouldnt know what to do.
 An example of a sql string might look like this:
"SELECT * FROM TimeCards1, clocknumbers WHERE(Date_Time between CDate('10/17/2004') and CDate('11/16/2004') and Clock = '3715' and Planned = 'Y' )"
0
 

Author Comment

by:tentavarious
ID: 12606543
I was wondering this is like pushing data into the report, so would i need to install some different meger modules on the sever?
0
 
LVL 10

Expert Comment

by:ebolek
ID: 12606854
Why dont you use select expert in the report and filter the report data with it. Seems like that would solve your problem
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 

Author Comment

by:tentavarious
ID: 12607046
I dont know how using the sql string above.  Like i said i need to pass the report a parameter from the webform.  I dont know how to use sql strings with crystal reports, the syntax doesnt look the same.
0
 
LVL 10

Expert Comment

by:ebolek
ID: 12607099
yes it is not the same. If you use sql command it is diferent than TSQL little bit. That is why you should use select expert, Get your tables as your source and then filter the records using the select expert. Select expert is easy to use.It is under report menu, select expert
0
 

Author Comment

by:tentavarious
ID: 12607410
Well how could i get a sql statement like this to work the select expert?  This is one of the many statements that get created by the user on a different web form, depending on the records they want to see.

"SELECT * FROM TimeCards1, clocknumbers WHERE(Date_Time between CDate('10/17/2004') and CDate('11/16/2004') and Clock = '3715' and Planned = 'Y' )"

What i have is one webform that gives the user the ability to select from checkboxes, dropdowns, and entering data into textfields.  By doing this a sql statement gets generated differently by each user, i need a way to pass that string to the report and that is where i am lost.  I can't just assign a select expert to limit out fields, it has to be done at runtime to assure that the correct records are displayed.  There are only two ways i know of one is what i am trying to do above by using a dataset and adapter, the other is to pass a parameter onto the report.  Passing a parameter would probably work, but how would i assign the tables and fields to select the records by at runtime?
0
 
LVL 10

Expert Comment

by:ebolek
ID: 12607477
OK you can do  this in varius ways.

1. This is what I would do. I create parameters for all the prossible fileters.
start datae, end date, clock, planned etc.

After that
I create a stored procedure witht hese 4 paraemeters
I create the where clause of the sp in runtime using inline sql. If you dont know how to make inline sql stored procedures. ask it in SQL section of this site, Or investigate in google. It is easy.

This will make sure that filtered records are returned

2. This is the second way I dont like. Create parameetrs in the reprot again. Use the parameetrs in teh select expert
(Date_Time between ?startdate and ?enddate and Clock = ?clock and Planned = ?clock )

Then in your application if the user doesnt choose to set the start and end date for example set the parameters start and end date to 01/01/1900, 01/01/2300 like this. This will ensure that no records are filtered for this criteria. Because user didnt set them. I dont like this method but it is a trick

Regards
Emre

0
 

Author Comment

by:tentavarious
ID: 12607691
I was thinking about doing it that way, but there are 3 drop downs 4 checkboxes and 7 textfields that aid the user in selecting records.  Each control is related to a field.  So what would i do if the user decides not to use one.  Below is part of how the sql string gets built on the webform, it would be a lot easier if it would work using the dataset, like what i have in my first post.  It does work on my system but not the server.

 Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
  If txtCellName.Text.Length > 0 = True Then
            ssql = "SELECT * FROM TimeCards1, clocknumbers, Factory WHERE(Date_Time between CDate('" & txtStartd.Text & "') and CDate('" & txtEndd.Text & "') and "
        Else
            ssql = "SELECT * FROM TimeCards1, clocknumbers WHERE(Date_Time between CDate('" & txtStartd.Text & "') and CDate('" & txtEndd.Text & "') and "
        End If
        ssql = dosearch(ssql)
end sub

 Function dosearch(ByVal ds As String) As String
        'dynamically builds sql string to use in table building
        If txtMaintNum.Text.Length > 0 Then
            ds &= "Maint_Num = " & txtMaintNum.Text.Trim
            ds &= " and "
        End If
        Select Case txtDeptNum.SelectedValue
            Case 1
                ds &= "Dept_Num = '101'"
                ds &= " and "
            Case 2
                ds &= "Dept_Num = '102'"
                ds &= " and "
            Case 3
                ds &= "Dept_Num = '103'"
                ds &= " and "
            Case 4
                ds &= "Dept_Num = '105'"
                ds &= " and "
            Case 5
                ds &= "Dept_Num = '107'"
                ds &= " and "
            Case 6
                ds &= "Dept_Num = '122'"
                ds &= " and "
            Case 7
                ds &= "Dept_Num = '124'"
                ds &= " and "
            Case 8
                ds &= "Dept_Num = '128'"
                ds &= " and "
        End Select

        If txtWorkorder.Text.Length > 0 Then
            ds &= "WorkOrder_Num = " & txtWorkorder.Text.Trim
            ds &= " and "
        End If

        If txtCellName.Text.Length > 0 Then
            ds &= "TimeCards1.Maint_Num = Factory.MaintNum and Factory.Cell = '" & txtCellName.Text.Trim
            ds &= "' and "
        End If

        If txtclock.Text.Length > 0 Then
            ds &= "Clock = '" & txtclock.Text.Trim
            ds &= "' and "
        End If

        If chkPlan.Checked = True Then
            ds &= "Planned = 'Y'"
            ds &= " and "
        End If

        If chkunplan.Checked = True Then
            ds &= "Planned = 'N'"
            ds &= " and "
        End If

        If chkemergency.Checked = True Then
            ds &= "Emergency = 'Y'"
            ds &= " and "
        End If

        If chknonemergency.Checked = True Then
            ds &= "Emergency = 'N'"
            ds &= " and "
        End If
        If ds.EndsWith(" and ") Then
            ds = Left$(ds, ds.Length - 4) & ")"
        End If

        If ds.EndsWith("WHERE(") Then
            ds = ds.Replace("WHERE(", "")
        End If
        Return ds
    End Function
0
 
LVL 10

Expert Comment

by:ebolek
ID: 12607884
There is nothing wrong with what you do. If it works on your system then you should check the deployment to the serer. Did you add merge modules for the dtaaset when you make your setup project
0
 

Author Comment

by:tentavarious
ID: 12608337
these are the mergermodules that i added
Crystal_Database_Access2003.msm
Crystal_Database_Access2003.enu.msm
Crystal_Managed2003.msm
Crystal_regwiz2003.msm

I thought maybe i forgot one, that would explain why it doesnt work on the server.  I thought i needed a another module that dealt with pushing data into a report rather then pulling
0
 
LVL 10

Accepted Solution

by:
ebolek earned 2000 total points
ID: 12608353
ye si think you did

http://support.businessobjects.com/fix/merge_modules.asp 
check this document for dataset merge module. You need ado .net dataset merge module as well
0
 

Author Comment

by:tentavarious
ID: 12608712
Yep i found two merger modules that deal with ado.net and dataset once i install them it should work.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month13 days, 23 hours left to enroll

807 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