SOLVED: problem with count in sqlite
lazarus / 16-Databases

----------------------------------------------------------------------
xaver13 · 2017-11-20 19:05

Hello, I wanted to improve my working code. New lines are between "new
added" and "new added end".
When I execute "Select count(ID) as cnt From wallet" in DB Browser it
works fine.
But when I run this code in Lazarus, I get message on line 14  (open)
about non existing column ... but this column exists!
Please any suggestions what could be wrong?

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 procedure
TFormWallet2.FormActivate(Sender: TObject);begin 
FormWallet2.DataSource1.DataSet := Query1;  Query1.Close; 
SQLiteConnection1.Close; // Ensure the connection is closed when we
start  SQLiteConnection1.Transaction := SQLTransaction1; 
SQLiteConnection1.DatabaseName := uWallet1.myHome + '/wallet.db'; 
SQLiteConnection1.Open;  Query1.Database := SQLiteConnection1; 
SQLTransaction1.Database := SQLiteConnection1;  SQLTransaction1.Active
:= True;  //new added  Query1.SQL.Text := 'Select count(ID) as cnt
From wallet';  Query1.Open;  ShowMessage(Query1.Fields[0].AsString); 
//recCount := Query1.Fields[0].AsInteger;  //recCount
:=Query1.FieldByName('cnt').AsInteger;  //new added end 
Query1.Close;  Query1.SQL.Text := 'Select * From wallet'; 
Query1.Open;

----------------------------------------------------------------------
mangakissa · 2017-11-21 07:57

It works for me. Sometimes it's case sensitive and should the
columnname set to 'id'
Have you tried to use count(*)?

----------------------------------------------------------------------
xaver13 · 2017-11-21 17:55

Quote from: mangakissa on November 21, 2017, 08:57:42 amHave you tried
to use count(*)?
Yes, I have tried count(*), with the same bad result. Maybe should be
something wrong with the table? I will try to export it and import to
another database.

Edited:
I have new database and tables established, but the error remains.

----------------------------------------------------------------------
xaver13 · 2017-11-21 18:56

This is working:
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  
FormWallet2.DataSource1.DataSet := Query1;  Query1.Close; 
SQLiteConnection1.Close; // Ensure the connection is closed when we
start  SQLiteConnection1.Transaction := SQLTransaction1; 
SQLiteConnection1.DatabaseName := uWallet1.myHome + '/wallet.db'; 
SQLiteConnection1.Open;  Query1.Database := SQLiteConnection1; 
SQLTransaction1.Database := SQLiteConnection1;  SQLTransaction1.Active
:= True;  //new added  Query10.Database := SQLiteConnection1; 
Query10.SQL.Text := 'Select count(id) as cnt From wallet'; 
//Query1.SQL.Text := 'Select max(id) as cnt  From wallet'; 
Query10.Open;  ShowMessage(Query10.Fields[0].AsString);  //recCount :=
Query1.Fields[0].AsInteger;  //recCount
:=Query1.FieldByName('cnt').AsInteger;  //new added end 
Query10.Close;  Query1.SQL.Text := 'Select * From wallet'; 
Query1.Open; 
What I did not specified: the fragment of code is from a form, which
uses TDBEdits, and the message was about DataField of some from it,
and when I deleted this field message was changed to any TDBedit used
and remained there.

When I added new Query, problem is solved. I have one piece of
Connection, Transaction, Datasource but I need two Queries in this
form.

----------------------------------------------------------------------
mangakissa · 2017-11-22 07:45

That's not a problem.

----------------------------------------------------------------------
MacWomble · 2017-11-22 08:05

I use allways one special query in my applications for such special
jobs like count, delete or something directly on the database - and
after this, if neccassary, I send a refresh to the main query.