Editing dataset using SQL

I have two fields Field1 and Field2 in my table. I want to update Field1 using Field2 using the following code:

Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('Update debts Set Field1=:PPercentAZM * Field2 Where (Currency="$")and(Calcul=True)');

Query1.ParamByName('PPercentAZM').AsFloat := Table1.FieldByName('FieldName').AsFloat;
Query1.Open;
It gives me an error with message "Error creating cursor handle". I tried to do something via SQL Explorer using the following code:

Update debts Set Field1=Field2 * 4924 Where (Currency="$")and(Calcul=True)

It's not giving error, but doesn't calculate anything.
What i should do in order to change values of all fields in dataset using values of other fields? (Certainly with SQL)
LVL 2
ferhadAsked:
Who is Participating?
 
moduloConnect With a Mentor Commented:
PAQed with points refunded (100)

modulo
Community Support Moderator
0
 
kretzschmarCommented:
instead of
>Query1.Open;

use
Query1.ExecSQL;

your statement seems to be ok

meikl ;-)
0
 
kretzschmarCommented:
btw. just don't forget to commit
0
 
calinutzCommented:
Its not SQl .... it's just Delphi help files... you should read it. If you have an SQL (query) that does not return any cursor then of course you have to use the ExecSQL...
Just like kretzschmar already  wrote. And you solved the problem, I'm sure.(just type Query1.Active and press F1 on the Active property  and you should fine very easily the help that you need)
Cheers.
0
 
ferhadAuthor Commented:
Thank you  for your advance
I have found that Update function doesn't work with BCD fields. In SQLExplorer have tested
the following code:

Update test Set Field3=Field1*5

If the type of Field3 is BCD it doesn't work. If integer it works. The problem is so I can
change type of Field3 into Integer, but not the type of Field1. When type of Field1 is BCD
and the type of Field3 is Integer what should I do in order to calculate Field3 using Field1?
0
All Courses

From novice to tech pro — start learning today.