How can one control SQLText in UpdateTQuery during Runtime? 3

[delphiman]

Thanks to whosrdaddy thread102-893361 I am able access duplicate tables from different folders as
per the following code.

begin
  qryname1 := '''C:\h\StdAcc\6\GL'' t1, ''C:\h\StdAcc\UsrTfr'' t2';
  qryname2 := 't1.PNo = t2.PNo';
  qryname3 := 't1.GLNo';    
  with dmStdAcc.qryGL do
  begin
    SQL.Clear;
    SQL.Add('SELECT *');
    s:=format('FROM %s',[qryname1]);
    SQL.Add (s);
    s:=format('WHERE %s',[qryname2]);
    SQL.Add (s);
    s:=format('ORDER BY %s',[qryname3]);
    SQL.Add (s);
    Active := True;
end;

But I obviously also need to be able to control the SQLText in the TUpDateSQL component.

How can I do that? Anyone?

Thanks in advance.

 

[hilfy]

Use the DeleteSQL, InsertSQL, and ModifySQL properties in the same way that you would use the SQL property of the TQuery - you'll have to know the field names, though. You could probably do something like this:

const
C_UPD := '%s = :%s'

sFields := '''
sUpdate := '';
sIns    := '';
for i := 0 to (MyQuery.FieldCount - 1) do
  if i = 0 then
  begin
    sFields := MyQuery.Fields[i].FieldName;
    sUpdate := Format(C_UPD, [MyQuery.Fields[i].FieldName, 
                              MyQuery.Fields[i].FieldName]);
    sIns := ':' + MyQuery.Fields[i].FieldName;
  end
  else
  begin
    sFields := sFields + ', ' + MyQuery.Fields[i].FieldName;
    sUpdate := sUpdate +',' + Format(C_UPD, 
                              [MyQuery.Fields[i].FieldName, 
                              MyQuery.Fields[i].FieldName]);
    sIns := sIns + ', :' + MyQuery.Fields[i].FieldName;
  end;

You then use sFields and sIns to create your InsertSQL and sUpdate to create your UpdateSQL.

-Dell

 

[delphiman]

Huston we have a problem!

It would appear that your code is at least very close to what I need but
I think I need more than "something like" to get the code to fly.


