So close! Need help with TUpDate please. 3

[delphiman]

I have the identical tabel structure in different folders as follows

C:\h\StdAcc\GL  \\ The default folder or I won't get the dmStdAcc.qryGL [b]Active[/b] Property set to [b]True[/b]

C:\h\StdAcc\[b]1[/b]\GL
C:\h\StdAcc\[b]2[/b]\GL
C:\h\StdAcc\[b]3[/b]\GL

I am able to access the individual folders as follows

var
  qryname1,qryname2, qryname3

begin
  with dmStdAcc.qryGL do
  begin
    if dmStdAcc.tblUsrTfrPNo.value = 1 then
      qryname1 := ''C:\h\StdAcc\[b]1[/b]\GL'';
    if dmStdAcc.tblUsrTfrPNo.value = 2 then
      qryname1 := ''C:\h\StdAcc\[b]2[/b]\GL''';
    qryname2 := 't1.PNo = t2.PNo';
    qryname3 := 't1.GLNo';
    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;

I am able to UpDate in the default folder with the following

procedure TfrmGL.UpDateGL;
var
  qryname1,qryname2, qryname3,qryname4, qryname5,qryname6 : string;
  s : string;
  i : integer;
  sFields: string;
  sUpdate: string;
  sIns : string;
const
  C_UPD = '%s = :%s';
  begin
  with dmStdAcc.updGL do
  begin
    for i := 0 to (dmStdAcc.qryGL.FieldCount - 1) do
    if i = 0 then
    begin
      sFields := dmStdAcc.qryGL.Fields[i].FieldName;
      sUpdate := sUpdate +',' + Format(C_UPD,
         [dmStdAcc.qryGL.Fields[i].FieldName,
          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;
end;

My problem being that this updates the table in the default folder.

What am I missing in order to UpDate the appropriate folder instead of the default folder?

Anyone?

 

[Pikkunero]

I'm not sure but with a quick look it seems that in the UpDateGl procedure you need to change the dmStdAcc.UpdGl to point to the correct table in the correct directory.

Apparently you're using Paradox as your DB so just change the TableName property to point to the right file.

Example: C:\h\StdAcc\1\GL

 

[delphiman]

>you need to change the dmStdAcc.UpdGl to point to the >correct table in the correct directory.

Exactly! .... but how do I do that?

.... however. You have prodded me into trying in the above final code ...

   if dmStdAcc.tblUsrTfrPNo.value = 1 then
      sFields :=''C:\h\StdAcc\1\GL'';
    if dmStdAcc.tblUsrTfrPNo.value = 2 then
      sFields := ''C:\h\StdAcc\2\GL''';

I'll get back to you! Thanks from downunder mate!!

 

[Pikkunero]

Hmm... If it is just a basic TTable component and the Database is Paradox the it should be as simple as

dmStdAcc.UpdGl.Close;
dmStdAcc.UpdGl.TableName := ''C:\h\StdAcc\1\GL\Tablename.Dbf'';
dmStdAcc.UpdGl.Open;

At least i think... havent used TTable component nor Paradox in years...

 

[donvittorio]

When using an SQL update statement you need to specify which table you want to update, in the same way as you have specified which table to get data from when defining your original select statement
e.g.
select * from TABLENAME where...

and

update TABLENAME set .....

where TABLENAME will change to specify the table in the desired directory. I get the impression that you would get a better understanding of this problem with a bit of SQL knowledge, try

http://sqlzoo.net/

for a gentle introduction to SQL

Steve

 

[delphiman]

>Hmm... If it is just a basic TTable component

Negative. One cannot use the 2nd code in this thread on a TTable. It has to be (and is) a TQuery.

>havent used TTable component nor Paradox in years

... must be. Or you have forgotten the above.

>I get the impression that you would get a better >understanding of this problem with a bit of SQL knowledge, >try

http://sqlzoo.net/

for a gentle introduction to SQL

Good on ya mate!

 

[delphiman]

Not winning!

My problem (in the 3rd code window in this thread) remains that I cannot have ..

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

.. because that TQuery (qryGL) defines sFields as being the fields which TQuery (dmStdAcc.qryGL) refers (must refer to) in a default table as per its StringList Editor : or one will never be able to set the Active Property of the TQuery to True. Which would be disasterous for a multitude of reasons.

From his knowledge of SQL perhaps donvittorio can explain where and what SQL code I might use in the 3rd code window in this thread wereby I could use
update TABLENAME set there. And then we are still left with insert into TABLENAME and delete from TABLENAME.

