Lazarus

Free Pascal => Beginners => Topic started by: heebiejeebies on July 21, 2021, 11:14:37 am

Title: [Solved!] Loading checkbox state from SQLite file
Post by: heebiejeebies on July 21, 2021, 11:14:37 am

Ok so we all saw this topic coming, after my previous one on saving checkbox state....  :D  Really baffled by this one because I've used this exact code before in another test project (only changing the names of the fields and objects), and it worked perfectly.  UserFileLoad is a TSQL query.  Just as a proof of concept I'm getting the data from the query and putting it in a field, tempdisplay2, but in the real world all the data will be true/false so I assume I can read it as a StrToBool and then set each checkbox state accordingly.

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

1. UserFileConnection.Open;
2. UserFileTransaction.Active:= True;
3. UserFileLoad.SQL.Text:=('SELECT * FROM IssueSpecific WHERE _rowid_=:RowParam');
4.  
5. UserFileLoad.Params.ParamByName('RowParam').Value := TempRecordNum.text;
6.  
7. UserFileLoad.ExecSQL;
8. UserFileTransaction.Commit;
9.  
10. tempdisplay2.Text := UserFileLoad.Fields[1].AsString;  
11.  
12.  

It compiles, then when I click the appropriate button I get:

Project raised exception class 'EListError' with message:
List index (1) out of bounds

 :(

Title: Re: Loading checkbox state from SQLite file
Post by: heebiejeebies on July 21, 2021, 11:03:29 pm

Tried this as well... this seemed to be the most logical other option:

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

1. tempdisplay2.Text := UserFileLoad.fieldvalues['PRValue'];
2.  

But it doesn't like that either.  Apparently there's no field "PRValue", even though there IS!

And since I loaded the entire table with this, it should be in the query result:

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

1. UserFileLoad.SQL.Text:=('SELECT * FROM IssueSpecific WHERE _rowid_=:RowParam');

Title: Re: Loading checkbox state from SQLite file
Post by: paweld on July 22, 2021, 06:50:08 am

for SELECT query you must use Open procedure, not ExecSQL:

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

1. UserFileConnection.Open;
2. UserFileTransaction.Active:= True;
3. UserFileLoad.SQL.Text:=('SELECT * FROM IssueSpecific WHERE _rowid_=:RowParam');
4. UserFileLoad.Params.ParamByName('RowParam').Value := TempRecordNum.text;
5. UserFileLoad.Open; // <------ open query
6. tempdisplay2.Text := UserFileLoad.Fields[1].AsString; // or: UserFileLoad.FieldByName('PRValue').AsString;
7. UserFileLoad.Close;

Title: Re: Loading checkbox state from SQLite file
Post by: dseligo on July 22, 2021, 10:32:35 am

In addition to what paweld said, if there is possibility that your row isn't found, you should check if there is result:

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

1. If not UserFileLoad.Eof then
2.   tempdisplay2.Text := UserFileLoad.Fields[1].AsString // or: UserFileLoad.FieldByName('PRValue').AsString;
3. else
4.   ShowMessage('Row not found.');
5.  

Title: Re: Loading checkbox state from SQLite file
Post by: heebiejeebies on July 22, 2021, 12:44:11 pm

THANK YOU LEGENDS!!  :D

Could not find anything on this anywhere and thought maybe I was dealing with a corrupted database.

I looked back through the old working code from my test project and, erm, yes.... there it was.... "open" instead of ExecSQL.  It's finicky about where you place the statement in the code, too.... Anyway, it works, brilliant!  That'll keep me busy for another few weeks of coding before I post my next n00b question.  :D