ModifySQL code problem. 1

[delphiman]

Delphi6 seems ready to accept the following ....

procedure TParentDM.IBFatherDataSetAfterScroll(DataSet: TDataSet);
begin
IBFatherDataSet.ModifySQL.Strings 'UpdateTRNSFR + set + P_NO =:FATHERP_NO';
end;

but I get a "(" expected but string constant found" exception ... with the cursor
located immediately after FATHERP_NO .. but before the ";".

Can anyone please tell me what is wrong with the above?

 

[Robertio]

If you replace your code with:

IBFatherDataSet.ModifySQL.Clear;
IBFatherDataSet.ModifySQL.Text := 'UpdateTRNSFR + set + P_NO =:FATHERP_NO';


It should work.

If you are using the strings property you have to tell it which string it is you want to edit, ie

IBFatherDataSet.ModifySQL.Strings[1] := 'Line 1';
IBFatherDataSet.ModifySQL.Strings[2] := 'Line 2';

Can't remember if strings starts at entry 0 or 1.


The strings property is handy if you just want to edit one line of a multi-line sql command, but generally I find it easier just to clear the whole lot and repopulate the SQL (makes the code easier to follow)

 

[delphiman]

Thanks .. that got Delphi to compile.

But THIS has just GOT to be maddening!! :-(

Whilst I acknowledge it is probably the subject for another thread perhaps you can chuck some light on the following?

I now get the following exception ..

'Size Mismatch - Field P_NO size is too small for data'

Even after I:-
1. Changed the script for the DataBase wherein I made absolutely sure that
in BOTH PARENT and TRSNFR field P_NO is now (TNOTNULLID) INTEGER.
2. Deleted the entire dataBase.
3. Ran the script all over again.
3. Deleted and replaced the TdataSet of both PARENT and TRNSFR. :-(

 

[delphiman]

that should read

in BOTH FATHER and TRSNFR field P_NO is now (TNOTNULLID)

 

[delphiman]

That should read..

BOTH FATHER and TRSNFR field P_NO is now (TNOTNULLID)

 

[delphiman]

>If you are using the strings property you have to tell it which string it is you want to edit, ie

>IBFatherDataSet.ModifySQL.Strings[1] := 'Line 1';
>IBFatherDataSet.ModifySQL.Strings[2] := 'Line 2';

>Can't remember if strings starts at entry 0 or 1.

I tried the following ....

with ParentDM.IBdsChild do
begin
IBdsChild.SelectSQL.Clear;
IBdsChild.SelectSQL.Strings[0] := 'select *';
IBdsChild.SelectSQL.Strings[1] := 'from';
IBdsChild.SelectSQL.Strings[2] := 'CHILD t1,TRNSFR t2';
IBdsChild.SelectSQL.Strings[3] := 'where';
IBdsChild.SelectSQL.Strings[4] := 't1.P_NO = t2.P_NO';
end;

... but I got the following exception whether I start with [0] OR [1]

'List index out of bounds (0)'. Process stopped. Use Step or Run to continue.


I also tried the following ...

IBdsChild.SelectSQL.Text := 'select * + from + (CHILD t1, TRNSFR t2) + where t1.P_NO = t2.P_NO';

... but I got the following exception

'Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 9 +'.

It looks like the kind of thing I need. But what am I doing wrong?

Any more suggestions?

 

[svanels]

Try IBdsChild.SelectSQL.Clear;
IBdsChild.SelectSQL.Add('select *');

etc..
Steven van Els
SAvanEls@cq-link.sr

 

[delphiman]

Thanks Steven.

Still doesn't work either and I get the following exception.

"Project MDModel.exe raised exception class EConvertError with message ''0.0' is not a valid timestamp'.

IBdsChild.SelectSQL.Clear;
IBdsChild.SelectSQL.Add ('select *');
IBdsChild.SelectSQL.Add ('from');
IBdsChild.SelectSQL.Add ('CHILD t1,TRNSFR t2');
IBdsChild.SelectSQL.Add ('where');
IBdsChild.SelectSQL.Add ('t1.P_NO = t2.P_NO');

I might mention that if I put a BreakPoint at the beginning and trace through each of the above steps (with F8) the contents of SelectSQL remains = () throughout.

Does that say something that I am missing?

 

[LucieLastic]

To 'inspect/modify' the sql strings during a debug/breakpoint, you need the index too.

To inspect the first line:
IBdsChild.SelectSql[0]

About the error, is it definitely this query that's failing? if so, sounds like either t1.P_NO or t2.P_NO is the wrong type in the db.

lou

 

[Robertio]

I assume the error is occuring on open of IBdsChild?

If so: comment out the where clause and see if it works, is so then as lou says it is probably because t1.P_NO and t2.P_NO are not of the same type. If the error still occurs then I'd guess you have other data aware components on the form and one of these is causing the problem.