[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

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)
0
ferhad
Asked:
ferhad
1 Solution
 
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
 
moduloCommented:
PAQed with points refunded (100)

modulo
Community Support Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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