Having hit some immediate exceptions (which I won't go into) I nevertheless managed
to developed on it to get as far as ..

procedure TfrmGL.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
var
   qryname1,qryname2, qryname3,qryname4, qryname5,qryname6 : string;
    s : string;
    i : real;

const
  C_UPD = '%s = :%s';
  sFields = '';
  sUpdate = '';
  sIns    = '';

begin
  for i := to (MyQuery.FieldCount - 1) do
  begin
    sFields := MyQuery.Fields[i].FieldName;
    sUpdate := Format(C_UPD, [MyQuery.Fields[i].FieldName,
                              MyQuery.Fields[i].FieldName]);
    sIns := ':' + MyQuery.Fields[i].FieldName;
  end

  etc. etc.

end;

.. but immediately after to in for i to I get exception
For loop control variable must have oridinal type.

What suggestions? Anyone.

Thanks in advance.

 

[whosrdaddy]

...
 for i :=[b]0[/b] to (MyQuery.FieldCount - 1) do
...

--------------------------------------
What You See Is What You Get

 

[whosrdaddy]

and i must be declared as integer not real

--------------------------------------
What You See Is What You Get

 

[delphiman]

Nyet!

begin
  for i := 0 to (dmStdAcc.qryGL.FieldCount - 1) do
  if i = 0 then
  begin
    sFields :=

... get exception Left side can not be assigned to

 

[donvittorio]

The error is referring to the line 'sFields := ...'
You have declared sFields as a constant, if you want to vary its value at run time it must be declared as a variable. The same goes for sUpdate and sIns.

i.e.

procedure TfrmGL.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
var
  qryname1,qryname2, qryname3,qryname4,qryname5,qryname6: string;
  s: string;
  i: real;
  sFields: string;
  sUpdate: string;
  sIns: string ;
const
  C_UPD = '%s = :%s';
.
.
.
.

 

[delphiman]

Many thanks for your help folks!

Code 1 now compiles but remains as useful as a wheelbarrow in a rowing boat
whilst

1.
I get exception No argument for '%s" during Runtime.

2.
My tiny mind has still not grasped exactly how the fields get to be declared/updated etc..

I imagine I need something as in Code 2 - but that won't fly. :-(

A reference to my thread102-893361 will put my problem in proper perspective.

Code 1

procedure TfrmGL.FormCloseQuery(Sender: TObject; var CanClose: Boolean);
var
  qryname1,qryname2, qryname3,qryname4, qryname5,qryname6 : string;
  s : string;
  i : integer;
  sFields: string;
  sUpdate: string;
  sIns : string;
const
  C_UPD = '%s = :%s';

begin
  for i := 0 to (dmStdAcc.qryGL.FieldCount - 1) do
  if i = 0 then
  begin
    sFields := dmStdAcc.qryGL.Fields[i].FieldName;
    sUpdate := Format(C_UPD, [dmStdAcc.qryGL.Fields[i].FieldName]);
    sIns := ':' + dmStdAcc.qryGL.Fields[i].FieldName;
  end
  else
  begin
    sFields := sFields + ', ' + dmStdAcc.qryGL.Fields[i].FieldName;
    sUpdate := sUpdate +',' + Format(C_UPD,
    [dmStdAcc.qryGL.Fields[i].FieldName,
    dmStdAcc.qryGL.Fields[i].FieldName]);
    sIns := sIns + ', :' + dmStdAcc.qryGL.Fields[i].FieldName;
  end;
end;


Code 2

const
  C_UPD = '%s = :%s';

begin
  if dmStdAcc.tblUsrTfrPNo.value = 1 then
    qryname1 := '''C:\h\StdAcc\1\GL'' t1, ''C:\h\StdAcc\UsrTfr'' t2';
  if dmStdAcc.tblUsrTfrPNo.value = 2 then
    qryname1 := '''C:\h\StdAcc\2\GL'' t1, ''C:\h\StdAcc\UsrTfr'' t2';
  qryname2 := 't1.PNo = t2.PNo';
  qryname3 := 't1.GLNo';

  with dmStdAcc.[b]UPDATE[/b]GL do
  begin
    SQL.Clear;
    SQL.Add ('update qryname1');
    SQL.Add ('set');
    SQL.Add ('GLNo2 = :GLNo2,');

    for i := 0 to (dmStdAcc.qryGL.FieldCount - 1) do
    if i = 0 then
    begin
      sFields := dmStdAcc.qryGL.Fields[i].FieldName;
      Etc. etc.

 

[donvittorio]

The error "No argument for '%s" will be occurring when you try to set sUpdate when i = 0. For each of instance of %s in your format string there must be a corresponding value in the array of arguments.
Your format string C_UPD has 2 instances of %s but you only have one entry in your argument array

sUpdate := Format(C_UPD, [dmStdAcc.qryGL.Fields[i].FieldName]);

When i > 0 you correctly specify two arguments, i.e.

sUpdate := sUpdate +',' + Format(C_UPD,
    [dmStdAcc.qryGL.Fields[i].FieldName,
    dmStdAcc.qryGL.Fields[i].FieldName]);

Steve

 

[delphiman]

Eureka!

Thanks to you guys and this great Website my problem is almost solved! BUT I still need to resolve how to control which table will be UpDated. The identical table existing in several folders.

In case I appear to be insane I have several different folders (unique to each client) with an identical table structure in each folder. I do this to prevent one table becoming excessively large and to limit the size of the backups required for each client's records. This is successfully controlled as follows ...

var
queryname : string

begin
  if dmStdAcc.tblUsrTfrPNo.value = [b]1[/b] then
    [b]qryname[/b] := 'C:\h\StdAcc\[b]1[/b]';
  if dmStdAcc.tblUsrTfrPNo.value = [b]2[/b] then
    [b]qryname[/b] := 'C:\h\StdAcc\[b]2[/b]';
  with dmStdAcc.qryGL do
  begin
    SQL.Clear;
    SQL.Add('SELECT *');
    s:=format('FROM %s',[[b]qryname[/b]]);
    SQL.Add (s);
    Active := True;
  end;
end;

This code will only update in the default folder - being C:\h\StdAcc\GL.

 sFields := dmStdAcc.qryGL.Fields[i].FieldName;

I need to be able to control this on the bases that ...

  if dmStdAcc.tblUsrTfrPNo.value = 1 then
    sFields := C:\h\StdAcc\[b]1[/b]\GL ; [b]\\??? [/b]
  if dmStdAcc.tblUsrTfrPNo.value = 2 then
    sFields := C:\h\StdAcc\[b]2[/b]\GL ; [b]\\??? [/b]

.... which won't work.

Anyone?

Thanks in advance.

 

[delphiman]

Anyone?

 

[delphiman]

For anyone folowing this thread visit my thread102-902528 for the solution.