tentavarious
asked on
exporting to pdf is not working too many records
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=
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(objDat
myReport.SetDataSource(obj
timeview.ReportSource = myReport
strConn.Close()
strConn.Dispose()
objDataset.Dispose()
'export pdf
Dim myexportoptions As CrystalDecisions.Shared.Ex
Dim mydiskfile As CrystalDecisions.Shared.Di
Dim myexportfile2 As String
myexportfile2 = "c:\inetpub\wwwroot\tool\t
mydiskfile = New CrystalDecisions.shared.Di
mydiskfile.DiskFileName = myexportfile2
myexportoptions = myReport.ExportOptions
With myexportoptions
.DestinationOptions = mydiskfile
.ExportDestinationType = .ExportDestinationType.Dis
.ExportFormatType = .ExportFormatType.Portable
End With
myReport.Export()
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"
Response.WriteFile(myexpor
Response.Flush()
Response.Close()
System.IO.File.Delete(myex
End Sub
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=
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(objDat
myReport.SetDataSource(obj
timeview.ReportSource = myReport
strConn.Close()
strConn.Dispose()
objDataset.Dispose()
'export pdf
Dim myexportoptions As CrystalDecisions.Shared.Ex
Dim mydiskfile As CrystalDecisions.Shared.Di
Dim myexportfile2 As String
myexportfile2 = "c:\inetpub\wwwroot\tool\t
mydiskfile = New CrystalDecisions.shared.Di
mydiskfile.DiskFileName = myexportfile2
myexportoptions = myReport.ExportOptions
With myexportoptions
.DestinationOptions = mydiskfile
.ExportDestinationType = .ExportDestinationType.Dis
.ExportFormatType = .ExportFormatType.Portable
End With
myReport.Export()
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/pdf"
Response.WriteFile(myexpor
Response.Flush()
Response.Close()
System.IO.File.Delete(myex
End Sub
ASKER
I was wondering this is like pushing data into the report, so would i need to install some different meger modules on the sever?
Why dont you use select expert in the report and filter the report data with it. Seems like that would solve your problem
ASKER
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.
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
ASKER
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?
"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?
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
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
ASKER
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
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
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
ASKER
these are the mergermodules that i added
Crystal_Database_Access200
Crystal_Database_Access200
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
Crystal_Database_Access200
Crystal_Database_Access200
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yep i found two merger modules that deal with ado.net and dataset once i install them it should work.
ASKER
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' )"