[Solved!] Loading checkbox state from SQLite file

[heebiejeebies]

Ok so we all saw this topic coming, after my previous one on saving checkbox state....    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

 

[heebiejeebies]

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');

[paweld]

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;

[dseligo]

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.  

[heebiejeebies]

THANK YOU LEGENDS!! 

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.