Essentially I am faced with having to be able to control the SQL code in the
SQLStatements in the TUpDate component (in the 3rd code window to this thread) during
Runtime - as I do (in the 2rd code window to this thread) in the StringList Editor of
the TQuery during RunTime?

I don't know how this is done.


Anyone?

 

[svanels]

Terry I understand that you are using various databases with the same structure in different folders?

Use a database component and link all your tables and queries to the database component. If you need to switch, just disconnect the database, change the parameters and reconnect again. Or if you need to work in both folders at the same time, use two databases.

Regards

Steven van Els
SAvanEls@cq-link.sr

 

[donvittorio]

I think Steven's idea is a good one, by far the tidiest way to solve this problem. And in response to your request for an example update SQL statement:

update TABLENAME
set FIELD1 = :VALUE1, FIELD2 = :VALUE2, FIELD3 = :VALUE3
where TABLENAME.FIELD4 = :VALUE4

this statement should be contained in the ModifySQL property of a TUpdateSQL, or in the SQL property of a TQuery, you can then set all of the parameters and execute the statement (n.b. in a TQuery you must use ExecSQL to execute an update or insert statement, rather than setting active to true or using open, because you are not fetching a dataset). Your for loop is adding a FIELD = :VALUE string to sUpdate for each field in dmStdAcc.qryGL, giving the parameter the same name as the field.
Similarly for inserting:

insert into TABLENAME(FIELD1, FIELD2, FIELD3, FIELD4)
values VALUE1, :VALUE2, :VALUE3, :VALUE4)

Steve

 

[delphiman]

svanels
Nice hearing again from you Steven but I don't think that is the way to go for too many reasons to go into here

donvittorio
I reckon this is the solution to explore.

HoweverI have tried that as follows but what I find maddening is that I get an exception "Invalid use of code word. Token: where Line bumber: 3" for no reason that I can see - or imagine. And simply has to be nonsense because that is how the code is generated by the "Generate SQL" button of the TupDateSQL component itself. And it works perfecty as an 'Valid use of code word' in the 2nd code window in this thread.

What suggestions? Anyone?

var
 qryname1,qryname2, qryname3 : string;
 s : string;

begin
  with dmStdAcc.updGL do  
  begin
    if dmStdAcc.tblUsrTfrPNo.value = [b]1[/b] then
      qryname1 := '''c:\h\StdAcc\[b]1[/b]\GL''';
    if dmStdAcc.tblUsrTfrPNo.value = [b]2[/b] then
      qryname1 := '''c:\h\StdAcc\[b]2\[/b]GL''';
    qryname2 := '''GLNo2, GLNo, PNo, Name''';
    qryname3 := '''GLNo2 = :OLD_GLNo2''';

    ModifySQL.Clear;
    s:=format('update %s',[qryname1]);
    ModifySQL.Add (s);
    s:=format('set %s',[qryname2]);
    ModifySQL.Add (s);
     s:=format('[b]where[/b] %s',[qryname3]);
    ModifySQL.Add (s);

    if dmStdAcc.qryGL.UpdatesPending then
      dmStdAcc.qryGL.ApplyUpdates;

  end;
end;

 

[donvittorio]

You don't appear to be building your update statement correctly, from the code you gave you will get the followiong statement:

update 'c:\h\StdAcc\1\GL'
set 'GLNo2, GLNo, PNo, Name'
where 'GLNo2 = :OLD_GLNo2'

I'm hazy on how you should be using the quotes but I don't think you need them around lines 2 or 3. More importantly though you are not assigning values to the fields you are updating. Your statement should be:

update 'c:\h\StdAcc\1\GL'
set GLNo2 = :GLNo2, GLNo = :GLNo, PNo = No, Name = :Name
where GLNo2 = :OLD_GLNo2

See the Delphi help on the ModifySQL property of TUpdateSQL.
Also, you are not applying the update to the database. Maybe you are doing this in a piece of code which you have not posted here, but if not then you will need to use dmStdAcc.updGL.ExecSQL(ukModify) to execute the sql statement.

Steve

 

[svanels]

There is a feature in delphi called quoted string to take the confusion out when you have to much '''''

Regards

Steven van Els
SAvanEls@cq-link.sr

 

[delphiman]

donvittorio You were were quite right about 'set ''GLNo2 =: GLNo2''etc. etc. Silly of me to have missed that!

The way I use the quotes is correct. If one does not do that as below Delphi objects.

However even when I reduce the problem to very basic terms as below I get the exception to use of the word 'Where'. I am beginning to suspect that this is a bug in Delphi 6 Enterprize.

   if dmStdAcc.tblUsrTfrPNo.value = 1 then
       ModifySQL.Add ('update ''c:\h\StdAcc\1\GL''');
   if dmStdAcc.tblUsrTfrPNo.value = 2 then
       ModifySQL.Add ('update ''c:\h\StdAcc\2\GL''');
    ModifySQL.Add ('set ''GLNo2 =: GLNo2, GLNo =: GLNo, PNo = PNo:,  Name =: Name''');
    ModifySQL.Add ('[b]where[/b]  ''GLNo2 = :OLD_GLNo2''');

 

