How I can use a SQLQuery to do an update??
lazarus / 16-Databases

----------------------------------------------------------------------
drako86 · 2006-11-15 20:40

Hi, I want to know how I can do an update query using a SQLQuery. I
try to do it like this:

**********************************************************

query:= 'update table_name set field1='value1', field2='value2' where
ID='value';

SQLQuery.SQL.text:= query;

SQLQuery.open;

***********************************************************

But a Lazarus error appears... What can I do?

----------------------------------------------------------------------
Emyr_lydaw · 2006-11-15 23:50

Hi Drako, if you put your query in SQL.text, use SQLQuery.ExecSQL and
not SQLquery.Open (use open for  'select  bla bla bla...')
It works :wink:

Now i'm working on refresh a TDBGrid connected to a Dataset, HELP XD


Solo era cuestion de tiempo descubrir como hacer un update, disfruta.

A otros: Sorry for my bad english, I'll try to improve it XD

----------------------------------------------------------------------
ematutu · 2009-01-19 20:56

Hi, You can try this one

SQLQuery.UpdateSQL.Clear;  //Clear Update SQL Statement if Any
SQLQuery.UpdateSQL.Add('update table_name');                         
                     //
SQLQuery.UpdateSQL.Add('set field1=''value1String'',
field2=value2Integer');   //Create SQL
SQLQuery.UpdateSQL.Add('where ID=valueInteger;')                     
                  //
SQLQuery.Open;
SQLQuery.Edit; //Put Query in Edit(Update) Mode
SQLQuery.Post;
SQLQuery.ApplyUpdates; //Post and Apply Updates
SQLQuery.Close;

----------------------------------------------------------------------
Loesje · 2009-01-20 13:00

It's not always necessary to provide an updatesql. In most cases
(select * from table) sqldb can generate the update-query by itself.
If the Upda]"]>Blockedde is UpWhereKeyOnly the table needs an primary
key. If the Upda]"]>Blockedde is upWhereAll that's not necessary. (But
all fields are added to the where-part of the update-queries)

Also, if you make up your own update-query, you should use
'parameters':

Code: [Select]SQLQuery.UpdateSQL.Text := 'update table_name set
field1=:field1 where id=:old_id';
For each record that is changed, sqldb fills in the right values. The
parameters must have the same name as the field-names, if the 'old_'
prefix is used, the value of the field before the change is used.

Then you can do:

Code: [Select]SQLQuery.Open;
SQLQuery.Edit;
SQLQuery.FieldByName('field1').asstring := 'hello';
SQLQuery.Post;
SQLQuery.Next;
SQLQuery.Edit;
SQLQuery.FieldByName('field1').asstring := 'world';
SQLQuery.Post;
SQLQuery.ApplyUpdates;
SQLQuery.Close;
SQLTransaction.CommitRetaining;