[Solved] Saving checkbox state into SQLite file

[heebiejeebies]

Hi everyone!

I know the basics about working with SQLite files and I've been successful in saving and retrieving the contents of various text fields into the database. However, I'm now working on a part of my programme that involves many different screens with check boxes on them, the state of which needs to be saved into one big table in an SQL file.  I thought this would be pretty much the same deal as saving the contents of a text file to a database, but it's causing me headaches.

Firstly, from what I understand, SQLite doesn't accept boolean types (what the hell....?)  but I've set up the fields just as text, into which I will save true or false.  I can actually set up a field as a 'BOOLEAN' type but it doesn't seem to behave like one.  Anyway....

UserFileSave is a TSQLQuery.  I've tried various methods of getting the state of all the checkboxes on the current page and saving them.  First I tried a 'save' button that collected all of them in one go.  Now I've tried putting this code in the MouseUp for each check box:

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

1.   if PRCheckBox.Checked=true then UserFileSave.Params.ParamByName('PRParam').AsString := 'true';
2.   if PRCheckBox.Checked=false then UserFileSave.Params.ParamByName('PRParam').AsString := 'false';          
3.  

PRParam is a parameter that I've defined in the UserFileSave, using the object inspector. When it comes to save the file, the save code will match up each parameter with the correct field in the database.

It compiles, but when I click the checkbox, Lazarus blows up and says "Project raised exception class 'EDatabaseError' with message: 'Parameter PRParam not found". It's THERE in the object inspector, poopyhead!  What's going on? 

[heebiejeebies]

I've figured out a way to get it to work - look at me go! 

Anyway, basically: defining params in the Object Inspector doesn't work (or if it does, I'll be darned if I know how.) You need to define them first in code, then you can assign values to them.  The only way I know of to define them in code is to reference them in the SQL property of a Query object.  So my code is now located in the save button, and it reads:

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

1.    UserFileConnection.Open;
2.   UserFileTransaction.Active:= True;
3. UserFileSave.SQL.Text:= ('UPDATE `IssueSpecific` SET `PRValue`=:PRParam WHERE _rowid_=:RowParam');
4. UserFileSave.Params.ParamByName('RowParam').Value := TempRecordNum.text;
5.    if PRCheckBox.Checked=true then UserFileSave.Params.ParamByName('PRParam').Value := 'true';
6.   if PRCheckBox.Checked=false then UserFileSave.Params.ParamByName('PRParam').Value := 'false';
7. UserFileSave.ExecSQL;
8. UserFileTransaction.Commit;              
9.  

Hey presto, it works!  But if you try to look at the checkboxes before referencing PRParam in the UserFileSave.SQL.Text then it doesn't know what you're talking about and explodes.  So best to get things in the right order.

[jamie]

or

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

1. UserFileSave.Params.ParamByName('PRParam').Value := BoolTostr(PRCheckBox.Checked,True);
2.  

[heebiejeebies]

Legend, thank you 

[heebiejeebies]

OK so as usually happens with me, I solve one thing, then something else comes up.  It all worked fine and dandy while I was only looking at one check box.  Now that I've added the code to look at the whole screenful of them, I get an SQL syntax error.  Again, it compiles but blows up when you press the save button. 

This produces an unspecified syntax error:

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

1. UserFileSave.SQL.Text:= ('UPDATE `IssueSpecific` SET `PRValue`=:PRParam WHERE _rowid_=:RowParam,'+
2. 'UPDATE `IssueSpecific` SET `ESValue`=:ESParam WHERE _rowid_=:RowParam,'+
3. 'UPDATE `IssueSpecific` SET `AbValue`=:AbParam WHERE _rowid_=:RowParam,'+
4. 'UPDATE `IssueSpecific` SET `WValue`=:WParam WHERE _rowid_=:RowParam;');
5.  

Replacing all the commas at the end of the SQL statements with semicolons produces the error "column index out of range", which sounds like a step in the right direction at least, but doesn't help me. Any thoughts?

[dseligo]

Separate them in individual 'update' statements and set parameters for each separately.

[dseligo]

Or, if this is all one row you can do it like this:

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

1. UserFileSave.SQL.Text:=
2.   'UPDATE `IssueSpecific`
3.  SET `PRValue`=:PRParam, `ESValue`=:ESParam, `AbValue`=:AbParam, `WValue`=:WParam
4.  WHERE _rowid_=:RowParam;';

[heebiejeebies]

Yep, all one row at a time - that's perfect, much simpler too!  Thank you!