Problem with .rtm documents and Template.LoadFromFile

rbuser

Hi

This issue is a problem on one site but we cannot work out why.

For invoices, our process loads data into a temporary table and then calls
the code below.

If the user does not print the invoice but loops through again we get an
error ......

"Expected lexixal element not found: ) expected closing ')' in expression.
There was a problem parsing the WHERE clause in your SELECT statement --
Location of the error in the SQL statement is 4932 (line:118)"

This line is one past the SQL which is in the Query Designer SQL Statement,
presumably it now contains additional lines for the autosearchcriteria.

1. Is there any way we can get to display the final SQL? This may enable
us to see what is happening.
2. It appears as though the Template.LoadFromFile is not cleaning out the
previous code. Have I missed a step?

I would appreciate any advice you can give. The customer is getting very
abrasive with us and we are at a loss to see why this occurs in code which
is used many hundreds of times a day.

Thanks in advance

Philip L Jackson

Env.

Delphi D2007 Professional
Report Builder Enterprise 10.09


Template.New;
template.filename := Invdirectory + '\' +
Table1.FieldByName('REPORT_TEMPLATE').AsString;

Template.LoadFromFile;
CreateAutoSearchCriteria('Inv_Temp','DRAFT_INVOICE_NO',soBetween,'1,2',true);
AutoSearchFields[0].SearchExpression :=
Table1.FieldByName('DRAFT_INVOICE_NO').AsString + ',' +
Table1.FieldByName('DRAFT_INVOICE_NO').AsString;
CreateAutoSearchCriteria('Inv_Temp','CONTACT_NAME',soLike,'S',true);
AutoSearchFields[1].SearchExpression :=
OpenCal.UserName;
PrinterSetUp.DocumentName := 'Invoice ';
ShowAutoSearchDialog := FALSE;
DeviceType := dtScreen;
ppviewer1.report :=
MainDataModule.ppReport1;
ppviewer1.FirstPage;

nardmoseley

Have not heard of this issue before.

You can call Report.FreeModules to free the internal data and code modules.
(However, when loading an .rtm, this called internally prior to loading.)

To view the SQL, you can put a break point in the DADE plug-in code for
SQLToDataView. For example, if you are using ADO, put a break point in
daADO.pas, the method TdaADOQueryDataView.SQLChanged.



--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Hi

Found it thanks.

For the record the problem was that the 2nd AutoSearchCriteria was
referencing a database field which was not in the data dictionary so the SQL
was incorrectly formed.

This is what the SQL should look like...
FROM INV_TEMP INV_TEMP
WHERE
( INV_TEMP.DRAFT_INVOICE_NO BETWEEN -102 AND -102 )
AND ( INV_TEMP.CONTACT_NAME LIKE 'Philip L Jackson%' )


When I had the code in the debugger, the 1st pass SQL was incomplete, but by
chance worked, on the 2nd pass the following error was generated "Philip L
Jackson is not a valid integer value" .As can be seen below, on the 2nd
pass, the invoice numbers had not been passed to the query though I presume
that the value 'Philip L Jackson' had.

1st Pass with CONTACT_NAME missing from data dictionary (this worked)...
FROM INV_TEMP INV_TEMP
WHERE
( INV_TEMP.DRAFT_INVOICE_NO BETWEEN -102 AND -102 )

2nd Pass which generated the error....
FROM INV_TEMP INV_TEMP
WHERE ( INV_TEMP.DRAFT_INVOICE_NO BETWEEN )

Presumably with the 1st pass going wrong something inside Report Builder had
been left incorrectly set which is why on the 2nd pass it tried to send the
name to search for an invoice number??

Anyway - found and fixed.

Regards to all

Philip L Jackson