[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Getting error "The database table "xxxx" cannot be found. Proceed to remove this table from the report?

Posted on 2006-05-31
9
Medium Priority
?
931 Views
Last Modified: 2012-08-13
I am using the CRAXDDRT control in a VFP application and I get the error when trying to set the SQLQuery String property of the report object.  The table does exist and the database connection string is pointing to it.  If I answer no to the question, the report runs fine.

I am using Crystal 11 against an ORACLE 9 database using ODBC.   I tried veriying the database before setting SQLQuery String which just moves the problem to the verify method (I get the same message at that point instead of when the SQLQueryString property is set).  Also, if I remove the SQLSetQueryString entirely, the report execution fails (as expected)

Any suggestions?

Code snippets follow:

.CreateTables()   & creates tables used by report
oRpt = .crystalcontrol.OpenReport(.RepFileName.Value)
* Set table locations according to assumptions 1 & 2 in thisform.ReadMe()
for i=1 to oRpt.Database.Tables.Count
        oRpt.Database.Tables(i).SetLogOnInfo(cDSN,cDSN,cUID,cPWD)
endfor
..............
 oRpt.sqlquerystring = thisform.QueryString  && error occurs
.............
oRpt.Printout(.f.)  && report runs fine

0
Comment
Question by:jmpjon
  • 5
  • 3
8 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 16804314
In general you cannot change the query from code.  You can add filters, sorting, grouping but if you are changing the SELECT part, Crystal will not allow it.

mlmcc
0
 

Author Comment

by:jmpjon
ID: 16808893
However, the original SQL that was being replaced was wrong so given the report runs OK once I ignore the message, the crystal control must (in some fashion) have accepted the SQL change and operated on it.  There is also nothing in the crystal documentation that implies that I cannot do what I am doing.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 16812193
Maybe CR XI fixed that problem.

Can you fix the report to run against the correct SQL?

mlmcc
0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 

Author Comment

by:jmpjon
ID: 16818391
I am not sure what you mean. The main reason for setting the SQL is that the original SQL refers to a different ORACLE schema (e.g. select a from "test"."table.  " and it needs to be "prod"."table". Note that all the forms have been converted from Crystal 7

I have got the form I was having the problem with working by unchecking the "dispaly alerts on refresh" box on the report options tab.  However, this only works for this particular form, other forms exhibit the same old behaviour even when I make the same change  (I had a moment when I thought I had solved the problem but unfortunately ...).  
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 16821599
Change you change the data source insude Crystal
Open the report
CLick DATABASE --> SET LOCATION

mlmcc
0
 

Author Comment

by:jmpjon
ID: 16834678
I finally fixed my initial problem  by removing references to the schema from all of the stored SQL (i.e. the SQL that had been stored with the report).  My problem now is on some reports it is not finding fields in some of the created tables even though the fields appear to be there.  I am going to investigate this further to see if there is any pattern (it affects about 50% of the reports).
0
 

Author Comment

by:jmpjon
ID: 16936636
The problem remains of changing code by setting the oRpt.SQLQUERYSTRING.  It works about 80% of the time and I am not sure why it fails the other 20%.  It fails when I try and run the report complaining that a field is not in the database table even though it is.  These are all converted reports from Crystal 7 which may be part of the problem however I have pointed them to the currect table, etc.   I also notice that I can set that SQLQUERYSTRING to the same character string that is already in SQLQUERYSTRING and it will fail - it seems to be something to do with the act of setting the field that creates a problem.  For most reports I can set the sqlquerystring without any problems. Any help would be really appreciated.
0
 

Accepted Solution

by:
jmpjon earned 0 total points
ID: 16938054
I have traced the problem down to the way the table is aliased in the report. For instance if the alias in the report is "test" then the SQLQUERYSTRING will be set to:

select "test"."FIELD1", "test1"."FIELD2", ... FROM "TEST" "test"

When I then set SQLQUERY STRING to "select * from  test", it actually changes SQLQUERYSTRING to:

select "test"."FIELD1", "test1"."FIELD2", ... FROM "TEST"

which fails since the field aliases do not match the table being selected from (one is lowercase and the other upercase).

Why Crystal does this is a mystery.  I can find no way around this other than to go back to the report and change the alias to upercase.

If anyone has another suggestion, I would be happy to hear it.  To me this is a bug in Crystal but no doubt BO will consider it a "feature"

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question