[Solved] Which component for update?

[jeanchristophe]

Hello,

I would like to make the following update on my table:

Code: [Select]

update objekt set object='Frimærke' where object= 'Frimµrke';  Right now I have:
TSQLite3connection, SQLQuery and SQLTransaction.
Obviously the SQLQuery require a "select".
I try to use SQLscript, but I an't make it works. Probably because I can't use it!

Can you please tell me how to do so?

Best Regards
Jean-Christophe

[BigChimp]

Obviously the SQLQuery require a "select".

That is what one would think looking at the "query" in the name.... but it isn't true.

See e.g. here:
http://wiki.lazarus.freepascal.org/Working_With_TSQLQuery#Insert_query_example

Edit: note: you can also use the Connection's ExecuteDirect method...

As an aside, I think perhaps the wiki page I linked to could do with some more updates based on the Dutch version, which is a bit more comprehensive in places..

[ludob]

Obviously the SQLQuery require a "select".

A query is any SQL command.
 
SQLQuery serves a dual purpose:
- It can act as a Dataset when the query is returning a result set such as SELECT, SHOW, etc. The query is executed with TSQLQuery.Open.
- It can just execute a query that doesn't return a result set such as UPDATE, DELETE, etc. The query is executed with TSQLQuery.ExecSQL. The number of affected rows is returned in SQLQuery2.RowsAffected. 

[jeanchristophe]

Thank you for your interest BigChimp

When I try to write my sql line, I got this:

Code: [Select]

SQLQuery1: Cannot open a non-select statement
I don't know about the Connection's ExecuteDirect method, but I am searching...

Best Regards
Jean-Christophe

[Arbee]

My guess is you try to execute the query by doing an "open".

That is for SELECT queries only.   Use an "ExecSQL" instead, or - indeed - an "ExecuteDirect".

[jeanchristophe]

Hello Arbee,

Here are the few lines of code:

Code: [Select]

procedure TForm1.Button1Click(Sender: TObject);
begin
  SQLQuery1.SQL.Text:= 'UPDATE objekt SET Object="Frimærke" WHERE Object="Frimµrke";';
  SQLQuery1.Active:= True;
  SQLQuery1.ExecSQL;
  SQLTransaction1.Commit;
Is it wrong?

Best Regards
Jean-Christophe

[ludob]

SQLQuery1.Active:= True is the same as SQLQuery1.Open.  Don't do that if you don't have a result set.

[jeanchristophe]

OK thank you for your answer Ludob

I was a little bit curious because the word "object" was highlighted where I write the SQL statement, so I went to SQLitte manager from mozilla insted.
I try the samme command several times, without succes.
I exported the database and imported it into a new one (to prevent damage)
Then I changed the name of the colon from "Object" to "Objet"
Then I try the exactly samme sentence again (since this morning, I knew it  )
And it's works!

I don't really know, but obviously SQLite3 doesn't like the name "Object"!

Thak you everybody for your help and see you soon here!