Report not displaying through VB app after group is added to report.

[marabou]

I have had a report working in a VB app for ages. The customer wanted some modifications and all I did was to add a group in between the two I already had.

Now the report will not display in the VB app at all. Nothing else has changed and it doesn't make any sense. Please can anyone help as this is very urgent.

Thanks in advance

Marabou

 

[kenhamady]

Did you modify the report with a newer version of CR than the app's Crystal Engine?

Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[marabou]

No, its the same one we used for the original report. This system hasn't gone live yet because this report is broken.

 

[kenhamady]

This report runs fine in the CR design environment, but won't run in VB?
If you delete that group will it work?
Does the original report still work fine in VB?
Is there coding related to grouping in the APP?
Does this report use a recordset or connect to the tables? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[marabou]

The report runs fine in the CR environment.
After changing the group to the way I need it and then changing it back it no longer works.
The original report works fine in VB.
I am unsure how within the code we specify groups within Crystal. We have overwritten the SQL query that CR originally produced, which is mirrored in the VB code, however, we did not include any group bys as I have never seen CR produce Group Bys in it's SQL queries.

Normally I would use the CR SQL Designer, but this means we would have to use the CR parameter's dialog box within the program which I do not want.

I am using a SQL Server DSN to connect to the database. We have terrible problems trying to get reports displayed in VB, with many attempts at exactly the same report before it will display properly.

Here is the code we are using within VB.

CrystalReport1.Connect = "mars"

stSQL = " select docstk.seq, doc.code as DocCode,docstk.qty, " & _
" docstk.aqty, docstk.linetype, serial.serno, " & _
" Stock.Code as StockCode, L1.Name as SourceName, " & _
" L2.Name as DestinationName " & _
" from docstk left join docser on (docstk.id = docser.docstkid) " & _
" join doc on (doc.id = docstk.docid) " & _
" left join serial on (docser.serid = serial.id) " & _
" left join stock on (docstk.stkid = stock.id) " & _
" join Location L1 on (doc.SourceId = L1.id) " & _
" join Location L2 on (doc.DestinationId = L2.id) " & _
" where Doc.id = 409 " & _
" order By DocStk.Seq" ', DocSer.DocStkId ASC, Stock.Id"

CrystalReport1.SQLQuery = stSQL
CrystalReport1.ReportFileName = App.Path & "\Reports\DespatchNoteInternal.rpt"
CrystalReport1.PrintReport


Hope this goes further to explaining the problem.

 

[kenhamady]

Open the report and look at the SQL statement that it is using in the ShowSQL window.

Compare the Select statement with the one in the code. I don't believe that the app can change the select statement and you might have a different select statement now. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[marabou]

It is different, it always gets overwritten. Is this is where the problem lies? I didn't think it would be a problem to overwrite the sql.

If this is why I have a problem how can I get my report to work as the CR app does not support the joins properly.

I could use the SQL designer, however when putting parameters into a SQL Designed report and then calling the report from a VB app, it always ends with the CR dialog box being displayed and this isn't suitable. Is there a way around this as VB code cannot overwrite the SQL Designed report?

 

[kenhamady]

Well if I understand it correctly, the VB code has always been passed. The issues may be that the SQL in VB has to be tweaked to match the new report. Who wrote the SQL in the app? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[marabou]

The developer, who passed the query on to me from the VB app, which I edited to test in SQL Server and fit into CR.

I have written many reports using the SQL Designer but have never managed to get an app to pass a parameter from VB to the report without entering a parameter field into the report and adding that parameter into the record selection formula. By doing this you always get the Enter Parameter Values dialog box when you refresh the report data. Unfortunately this is unsuitable for the application in question.

As this is extremely urgent as this report is the only outstanding item from the project, we have decided to do a quick fix and go live with the Enter Parameter Values dialog box being visible. I now have come up with another problem. The database at the moment has around 500,000 records in it. By putting the parameter in the report rather than the SQL Designer query, reading of records is now taking around 10 minutes.

I suppose this question may solve both problems but is there anyway of passing a Where clause from VB that will overwrite the where clause in the SQL Designed query.

EG
CR SQL Designed - doc.id = 1

VB - doc.id = 2

so the report would then display all records when run through the app which have a doc.id of 2.

Hope this goes some more to explain the problem, hope you can help.

Thanks in advance

 

[kenhamady]

Passing the Where would be the same as passing the SQLQuery as you are now.

You get the report to run in the report designer. Have you compared the SELECT used by the designer to the SELECT hardcoded into the app? Getting these to be the same may solve the problem.

Passing a parameter CAN be done without showing the prompt. Show me the command you are using to pass the parameter, and I will tell you how to change it to not have the prompt appear. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[marabou]

Thanks Ken, I will stop trying to overwrite the SQL the CR app makes within the report.