• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 885
  • Last Modified:

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
0
khaled salem
Asked:
khaled salem
1 Solution
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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