Subject Re: Dynamic Master Detail SQL
Author sdbeames
Thanks Helen,
it seems to work at first, but only if I don't select a different
line in the master grid. Once you do, the same error occurs. This is
what I tried on my simple test form....

//---------------------------------------------------------------------------
void __fastcall TForm1::Button1Click(TObject *Sender)
{
if (IBOQuery2->Active)
IBOQuery2->Close();
IBOQuery2->SQL->Clear();
IBOQuery2->DataSource = NULL;

if (IBOQuery1->Active)
IBOQuery1->Close();
IBOQuery1->SQL->Clear();

IBOQuery1->SQL->Add("select CAT2NO,CAT1NO,CAT2 from CATEGORY2 where
CAT1NO < 200");
IBOQuery2->SQL->Add("select CAT3 from CATEGORY3 where CAT2NO=:CAT2NO");
IBOQuery2->DataSource = DataSource1;
IBOQuery1->Open();
IBOQuery2->Open();
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button2Click(TObject *Sender)
{
if (IBOQuery2->Active)
IBOQuery2->Close();
IBOQuery2->SQL->Clear();
IBOQuery2->DataSource = NULL;

if (IBOQuery1->Active)
IBOQuery1->Close();
IBOQuery1->SQL->Clear();

IBOQuery1->SQL->Add("SELECT CAT1NO, CAT1 FROM CATEGORY1 where
DEPT='EWS'");
IBOQuery2->SQL->Add("SELECT CAT2NO, CAT2 FROM CATEGORY2 where
CAT1NO=:CAT1NO");
IBOQuery2->DataSource = DataSource1;
IBOQuery1->Open();
IBOQuery2->Open();
}
//---------------------------------------------------------------------------

Anything else you can think of? Thanks once again. Steve.

> > I'd like to dynamically change the SQL of my Master & Detail
> >TIBOQuerys (used to interface to FreeReport 2.34 components),but I get
> >an error complaining that the Detail query can no-longer find the
> >fields from the first SQL query it used when it was first opened. How
> >can I tell it to forget about the first query's fields?

<SNIP>

> The order of doing things is wrong here; and an important step is
> missing (nil out the detail's Datasource property).
>
> Try this (sorry about the Pascal):
>
> procedure SwapSQL (NewSql1, NewSql2: string; NewDS:TDatasource);
> begin
> if IBOQuery2.Active then
> IBOQuery2.Close;
> IBOQuery2.SQL.Clear; // this causes Unprepare
> IBOQuery2.Datasource := Nil; // ESSENTIAL!!
>
> if IBOQuery1.Active then
> IBOQuery1.Close;
> IBOQuery1.SQL.Clear; // this causes Unprepare
>
> // Now we are clean so we set up the new structure
> IBOQuery1.Sql.Add(NewSql1);
> IBOQuery2.Sql.Add(NewSql2);
> IBOQuery2.Datasource := NewDS;
>
> // No need to call Prepare, as Open calls it
>
> // Include some try..except protection here
> ....
> IBOQuery1.Open;
> IBOQuery2.Open;
> ....
> end;
>
> On your buttonclick:
>
> ....
>
> var
> sql1, sql2: String;
> DS: TDatasource;
> begin
> //SelectedMaster is a string property you have set up
> if SelectedMaster = 'CATEGORY1' then
> begin
> sql1 := 'SELECT CAT1NO, CAT1 FROM CATEGORY1
> where DEPT='EWS';
> sql2 := 'select CAT3 from CATEGORY3
> where CAT2NO=:CAT2NO
> DS := dsCategory1;
> end
> else
> if SelectedMaster = ...............
> ...
> end;
> SwapSQL (sql1, sql2, DS);
> end;
>
> Helen
>