SOLVED: problem with count in sqlite

[xaver13]

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][+][-]

1. procedure TFormWallet2.FormActivate(Sender: TObject);
2. begin
3.   FormWallet2.DataSource1.DataSet := Query1;
4.   Query1.Close;
5.   SQLiteConnection1.Close; // Ensure the connection is closed when we start
6.   SQLiteConnection1.Transaction := SQLTransaction1;
7.   SQLiteConnection1.DatabaseName := uWallet1.myHome + '/wallet.db';
8.   SQLiteConnection1.Open;
9.   Query1.Database := SQLiteConnection1;
10.   SQLTransaction1.Database := SQLiteConnection1;
11.   SQLTransaction1.Active := True;
12.   //new added
13.   Query1.SQL.Text := 'Select count(ID) as cnt From wallet';
14.   Query1.Open;
15.   ShowMessage(Query1.Fields[0].AsString);
16.   //recCount := Query1.Fields[0].AsInteger;
17.   //recCount :=Query1.FieldByName('cnt').AsInteger;
18.   //new added end
19.   Query1.Close;
20.   Query1.SQL.Text := 'Select * From wallet';
21.   Query1.Open;

[mangakissa]

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

[xaver13]

Have 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]

This is working:

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

1.   FormWallet2.DataSource1.DataSet := Query1;
2.   Query1.Close;
3.   SQLiteConnection1.Close; // Ensure the connection is closed when we start
4.   SQLiteConnection1.Transaction := SQLTransaction1;
5.   SQLiteConnection1.DatabaseName := uWallet1.myHome + '/wallet.db';
6.   SQLiteConnection1.Open;
7.   Query1.Database := SQLiteConnection1;
8.   SQLTransaction1.Database := SQLiteConnection1;
9.   SQLTransaction1.Active := True;
10.   //new added
11.   Query10.Database := SQLiteConnection1;
12.   Query10.SQL.Text := 'Select count(id) as cnt From wallet';
13.   //Query1.SQL.Text := 'Select max(id) as cnt  From wallet';
14.   Query10.Open;
15.   ShowMessage(Query10.Fields[0].AsString);
16.   //recCount := Query1.Fields[0].AsInteger;
17.   //recCount :=Query1.FieldByName('cnt').AsInteger;
18.   //new added end
19.   Query10.Close;
20.   Query1.SQL.Text := 'Select * From wallet';
21.   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]

That's not a problem.

[MacWomble]

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.