[donvittorio]

Try running your update statement in an SQL window in your database software (I forget what database you are using), that way you will know whether you have got the statement correct.
When specifying parameters the colon should be immediately before the param name, i.e.

GLNo2 = :GLNo2

not

GLNo2 =: GLNo2

Steve

 

[delphiman]

FIXED!!

For those who might follow this thread the following is the required code.
Note that (obviously) InsertSQL and DeleteSQL also needs to be handled.

To avoid errors it is advisable to let the UpdateSQLEditor (in the TUpdateSQL)
generate the code for the default table. One can then copy and paste as below. Obviously one
precedes with "ModifySQL.Add", "InsertSQL.Add" and "DeleteSQL.Add" as required.

procedure TdmStdAcc.UpDateGenAccs;
begin
  with dmStdAcc.updGenAccs do  // upGL being a TupDateSQL component
  begin
    [b]ModifySQL[/b].Clear;
    ModifySQL.Add ('update');
//    ModifySQL.Add ('''c:\h\StdAcc\GenAccs''');
    if dmStdAcc.tblUsrTfrPNo.value = 1 then
      ModifySQL.Add ('''c:\h\StdAcc\1\GenAccs''');
    if dmStdAcc.tblUsrTfrPNo.value = 2 then
      ModifySQL.Add ('''c:\h\StdAcc\2\GenAccs''');
    if dmStdAcc.tblUsrTfrPNo.value = 3 then
      ModifySQL.Add ('''c:\h\StdAcc\3\GenAccs''');
    ModifySQL.Add ('set');
    ModifySQL.Add ('GLAccNo = :GLAccNo, GLAccNo2 = :GLAccNo2, GLAccDate = :GLAccDate,');
    ModifySQL.Add ('Doc = :Doc, FileNo = :FileNo, Vat = :Vat, OppAccName = :OppAccName, OppAccNo = :OppAccNo, OppAccNo2 = :OppAccNo2,');
    ModifySQL.Add ('Amount = :Amount, CBAmount = :CBAmount, Balance = :Balance');
    ModifySQL.Add ('where');
    ModifySQL.Add ('Akv = :OLD_Akv');

    [b]InsertSQL[/b].Clear;
    InsertSQL.Add ('insert into');
    if dmStdAcc.tblUsrTfrPNo.value = 1 then
      InsertSQL.Add ('''c:\h\StdAcc\1\GenAccs''');
    if dmStdAcc.tblUsrTfrPNo.value = 2 then
      InsertSQL.Add ('''c:\h\StdAcc\2\GenAccs''');
    if dmStdAcc.tblUsrTfrPNo.value = 3 then
      InsertSQL.Add ('''c:\h\StdAcc\3\GenAccs''');
    InsertSQL.Add ('(GLAccNo, GLAccNo2, GLAccDate, Doc, FileNo, Vat, OppAccName, OppAccNo, OppAccNo2, Amount, CBAmount, Balance)');
    InsertSQL.Add ('values');
    InsertSQL.Add ('(:GLAccNo, :GLAccNo2, :GLAccDate, :Doc, :FileNo, :Vat, :OppAccName, :OppAccNo,');
    InsertSQL.Add (':OppAccNo2, :Amount, :CBAmount, :Balance)');

    [b]DeleteSQL[/b].Clear;
    DeleteSQL.Add ('delete from');
    if dmStdAcc.tblUsrTfrPNo.value = 1 then
      DeleteSQL.Add('''c:\h\StdAcc\1\GenAccs''');
    if dmStdAcc.tblUsrTfrPNo.value = 2 then
      DeleteSQL.Add ('''c:\h\StdAcc\2\GenAccs''');
    if dmStdAcc.tblUsrTfrPNo.value = 3 then
      DeleteSQL.Add ('''c:\h\StdAcc\3\GenAccs''');
    DeleteSQL.Add ('where');
    DeleteSQL.Add ('Akv = :OLD_Akv');

    if dmStdAcc.qryGenAccs.UpdatesPending then
      dmStdAcc.qryGenAccs.ApplyUpdates;
  end;
end;

 

[delphiman]

Finally ... note that correct punctuation is critical!!