What is wrong with this SQL.Text ?

[HatForCat]

I am getting an error "error near 'set' : syntax error"
Having torn out the last of my thinning hair, I am asking here...

Code: [Select]

  anID:=dm.sqlEmails.FieldByName('zID').AsString;  // zID is an AutoInc Primary
  if turnON then
    dm.sqlEmails.SQL.Text:='UPDATE sqlEmails SET zSent = 1 WHERE zID = '+anID else
    dm.sqlEmails.SQL.Text:='UPDATE sqlEmails SET zSent = 0 WHERE zID = '+anID;

Thanks

[magikfingerz]

Just to discard it... is sqlEmails a table or an updatable view?

[rvk]

Is this the exact code?
Because it can't work like that.
On the first line dm.SqlEmails is a dataset which should be open to read zId. And in the following lines you try to set the SQL of that exact same dataset.

You can't set the SQL of an open dataset (afaik).

There should be a dm.SqlEmails.Close in between those lines.

[bylaardt]

MySQL? Postgres? Oracle?
Is the Server on Linux?

Why those questions?
Because:
MySQL on Linux has tables and case-sensitive field names, but not on Windows;
";" Must be set in a few DBMS
Maybe BEGIN / COMMIT is missing

My first suggestion is (just a hunch):

Code: Pascal  [Select][+][-]

1.   dm.sqlEmails.SQL.Text:=format('UPDATE sqlEmails SET zSent=%d WHERE zID=%d;',
2.     [Integer(turnON),m.sqlEmails.FieldByName('zID').AsInteger]);

[HatForCat]

MySQL? Postgres? Oracle?
Is the Server on Linux?

Thank you kindly for the code example.

Funny you should ask, I have been asked that kind of stuff before and finally added everything in my Signature, but omitted the Database Server (SQLite3) so will rectify that right after posting this.

I had not thought of the method you provided, but will endeavor to use that approach in the future. Thanks very much.

After posting I went back to hammering around and found this to solve the problem. But as mentioned, your code is a much better approach.

Code: Pascal  [Select][+][-]

1.   SET zSent = '+#39+'0'+#39+' WHERE
2. and
3.   SET zSent = '+#39+'1'+#39+' WHERE
4.  

[kapibara]

I think zSent should be created as integer in the table. Then you can just assign 0 and 1 and skip the apostrofes (#39+'0'+#39). Only strings need an apostrofe in the SQL statement.

[mangakissa]

First you said sZent is an integer and now you said sZent is a string.
What's the structure of table exactly?
Are you sure zID is an string and not an integer?

Code: Pascal  [Select][+][-]

1. procedure MyUpdate;
2. begin
3.    dm.sqlEmails.SQL.Text := 'UPDATE sqlEmails SET zSent = :newvalue WHERE zID = :id';
4.    dm.sqlEmails.parambyname('id').AsInteger := dm.sqlEmails.FieldByName('zID').AsInteger;  // zID is an AutoInc Primary
5.    if turnON then
6.      dm.sqlEmails.parambyname('newvalue').AsInteger := 1
7.   else
8.      dm.sqlEmails.parambyname('newvalue').AsInteger := 0;
9.    dm.sqlEmails.ExecSQL;
10.    //transaction.commitretaining if it's not automatic.
11. end;
12.  

The most important thing is where did you get the value zID. If the dataset is already active, you only have to edit en post the record.

Code: Pascal  [Select][+][-]

1. procedure MyUpdate;
2. begin
3.    dm.sqlEmails.edit;
4.    dm.sqlEmails.FieldByName('zID').AsInteger;  // zID is an AutoInc Primary
5.    if turnON then
6.      dm.sqlEmails.FieldByName('zID').AsInteger := 1
7.   else
8.      dm.sqlEmails.FieldByName('zID').AsInteger := 0;
9.    dm.sqlEmails.Post;
10.    dm.sqlEmails.applyupdates;
11.    //transaction.commitretaining if it's not automatic.
12. end;
13.  

Try to avoid using function format(). The framework is built to use params. Structures like format makes ik harder to create the right syntax of your query.

[mangakissa]

You can't set the SQL of an open dataset (afaik).

Yes you can. Use property UpdateSQL. But not how HatforCat is using it, but just a query.

[rvk]

You can't set the SQL of an open dataset (afaik).

Yes you can. Use property UpdateSQL. But not how HatforCat is using it, but just a query.

Of course I meant the SQL-property HatforCat used.

But now that you brought up UpdateSQL... You CAN'T set that either when the dataset is active !!

Project project1 raised exception class 'EDatabaseError' with message:
Operation cannot be performed on an active dataset

But you're correct that using .Edit and .Post might be a better solution here.
(As far as I can see the operation is done on the same dataset)