Problem w/Crystal Subreport/ADO/Delphi/Interbase

[Zogby]

Hi:

Trying to get subreport running here. Reports w/o subs run fine if I comment out all the subreport stuff in second code block below. W/o commenting out subreport stuff, not even the basic data from the dataset is displayed, only the variables I set in code. Problems totally associated with adding subreport. No errors reported.

Calling Code bloc:

//----------------------------------------------------------------------------
void __fastcall TReportDM:rintStdFormLetter(int Top, int Left,
int Height, int Width, int Kind, int Id)
{
// TStringList *LetterBody = new TStringList();;
// LetterBody->Clear();
String FileName;
String Distribution = "1";
switch (Kind)
{
case 1 : FileName = "WelcomeLetterMem";break;
case 2 : FileName = "WelcomeLetterAssoc"; break;
case 4 : FileName = "WelcomeLetterConv"; break;
case 5 : FileName = "NonMemberInvitation"; break; //Single Id
case 6 : {FileName = "NonMemberInvitation";
Distribution = "2";
break;} //Bulk mailing
case 10 : FileName = "AdvertiserInvitation"; break;
case 11 : FileName = "AdvertiserRenewal"; break;
case 12 : FileName = "AdvertiserCancel"; break;
case 14 : FileName = "CancelMemberLetter"; break;
case 16 : FileName = "CancelCreditLetter"; break;
case 18 : FileName = "ReminderLetter"; break;
}
// RecoverFormLetterFile(FileName);
// String FilePath = SuP.CommonPath + "\\letters\\" + FileName + ".txt"; //".rtf";
// LetterBody->LoadFromFile(FilePath);

//Create the report background
CreateReportBackground(50,/*Top*/800, /*Left*/ Height, Width, "Printing Standard Form Letters");

//Set up report name
String ReportName;
String SubReportName;
if (Kind == 10 || Kind == 11 | Kind == 12)
{
ReportName = "StdAdvLetter";
}
else
{
ReportName = "StdLetter";
}
SubReportName = "StdLetterText";
CrpeADO->ReportName = "";
String ReportsPath = SuP.ReportsPath;
CrpeADO->ReportName = ReportsPath + "\\" + ReportName + ".rpt";
// CrpeADO->Subreports->Name = ReportsPath + "\\" + SubReportName + ".rpt";
String FullSubReportName = ReportsPath + "\\" + SubReportName + ".rpt";
//Recover the report from the database
if (!RecoverReportFile(true, ReportName, ReportsPath))
return;
if (!RecoverReportFile(true, SubReportName, ReportsPath))
return;

//Set up record selection
if (Kind != 5)
{
Id = 0;
}

String Description;
if (Kind == 1){Description = "Membership Welcome";}
else if (Kind == 2){Description ="Associate Welcome";}
else if (Kind == 4){Description = "Membership Welcome";}
else if (Kind == 5){Description = "Membership Invitation";}
else if (Kind == 6){Description = "Membership Invitation";}
else if (Kind == 16){Description = "Credit Cancellation";}
else if (Kind == 18){Description = "Membership Renewal";}
else if (Kind == 14){Description = "Membership Cancellation";}


//Retrieve the formulas
CrpeADO->Formulas->Retrieve();

//DSet record selection formula
CrpeADO->Formulas->Name = "Id";
CrpeADO->Formulas->Formula->Text = IntToStr(Id);

//Set Report description header and Text File ID and distribution (bulk vs individual
CrpeADO->Formulas->Name = "TextFileId";
CrpeADO->Formulas->Formula->Text = "\"" + Description + "\"";
CrpeADO->Formulas->Name = "Description";
CrpeADO->Formulas->Formula->Text = "\"" + Description + "\"";
CrpeADO->Formulas->Name = "Distribution";
CrpeADO->Formulas->Formula->Text = "\"" + Distribution + "\"";

//Set Organization Data arameters
CrpeADO->Formulas->Name = "OrgAcronym";
CrpeADO->Formulas->Formula->Text = "\"" + SuP.suUserData->Values["Acronym"] + "\"";
CrpeADO->Formulas->Name = "OrgName1";
CrpeADO->Formulas->Formula->Text = "\"" + SuP.suUserData->Values["Name1"] + "\"";
CrpeADO->Formulas->Name = "OrgName2";
CrpeADO->Formulas->Formula->Text = "\"" + SuP.suUserData->Values["Name2"] + "\"";
CrpeADO->Formulas->Name = "OrgAddress";
CrpeADO->Formulas->Formula->Text = "\"" + SuP.suUserData->Values["Address"] + "\"";
CrpeADO->Formulas->Name = "OrgCityStateZip";
String CityStateZip = SuP.suUserData->Values["City"] + ", " +
SuP.suUserData->Values["State"] + " " +
SuP.suUserData->Values["Zip"];
CrpeADO->Formulas->Formula->Text = "\"" + CityStateZip + "\"";
CrpeADO->Formulas->Name = "OrgPhone";
String Phone = Trim(SuP.suUserData->Values["Phone"]);
if (Phone != "")
Phone = "Phone: " + Phone;
CrpeADO->Formulas->Formula->Text = "\"" + Phone + "\"";
CrpeADO->Formulas->Name = "OrgFax";
String Fax = Trim(SuP.suUserData->Values["Fax"]);
if (Fax != "")
Fax = "FAX: " + Fax;
CrpeADO->Formulas->Formula->Text = "\"" + Fax + "\"";
CrpeADO->Formulas->Name = "OffHours1";
CrpeADO->Formulas->Formula->Text = "\"" + SuP.suUserData->Values["Hours1"] + "\"";
CrpeADO->Formulas->Name = "OffHours2";
CrpeADO->Formulas->Formula->Text = "\"" + SuP.suUserData->Values["Hours2"] + "\"";
CrpeADO->Formulas->Name = "OffHours3";
CrpeADO->Formulas->Formula->Text = "\"" + SuP.suUserData->Values["Hours3"] + "\"";
CrpeADO->Formulas->Name = "OffHours4";
CrpeADO->Formulas->Formula->Text = "\"" + SuP.suUserData->Values["Hours4"] + "\"";
CrpeADO->Formulas->Name = "OffHours5";
CrpeADO->Formulas->Formula->Text = "\"" + SuP.suUserData->Values["Hours5"] + "\"";
CrpeADO->Formulas->Name = "OrgMessage";
CrpeADO->Formulas->Formula->Text = "\"" + SuP.suUserData->Values["Mesg"] + "\"";

//Set print options
CrpeADO->PrintOptions->Retrieve();
CrpeADO->PrintOptions->Copies = 1;
CrpeADO->WindowParent = PrintForm;

//Run the print job
RunADOReport(CrpeADO->ReportName, FullSubReportName, true, 0, "NI");

while (!PrintWindowClosed)
Application->ProcessMessages();
}


