Query Syntax

Hi all:
I'd like to create a Delphi 2010 with MySQL Database, procedure to correct balance value in a table, I've made a breakpoints to trace each step in calculation process. All values are OK. but the update query doesn't effect the value the following is the procedure code.

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

Error-msg.png
khaled salemSoftware DeveloperAsked:
Who is Participating?
 
lwadwellCommented:
What is 'sn'? ... as it used in the UPDATE statements as the key, I would assume it is the unique (PK) identifier for each row ... yet it only seems to be set in the SNN variable once (SNN:=DM.Query1.Fieldbyname('sn').AsInteger;) for the first row.
Also ... how do you know the order the rows will be processed as there is no order by on the SELECT statement?

I would maybe simplify the main loop to be
    DM.Query1.First;
    For i:= 0 to DM.Query1.RecordCount-1 do
    Begin
        X1:=DM.Query1.Fieldbyname('Qty').Asfloat;
        J:=DM.Query1.Fieldbyname('Op_Type').AsInteger;
        SNN:=DM.Query1.Fieldbyname('sn').AsInteger;
        IF i=0 Then
            X5:=X1;
        Else
        Begin
            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
                X5:=X5+X1;
            IF (J=0) or (J=2) Then          // J is the operation type 0,2 debit value this meaning to decrease the Qty
                X5:=X5-X1;
        End;
        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)));
            ParamByName('X2').AsInteger:=snn;
            Execute;
        End;
        Showmessage(inttostr(i)+'   ===> '+Floattostr(X5));
        DM.Query1.Next;
    End;

Open in new window

0
 
mlmccCommented:
Why do you think the update isn't working?

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.