We help IT Professionals succeed at work.

Editing dataset using SQL

ferhad
ferhad asked
on
Medium Priority
163 Views
Last Modified: 2010-04-05
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)
Comment
Watch Question

Top Expert 2004

Commented:
instead of
>Query1.Open;

use
Query1.ExecSQL;

your statement seems to be ok

meikl ;-)
Top Expert 2004

Commented:
btw. just don't forget to commit

Commented:
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.

Author

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?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.