And to call to the ADO:

//---------------------------------------------------------------------------
void __fastcall TReportDM::RunADOReport(String sReportName, String sSubReportName, bool LoadSQL, int Control, String InvDate)
{
bool HaveSubReport = false;
if (LoadSQL)
{
if (Trim(sSubReportName) != "")
{HaveSubReport = true;}
String sReportSQL = sReportName;
sReportSQL.Delete(sReportSQL.Pos(".rpt"), 5);
sReportSQL = sReportSQL + ".sql";
qrADOReports->SQL->Clear();
qrADOReports->SQL->LoadFromFile(sReportSQL);

if (HaveSubReport)
{
String sSubReportSQL = sSubReportName;
sSubReportSQL.Delete(sSubReportSQL.Pos(".rpt"), 5);
sSubReportSQL = sSubReportSQL + ".sql";
qrADOSubReports->SQL->Clear();
qrADOSubReports->SQL->LoadFromFile(sSubReportSQL);
}

if ((Trim(InvDate) != "NI") && (Control >= itDues) && (Control <= itCredit))
{
InvDate = "\'" + InvDate + "\'";
if (Control == itDues)
qrADOReports->SQL->Add(Format("WHERE MEMPAY2.REMIND_LET = %s", ARRAYOFCONST(((String)InvDate))));
else if (Control == itCredit)
qrADOReports->SQL->Add(Format("WHERE MEMPAY2.CRDREM_LET = %s", ARRAYOFCONST(((String)InvDate))));
}
}
PrintWindowClosed = false;
Screen->Cursor = crHourGlass;
TSCRObject *adoRecordSet = new TSCRObject();
TSCRObject *adoSubRecordSet;
if (HaveSubReport)
adoSubRecordSet = new TSCRObject();

ICRDataSource *tInterface;
ICRDataSource *tSubInterface;
String eMessage;
trADOReports->StartTransaction();
if (HaveSubReport)
qrADOSubReports->Open();
qrADOReports->Open();


if (CrpeADO->HasSavedData)
CrpeADO->DiscardSavedData = true;

try
{
adoRecordSet->SetDataSet(qrADOReports);
tInterface = *adoRecordSet;
if (HaveSubReport)
{
adoSubRecordSet->SetDataSet(qrADOSubReports);
tSubInterface = *adoSubRecordSet;
}


CrpeADO->Subreports->Retrieve();
CrpeADO->Subreports[0]; //pointing to main report
CrpeADO->Tables->Retrieve();
CrpeADO->Tables[0].DataPointer = &tInterface;

if (HaveSubReport)
{
CrpeADO->Subreports[1]; //pointing to subreport
CrpeADO->Tables->Retrieve();
CrpeADO->Tables[0].DataPointer = &tSubInterface;
}
CrpeADO->Subreports[0]; //point to Main Report before Executing


/* CrpeADO->Tables->Retrieve();
CrpeADO->Tables[0].DataPointer = &tInterface;*/
CrpeADO->Execute();
}
catch(...)
{
Screen->Cursor = crDefault;
adoRecordSet->FreeInstance();
if (HaveSubReport)
adoSubRecordSet->FreeInstance();
if (CrpeADO->LastErrorString != "")
{
eMessage = ("There was an error running the report. The "
"report code was " + IntToStr(CrpeADO->LastErrorNumber));
eMessage = eMessage + " and the message was " + CrpeADO->LastErrorString;
ShowMessage(eMessage);
if (PrintForm != NULL)
{
delete PrintForm;
PrintForm = NULL;
}
return;
}
else
throw;
}
Screen->Cursor = crDefault;
while (!PrintWindowClosed)
Application->ProcessMessages();
adoRecordSet->FreeInstance();
if (HaveSubReport)
adoSubRecordSet->FreeInstance();
trADOReports->Commit();
if (PrintForm != NULL)
{
delete PrintForm;
PrintForm = NULL;
}
}

 

