Solved

# exporting to pdf is not working too many records

Posted on 2004-11-17
Medium Priority
406 Views
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")
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.ContentType = "application/pdf"
Response.WriteFile(myexportfile2)
Response.Flush()
Response.Close()
System.IO.File.Delete(myexportfile2)

End Sub
0
Question by:tentavarious
• 7
• 5

Author Comment

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

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

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

Author Comment

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

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

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

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

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

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

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

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

check this document for dataset merge module. You need ado .net dataset merge module as well
0

Author Comment

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

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.