VB code for CR identical in two procedures but only one works

[Former Member]

I am trying to convert an application built by someone that has left our organization.  The app was written in VB in VS2002.  This is one of several that I am converting; others have gone fine.  The reason we are converting the apps is because the server they are on is dying and we need to move them to a more consolidated share structure on a new server.  The problem that I have run into is in a part of her code that runs a Crystal Report that has two different parts, Part1 and Part2 (as I'll refer to them).  The .NET front-end is connected to an Access db backend with her customized queries inside also.

The report works fine in the VS2002 version of the program and the second half of the report (Part2) runs fine in my VS2005 converted program.  The code in the two procedures is basically identical except for the report queries they run.  (I have even tried swapping the queries out in the code and it stills gives me the same error)  In debug mode, the line it fails on has the exact same result in it also for both procedures.  The two procedures even pick up the same same queried value for their variables, which get populated in both successfully, but it just fails at the very end, right when it is supposed to print the first report page, with the following error:

CrystalDecisions.CrystalReports.Engine.FormulaException was caught  Message="
Error in File C:\DOCUME~1\akomarek\LOCALS~1\Temp\temp_5b508b45-188e-45e9-81e6-939521728ba1 {E33AF2AD-BB1F-4270-BCDE-6ED2D8B9EEFB}.rpt:

SQL Expression error: Error in compiling SQL Expression :  SQL Expressions can not be used in this report..."   Source="CrystalDecisions.ReportAppServer.DataSetConversion"
StackTrace:  at CrystalDecisions.ReportAppServer.ConvertDotNetToErom.ThrowDotNetException(Exception e) at CrystalDecisions.ReportSource.EromReportSourceBase.GetLastPageNumber(ReportPageRequestContext reqContext) at CrystalDecisions.CrystalReports.Engine.FormatEngine.PrintToPrinter(Int32 nCopies, Boolean collated, Int32 startPageN, Int32 endPageN) at CrystalDecisions.CrystalReports.Engine.ReportDocument.PrintToPrinter(Int32 nCopies, Boolean collated, Int32 startPageN, Int32 endPageN) at PayReqProject.frmPayReqRpt.BindFirstReport() in C:\PROJECTS\Payroll\PayReqProject\frmPayReqRpt.vb:line 101

The offending line 101 is:     oRpt.PrintToPrinter(1, False, 0, 0)

Here is the code from Part1:

Private Sub BindFirstReport()
 
        Dim strOleDB As String
        Dim cnOleDB As New OleDbConnection(m_strConnectionString)
        Dim dsOleDB As New DataSet()
        Dim oRpt As New rptPaymentRequest()
        Dim daOleDB As New OleDbDataAdapter(strOleDB, cnOleDB)
 
        Try
            ' Build Select statement to query invoices from tblInvoices
 
            strOleDB = "SELECT * FROM qryFindPayReqsForBatchNbr WHERE BatchNbr = " & m_intBatchNbr & ";"
            daOleDB.Fill(dsOleDB, "qryFindPayReqsForBatchNbr")
 
            'Uncomment the following line to write out what is being returned so you can compare it to what you you are getting if you have errors
            'dsOleDB.WriteXmlSchema("c:\payreq_schema_test.xml")
                oRpt.SetDataSource(dsOleDB)
            'This code allows the report to go directly to the printer without previewing
 
                 oRpt.RecordSelectionFormula = " {qryFindPayReqsForBatchNbr.BatchNbr} = " & CStr(m_intBatchNbr) & " "
                 oRpt.PrintToPrinter(1, False, 0, 0)   *************FAILS ON THIS LINE**************
            ''This code would allow you to preview the report with the viewer before printing
            'CrystalReportViewer1.ReportSource = oRpt
            'CrystalReportViewer1.SelectionFormula = " {qryFindPayReqsForBatchNbr.BatchNbr} = " & CStr(m_intBatchNbr) & " "
            'CrystalReportViewer1.RefreshReport()
            'CrystalReportViewer1.DisplayGroupTree = False
 
        Catch e As OleDbException
            MsgBox(e.Message, MsgBoxStyle.Critical, "OleDB Error")
        Catch e As Exception
            MsgBox(e.Message, MsgBoxStyle.Critical, "General Error")
        End Try
    End Sub

I tried the report preview code (with the print commented out) and got the same error message.

I have been working to resolve this for many hours and am at a loss; any light anyone can shine on this for me would be greatly appreciated.

Thanks in advance,

Al

Message was edited by: Ludek Uher

[former_member183750 · Accepted solution]

Hello Al

As a test, I'd like you to comment out the record selection line:

oRpt.RecordSelectionFormula = " {qryFindPayReqsForBatchNbr.BatchNbr} = " & CStr(m_intBatchNbr) & " "

Theoretically, if you are using datasets, using a selecton on top is kind of loked down on as the selection should be part of the dataset creation. E.g.; upi're only adding overhead by having CR select data from a dataset which in effect is already selected data.

Another thing to try is run the report in the CR Designer as detailed in this blog. If the report does not work in the designer, see this forum thread:

http://scn.sap.com/thread/0001822727

- Ludek

Senior Support Engineer AGS Primary Support, Global Support Center Canada

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

Hi Ludek,

Thank for the suggestions, which I did try.  When I commented out the line you suggested, ran it, and I got te very same error message again.  This surprised me too because I thought ths was the SQL statement it meant in error message as well.

I opened the report in Designer and it runs fine; populates all of the fields but they are not filtered, it shows all records basically.

This is a very strange one... I am thinking about having a virtual machine built, installing VS2002 on it, making the modifications in it, and leaving the darn thing alone.  It's just that it should work and I cannot figure out what is stopping it.  Why would the Part2 report accept the SQL and run fine, while Part1 will not accept it?  The code is absolutely identical!

Thanks again,

Al

[former_member183750 · Accepted solution]

Hello Al

Good questions, but with a bit of work we should be able to get to the bottom of this (fingers crossed )

Anyhow, you said that this report works in CR designer, but you get unfiltered data. I suspect that this is because you are connecting to the database directly(?). The blog I referenced, shows you how to essentially mimic running the report with data coming from a dataset. E.g.; save the dataset to an XML and point the report at that. This should give you the data that is in the dataset. You can then use a selection formula in the designer to do more filtering if needed.

- Ludek

[Former Member · Accepted solution]

Hi Ludek,

I looked at the article and I'm not sure that would work in this case.  This is a .NET application used by several people with menus that are used to select the items they are looking for.  The report is returning a single record in each case for these reports and not really all that complicated.  In both cases, the procedures go through the exact same steps, populate the same variables, Part2 succeeds, and Part1 fails.  It's not like I'm passing an array of records for it to pull; the variable "m_intBatchNbr" has a single value in it always.

I just got back from talking with our server people and requested a stripped down VM be built so that I can install VS2002 to change the connection strings in the old version and leave well enough alone.  I am not going to waste any more time on this brain rattler.

Thank you for your help,

Al

[former_member183750 · Accepted solution]

Understood. I want you to do the steps there as a test though. Nothing else I can do at this point...

- Ludek

[Former Member · Accepted solution]

I was able to strip down a VM and install VS2002 and then load the old version of the code.  I converted the connection string, all of the reports, and got it working just fine.  Not sure why VS2005 did not like this app but I didn't want to waste another day on it.  All of the other apps I was asked to convert, worked out great in VS2005 and all of their reports work fine.

Thank you for your help,

Al

[former_member183750 · Accepted solution]

Sorry Al

Hopefully we'll be able to help next time

Have a great weekend

- Ludek