[Solved] SQL update statement and CheckBox
lazarus / 16-Databases

----------------------------------------------------------------------
folkeu08 · 2025-01-19 21:31

Hi,
In an SQL update statement, I update the value of a checkbox with this
piece of code:
 Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i +',
Present=' + quotedstr(BoolToStr(Present_CheckBox.Checked)) + 'when the
checkbox is checked, the value of Present is '-1'. I assume this is a
consequence of the BoolToStr conversion.
Is there a method to get 1?
I tried to convert to absolute value (abs) but the types are
incompatible.
Should I really do a test on the status of the chekbox and hard-code 0
or 1 with a variable in the update command ?
Thanks
François

----------------------------------------------------------------------
dseligo · 2025-01-19 22:18

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i +',
Present=' + quotedstr(IntToStr(Ord(Present_CheckBox.Checked))) + '

----------------------------------------------------------------------
dseligo · 2025-01-19 22:19

Or like this if you want '1' for false and '0' for true:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i +',
Present=' + quotedstr(IntToStr(1 - Ord(Present_CheckBox.Checked))) + '

----------------------------------------------------------------------
egsuh · 2025-01-20 04:17

You should not use "quotedStr".  The SQL  you should write :

   update tbl1 set Present=true where .... , not Present = 'true'.

AFAIK integer 0 is regarded as false while any other value is
interpreted as true. So, if your table has a Boolean field, then you
don't have to mind the returned integer value from Lazarus App.

Or,

     + ' Present=' + BoolToStr(Present_CheckBox.Checked, true)

which will return string 'True' or 'False' depending on the checkbox's
status.

----------------------------------------------------------------------
Zvoni · 2025-01-20 06:58

Quote from: egsuh on January 20, 2025, 05:17:49 amYou should not use
"quotedStr".  The SQL  you should write :

   update tbl1 set Present=true where .... , not Present = 'true'.

AFAIK integer 0 is regarded as false while any other value is
interpreted as true. So, if your table has a Boolean field, then you
don't have to mind the returned integer value from Lazarus App.

Or,

     + ' Present=' + BoolToStr(Present_CheckBox.Checked, true)

which will return string 'True' or 'False' depending on the checkbox's
status.
This would be true, if sqlite did have a Boolean-Type, which it
doesn't.

@TS: If you have a "Boolean"-Column in your Database-Table, set its
DataType to integer, Default=0
Then from your code you can use Parameters, with "0" being False,
everything else being true, by assigning the CheckBox-Value directly
Any "displaying" of "True/False" on your Form is then a simple thing

----------------------------------------------------------------------
dseligo · 2025-01-20 09:22

Quote from: egsuh on January 20, 2025, 05:17:49 amYou should not use
"quotedStr".  The SQL  you should write :

Maybe he wants to have 0 and 1 for false and true. If you mentioned
not to use 'QuotedStr', better advice would be to use parameters,
something like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i     +',
Present = :present';  ParamByName('present').AsInteger :=
Ord(Present_CheckBox.Checked);

----------------------------------------------------------------------
Zvoni · 2025-01-20 10:03

or if he showed us the FULL SQL-Statement, we would better equipped to
advise better....

There is another Concatenation preceding the snippet (notice the "+"
at the beginning), as well as following the snippet

----------------------------------------------------------------------
silvercoder70 · 2025-01-20 21:52

Random thought, Use this version ...

function BoolToStr(B: boolean; const TrueS, FalseS: string): string;

and call as

BoolToStr(boolean-value, '1', '0');

----------------------------------------------------------------------
folkeu08 · 2025-01-22 09:10

Hello everyone,
Thank you for your help.
I tried the newest solutions to the oldest and stopped at the first
one that worked.
The proposal of 'dseligo' with this code:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i +',
Present=' + quotedstr(IntToStr(Ord(Present_CheckBox.Checked))) +
I started with the following code but the values ​​were reversed (I
had not read the remark at the beginning of the post). I had made an
adaptation:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i +',
Present=' + quotedstr(IntToStr(0 - Ord(Present_CheckBox.Checked)))
+Thanks
François