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
    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")
        timeview.ReportSource = myReport

        '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
        Response.ContentType = "application/pdf"

    End Sub
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tentavariousAuthor Commented:
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' )"
tentavariousAuthor Commented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

tentavariousAuthor Commented:
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
tentavariousAuthor Commented:
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?
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


tentavariousAuthor Commented:
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 "
            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
tentavariousAuthor Commented:
these are the mergermodules that i added

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
ye si think you did

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tentavariousAuthor Commented:
Yep i found two merger modules that deal with ado.net and dataset once i install them it should work.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.