[Zogby]

I have no set up a link form sub to main report usng a formula field in the main and database field in sub (Subreport Link dialog) and still same. Whe calling report w/subreport, no results.

 

[Zogby]

I meant I "now" set up link.

 

[Zogby]

Whole new tack here. No subreport but same problem:

I have two crystal reports that include a memo field (Interbase BLOB).

In both cases, the BLOB is stored in a separate table and joined. In the first report I join on a specific data (date) but in the second
report I join on a <> to pull all of the blobs but use a where at runtime to limit the response.

Both of these SQL's return the proper dataset when run in IBConsole.

The problem is that the invoice report shows the blob text fine but the StdLetter report does not. I put the LETTER_ID field on the report and it does show up as selected but nothing from the blob field.

Any great insight appreciated here as I have been "looking" at this for days to a mind numbing lack of resiolution.

Thanks.

Best regards


----------------------------------------------------

Invoice SQL

SELECT
Members."ID", Members."NAMELAST", Members."NAMEFIRST", Members."DBA", Members."NUMBER", Members."STREET", Members."POBOX", Members."CITY", Members."STATE", Members."ZIP", Members."PHONE", Members."FAX",
MemPay2."RENEW_DATE", MemPay2."REMIND_LET", MemPay2."RENEW_DUES", MemPay2."DATE_PAID", MemPay2."CRDRPT_FEE", MemPay2."CRDRPT_RNW", MemPay2."CRDREM_LET", MemPay2."CRDRPT_PD",
DUESINVOICE."INVOICEDATE", DUESINVOICE."DUESMSG",
CREDITINVOICE."INVOICEDATE", CREDITINVOICE."CREDITMSG",
MEMPAY1."UNITS_MEM"
FROM
((("MEMBERS" Members INNER JOIN "MEMPAY2" MemPay2 ON
Members."ID" = MemPay2."ID")
LEFT OUTER JOIN "CREDITINVOICE" CREDITINVOICE ON
MemPay2."CRDREM_LET" = CREDITINVOICE."INVOICEDATE")
INNER JOIN "MEMPAY1" MEMPAY1 ON
MemPay2."ID" = MEMPAY1."ID")
LEFT OUTER JOIN "DUESINVOICE" DUESINVOICE ON
MemPay2."REMIND_LET" = DUESINVOICE."INVOICEDATE"


Invoice TTX

ID Number 1
NAMELAST String 40 HIGGINS
NAMEFIRST String 30 LELA
DBA String 40
NUMBER Number 737
STREET String 35 LONGFELLOW AVE
POBOX Number 0
CITY String 20 SONOMA
STATE String 4 CA
ZIP String 12 94709-2077
PHONE String 14 (707) 996-8774
FAX String 14 ( ) -
RENEW_DATE Date 04/01/1998
REMIND_LET Date 02/17/1998
RENEW_DUES Number 35
DATE_PAID Date 03/23/1998
CRDRPT_FEE Number 10
CRDRPT_RNW Date 07/28/1998
CRDREM_LET Date 06/29/1998
CRDRPT_PD Date 07/24/1998
INVOICEDATE Date
DUESMSG Memo
INVOICEDATE1 Date
CREDITMSG Memo
UNITS_MEM String 3 1-4

---------------------------------------------------------------------

StdLetter SQL

SELECT
M."ROA_ID",M."ID", M."STATUS_MEM", M."INV_LET", M."NAMELAST", M."NAMEFIRST", M."DBA", M."NUMBER", M."STREET", M."POBOX", M."CITY", M."STATE", M."ZIP", M."DPBC_2", M."DPBC_CHK",M."PHONE", CFL."LETTER_ID", CFL."LETTER_TEXT"
FROM
("MEMBERS" M JOIN "COMMON_FORM_LETTERS" CFL ON
M."STATUS_MEM" <> CFL."LETTER_ID")


Std Letter TTX

ROA_ID String 20 JALA
ID Number 5009
STATUS_MEM String 13 NON-MEMBER
INV_LET Date
NAMELAST String 40 STRONGARM
NAMEFIRST String 30 JAMES
DBA String 40
NUMBER Number 0
STREET String 35 4th St
POBOX Number 0
CITY String 20 BERKELEY
STATE String 4 CA
ZIP String 12 94207
DPBC_2 String 2 01
DPBC_CHK String 1 1
PHONE String 14 (415) 244-1234
LETTER_ID String 50 NonMemberInvitation
LETTER_TEXT Memo