Hi All,

I am having problems updating a table with following SQL:

Code 1:

 with OpdaterKundeSaldo do begin
    if active then Close;
    SQL.Clear;
    SQL.ADD('update "kunder.dat"');
    SQL.ADD('set saldo = saldo - :belob');
    SQL.ADD('where kundenr = :kundenum');
    Unprepare;
    Prepare;
    ParamByName('belob').AsCurrency := Ordre_TableIAltBelb.oldvalue -
Ordre_TableIAltBelb.value;
    ParamByName('kundenum').AsString := Ordre_TableKundenr.value;
    ExecSQL;
   end;

Code 2:

with OpdaterKundeSaldo do begin
    if active then Close;
    SQL.Clear;
    SQL.ADD('update "kunder.dat"');
    SQL.ADD('set saldo = saldo + 500');
    SQL.ADD('where kundenr = :kundenum');
    Prepare;
    ParamByName('kundenum').AsString := Ordre_TableKundenr.value;
    OpdaterKundeSaldo.SQL.Text := StringReplace(OpdaterKundeSaldo.SQL.Text,
'500', currtostr(Ordre_TableIAltBelb.value) ,[rfReplaceAll]);
    ExecSQL;
   end;

Both codes compiles ok, but the table "kunder.dat" is not updated??

What am i doing wrong?

DBISAM, 4.26 B3. D2007.

Thanks in advance,

Best regards,
Hüseyin A.

"Hüseyin Aliz" <haliz@consit.dk> wrote in message
news:E12B939F-B5C7-4A88-9AD5-7991EC71CA97@news.elevatesoft.com...
> Hi All,
>
>
> What am i doing wrong?
>

Have you verified that this

'where kundenr = :kundenum'

produces a match? What happens if you eliminate the parameter and use a hard
coded literal instead?

Also, there is no need to do explicit prepare and unprepare unless you are
going to re-use the same SQL with different parameters.

Robert

Hi Robert,

I've tried following:

update "kunder.dat"
set saldo = saldo - :belob
where kundenr = '99999999'

And following:

update "kunder.dat"
set saldo = saldo + 500
where kundenr = '99999999'

Same result.

Set RequestLive to true. Tried the last one from DBsys, also with no
success.

Saldo is a currency field and kundenr a string field.

Regards,
Hüseyin

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> skrev i en meddelelse
news:D47C6A71-167A-4302-A51C-33D79057BF0D@news.elevatesoft.com...
>
> "Hüseyin Aliz" <haliz@consit.dk> wrote in message
> news:E12B939F-B5C7-4A88-9AD5-7991EC71CA97@news.elevatesoft.com...
>> Hi All,
>>
>>
>> What am i doing wrong?
>>
>
> Have you verified that this
>
> 'where kundenr = :kundenum'
>
> produces a match? What happens if you eliminate the parameter and use a
> hard coded literal instead?
>
> Also, there is no need to do explicit prepare and unprepare unless you are
> going to re-use the same SQL with different parameters.
>
> Robert
>

Hüseyin

As Robert asked are you sure you're getting a match? Try

SELECT * FROM kunder where kundenr = '99999999'

and see it it returns anything


Roy Lambert [Team Elevate]

"Hüseyin Aliz" <haliz@consit.dk> wrote in message
news:72110E27-B466-491C-A68D-3EE66CB495C2@news.elevatesoft.com...
> Hi Robert,
>
> I've tried following:
>
> update "kunder.dat"
> set saldo = saldo - :belob
> where kundenr = '99999999'
>
> And following:
>
> update "kunder.dat"
> set saldo = saldo + 500
> where kundenr = '99999999'
>
> Same result.
>

Look, obviously '99999999' is not matching to any kundenr. There are
trailing spaces that are part of the field, something like that. Try

where rtrim(kundenr) = '99999999'

Robert

Hi Roy,

Returning 1 record from dbsys as it should.

Regards,
Hüseyin

"Roy Lambert" <roy.lambert@skynet.co.uk> skrev i en meddelelse
news:3D53C3B0-07F7-48A8-9B50-DDF8CCA933D7@news.elevatesoft.com...
> Hüseyin
>
> As Robert asked are you sure you're getting a match? Try
>
> SELECT * FROM kunder where kundenr = '99999999'
>
> and see it it returns anything
>
>
> Roy Lambert [Team Elevate]
>

Hi Robert,

Just tried to run following SQL:

update "kunder.dat"
set saldo = saldo + 500
where rtrim(kundenr) = '99999999'

Same result.

Regards,
Hüseyin

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> skrev i en meddelelse
news:C8FA6B33-6B66-40E8-B05B-57B7407AE3A6@news.elevatesoft.com...
>
> "Hüseyin Aliz" <haliz@consit.dk> wrote in message
> news:72110E27-B466-491C-A68D-3EE66CB495C2@news.elevatesoft.com...
>> Hi Robert,
>>
>> I've tried following:
>>
>> update "kunder.dat"
>> set saldo = saldo - :belob
>> where kundenr = '99999999'
>>
>> And following:
>>
>> update "kunder.dat"
>> set saldo = saldo + 500
>> where kundenr = '99999999'
>>
>> Same result.
>>
>
> Look, obviously '99999999' is not matching to any kundenr. There are
> trailing spaces that are part of the field, something like that. Try
>
> where rtrim(kundenr) = '99999999'
>
> Robert
>
>
>
>

"Hüseyin Aliz" <haliz@consit.dk> wrote in message
news:E6A88B27-8F7B-448B-9A34-22AB2414FDCD@news.elevatesoft.com...
> Hi Robert,
>
> Just tried to run following SQL:
>
> update "kunder.dat"
> set saldo = saldo + 500
> where rtrim(kundenr) = '99999999'
>
> Same result.
>

Is saldo null? adding anything to null gets you a null.

Robert
> Regards,
> Hüseyin
>
> "Robert" <ngsemail2005withoutthis@yahoo.com.ar> skrev i en meddelelse
> news:C8FA6B33-6B66-40E8-B05B-57B7407AE3A6@news.elevatesoft.com...
>>
>> "Hüseyin Aliz" <haliz@consit.dk> wrote in message
>> news:72110E27-B466-491C-A68D-3EE66CB495C2@news.elevatesoft.com...
>>> Hi Robert,
>>>
>>> I've tried following:
>>>
>>> update "kunder.dat"
>>> set saldo = saldo - :belob
>>> where kundenr = '99999999'
>>>
>>> And following:
>>>
>>> update "kunder.dat"
>>> set saldo = saldo + 500
>>> where kundenr = '99999999'
>>>
>>> Same result.
>>>
>>
>> Look, obviously '99999999' is not matching to any kundenr. There are
>> trailing spaces that are part of the field, something like that. Try
>>
>> where rtrim(kundenr) = '99999999'
>>
>> Robert
>>
>>
>>
>>
>
>

Hi Robert,

You got a point there
It was a problem adding to a null field, i've never used the saldo field
before, so the values were null.
Thanks again for the help.

Regards,
Hüseyin

>>
>
> Is saldo null? adding anything to null gets you a null.
>

"Hüseyin Aliz" <haliz@consit.dk> wrote in message
news:3C5467E1-8894-476B-A213-06C676694D9C@news.elevatesoft.com...
> Hi Robert,
>
> You got a point there
> It was a problem adding to a null field, i've never used the saldo field
> before, so the values were null.

Unless there is a specific reason not to, all numeric fields should have a
default of zero. That prevents this kind of problem.

Robert