Setting Report.SQLQuereyString problems

[Ruairi]

I have a report being printed from a VB front end (VB6, CR8, RDC). The report was created from a dictionary using Access as the back end. We had to upgrade to SQL 2000 because there was too much of a workload for access. Upsizing the DB to SQL took 30 minutes, changing the VB code took 5 minutes, and now i have this damn report that doesnt work. It returns a [SQL Server Driver] error saying
"syntax error near '`'" (the wierd quote CR uses around it's table aliases) The problem is that i don't have any of those quotes in the string i pass to the .SQLQuereyString property. As you can see i'm printing the querey to a text file. I can take this text file and paste it into the SQL Querey window of SQL 2000 and it works like a charm. Somehow CR is changing my querey before it sends it to SQL Server. can anyone help?

CRViewer1.ReportSource = Report

Print #99, RecordSelFormula

MsgBox RecordSelFormula

Report.SQLQueryString = RecordSelFormula

Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[kenhamady]

What changes did you make to the report?
Have you converted the RPT from Access to SQL?
Does it run OK in the report design environment? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[dcm]

Ruairi: If you post an example of your query string I may be able to help. As you correctly state CR does have this annoying habit of randomly requiring/ignoring quoted names and literals so if you have created any SQL Expressions which are used in your query this is the first place I would look. David C. Monks
david.monks@chase-international.com

http://www.chase-international.com

Accredited Crystal Decisions Enterprise Partner

 

[emcevoy]

Hello

If you have not done anything to the reports, you will need to do a convert database driver to the report as well as if the database is a different place a set location will be needed.

Once done, do a verify database to make sure that it says database up to date.

Once it is working properly in the designer, then we can look at the VB code.

hope the helps.
E. McEvoy
Crystal Reports Consultant

http://www.pacificridgesolutions.com

 

[Ruairi]

Thank you all for your comments. I got the problem solved, but i'll go ahead and answer your questions along with telling you what i had to do to fix it.I know i'm always curious about how a question turned out. The report's data source was a dictionary. Originally the data source for the dictionary was an access database. The idea of using dictionaries was that my client could write their own reports without being exposed to the complexity of the database and i could then import those reports to the vb front end for them. We then upgraded to a SQL 2000 database for proformance reasons. I changed the location of each table in the dictionary to the SQL database. After that the report stopped working with the error i mentioned above. I recreated the report (without the dictionary, it seems that changing the data source for a report with a dictionary causes a lot of problems). And copied the SQLQuereyString that the report wizard created and reworked my building of the querey string this as a model. It is now working again, a whole lot faster than it did in access. Heres the new querey string. The '{ oj }' syntax is what i was missing. Thanks for the intrest in helping me out.

SELECT
Production."thickness", Production."width", Production."pieces", Production."length", Production."shift", Production."volume", 
grade."description", endtrim."description "
From
{ oj ((("LumberSystem"."dbo"."Production" Production INNER JOIN "LumberSystem"."dbo"."grade" grade ON Production."graderec" = grade."rec") INNER JOIN "LumberSystem"."dbo"."endtrim" endtrim ON Production."endtrimrec" = endtrim."dosrec") INNER JOIN "LumberSystem"."dbo"."ProductionCenters" ProductionCenters ON Production."productioncenterrec" = ProductionCenters."dosrec") INNER JOIN "LumberSystem"."dbo"."groups" groups ON Production."grouprec" = groups."dosrec"}
Where Production.&quot;dag&quot; >= '3/14/2001' AND Production.&quot;dag&quot; <= '3/15/2001' AND ProductionCenters.&quot;description&quot; = 'SAWMILL' AND groups.description <> 'FENCING'
 Order By Production.&quot;width&quot; ASC, Production.&quot;length&quot; ASC, grade.&quot;description&quot; ASC

Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com