Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 743
  • Last Modified:

piece of code doesn't work

Hi all:
I've write a procedure Delphi2010 & Mysql Database to correct the balance field according to the value of qty and the value of operation type. as show bellow:

Procedure MySQLCorrect_Balance(Code:String;StoreID:Integer);
Var
SNN, I, J,K:integer;
x1,x2,x3,x4,X5:Real;
S,S2,S3,S4,A,B:STRING;
R,F,W:REAL;
Begin

x1:=0; x2:=0; x3:=0; x4:=0; x5:=0;

        WITH DM.QUERY1 DO
        BEGIN
        CLOSE;
        SQL.CLEAR;
        SQL.ADD('SELECT sn, Qty,Op_Type,Balance FROM Transaction1 WHERE code= :X1 and StoreID= :X2');
        ParamByName('X1').value:=code;
        ParamByName('X2').AsInteger:=StoreID;
        OPEN;
        END;

        DM.Query1.First;
        For i:= 0 to DM.Query1.RecordCount-1 do
        Begin

        IF i=0 Then
        Begin
                                X1:=DM.Query1.Fieldbyname('Qty').Asfloat;
                                J:=DM.Query1.Fieldbyname('Op_Type').AsInteger;
                                SNN:=DM.Query1.Fieldbyname('sn').AsInteger;

                              With DM.Query3 Do
                              Begin
                                   Close;
                                   SQL.Clear;
                                   SQL.Add('Update Transaction1 Set balance= :X1 where Sn= :X2');
                                   ParamByName('X1').AsFloat:=StrtoFloat(Formatcurr('0.000',x1-x2));
                                   ParamByName('X2').AsInteger:=snn;
                                   Execute;
                              End;
                              X5:=X1;

        End
        Else
        Begin
        X1:=DM.Query1.Fieldbyname('Qty').Asfloat;
        J:=DM.Query1.Fieldbyname('Op_Type').AsInteger;
        IF(J=1) or (J=3)or (J= 4) Then                        // J is the operation type 1,3,4 credit value this meaning to increase the Qty
           Begin

                              With DM.Query3 Do
                              Begin
                                   Close;
                                   SQL.Clear;
                                   SQL.Add('Update Transaction1 Set balance= :X1 where Sn= :X2');
                                   ParamByName('X1').AsFloat:=StrtoFloat(Formatcurr('0.000',(X5+X1)));
                                   ParamByName('X2').AsInteger:=snn;
                                   Execute;
                              End;
           X5:=X5+X1;
           End;

        IF (J=0) or (J=2) Then         // J is the operation type 0,2 debit value this meaning to decrease the Qty
           Begin

                              With DM.Query3 Do
                              Begin
                                   Close;
                                   SQL.Clear;
                                   SQL.Add('Update Transaction1 Set balance= :X1 where Sn= :X2');
                                   ParamByName('X1').AsFloat:=StrtoFloat(Formatcurr('0.000',(X5-X1)));
                                   ParamByName('X2').AsInteger:=snn;
                                   Execute;
                              End;
           X5:=X5-X1;
           End;

        End;
        Showmessage(inttostr(i)+'   ===> '+Floattostr(X5));
        DM.Query1.Next;
//        ShowMessage(FloatToStr(X5));

        End;


 End;
/////////////////////////////////////////////////////////////////////////////////////////////

Open in new window


i'd trace the values inside the procedure all values are OK, but the balance value doesn't updated with correct value.
0
khaled salem
Asked:
khaled salem
  • 2
1 Solution
 
Ephraim WangoyaCommented:
First hint is, dont use a for loop to traverse your query and call Next at the same time, infact dont use a for loop at all when traversing the query.

Remove all the unneccessary 'WITH' statements

Here is a cleaned up version
Procedure MySQLCorrect_Balance(Code:String;StoreID:Integer);
Var
  SNN, I, J,K:integer;
  x1,x2,x3,x4,X5:Real;
  S,S2,S3,S4,A,B:STRING;
  R,F,W:REAL;
Begin

  x1:=0; x2:=0; x3:=0; x4:=0; x5:=0;

  DM.QUERY1.CLOSE;
  DM.QUERY1.SQL.CLEAR;
  DM.QUERY1.SQL.ADD('SELECT sn, Qty,Op_Type,Balance FROM Transaction1 WHERE code= :X1 and StoreID= :X2');
  DM.QUERY1.ParamByName('X1').value:=code;
  DM.QUERY1.ParamByName('X2').AsInteger:=StoreID;
  DM.QUERY1.OPEN;

  //set up this query since the statements are always the same
  DM.Query3.Close;
  DM.Query3.SQL.Clear;
  DM.Query3.SQL.Add('Update Transaction1 Set balance= :X1 where Sn= :X2');

  //DM.Query1.First; no need for this
  I := 0;
  DM.Query1.DisableControls;
  while not DM.Query1.eof do
  Begin
    Inc(I);
    IF i=0 Then
    Begin
      X1:=DM.Query1.Fieldbyname('Qty').Asfloat;
      J:=DM.Query1.Fieldbyname('Op_Type').AsInteger;
      SNN:=DM.Query1.Fieldbyname('sn').AsInteger;

      DM.Query3.ParamByName('X1').AsFloat:=StrtoFloat(Formatcurr('0.000',x1-x2));
      DM.Query3.ParamByName('X2').AsInteger:=snn;
      DM.Query3.Execute;

      X5:=X1;

    End
    Else
    Begin
      X1:=DM.Query1.Fieldbyname('Qty').Asfloat;
      J:=DM.Query1.Fieldbyname('Op_Type').AsInteger;
      case J of
        1, 3, 4:                        // J is the operation type 1,3,4 credit value this meaning to increase the Qty
        Begin
          DM.Query3.ParamByName('X1').AsFloat:=StrtoFloat(Formatcurr('0.000',(X5+X1)));
          DM.Query3.ParamByName('X2').AsInteger:=snn;
          DM.Query3.Execute;
          X5:=X5+X1;
        End;
        0, 2:         // J is the operation type 0,2 debit value this meaning to decrease the Qty
        Begin
          DM.Query3.ParamByName('X1').AsFloat:=StrtoFloat(Formatcurr('0.000',(X5-X1)));
          DM.Query3.ParamByName('X2').AsInteger:=snn;
          DM.Query3.Execute;
          X5:=X5-X1;
       End;
      end;
    End;
    Showmessage(inttostr(i)+'   ===> '+Floattostr(X5));
    DM.Query1.Next;
//        ShowMessage(FloatToStr(X5));

    End;


 End;
/////////////////////////////////////////////////////////////////////////////////////////////

Open in new window

0
 
khaled salemSoftware DeveloperAuthor Commented:
I've used your code but the result is the same. the balance field doesn't effected
0
 
mlmccCommented:
Why do you think the update isn't working?

mlmcc
0
 
khaled salemSoftware DeveloperAuthor Commented:
I am confident your answer is correct, but give me no result. therefore, i am asking by an other way. the new answer is working perfect..
Thanks for all
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now