Recent

Author Topic: SOLVED: problem with count in sqlite  (Read 4483 times)

xaver13

  • Full Member
  • ***
  • Posts: 111
SOLVED: problem with count in sqlite
« on: November 20, 2017, 08:05:25 pm »
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;
« Last Edit: November 21, 2017, 07:56:40 pm by xaver13 »
--
Jiri Cvrk

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: problem with count in sqlite
« Reply #1 on: November 21, 2017, 08:57:42 am »
It works for me. Sometimes it's case sensitive and should the columnname set to 'id'
Have you tried to use count(*)?
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

xaver13

  • Full Member
  • ***
  • Posts: 111
Re: problem with count in sqlite
« Reply #2 on: November 21, 2017, 06:55:25 pm »
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.
« Last Edit: November 21, 2017, 07:06:17 pm by xaver13 »
--
Jiri Cvrk

xaver13

  • Full Member
  • ***
  • Posts: 111
Re: problem with count in sqlite
« Reply #3 on: November 21, 2017, 07:56:20 pm »
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.


« Last Edit: November 22, 2017, 07:42:14 am by xaver13 »
--
Jiri Cvrk

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: SOLVED: problem with count in sqlite
« Reply #4 on: November 22, 2017, 08:45:37 am »
That's not a problem.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

MacWomble

  • Jr. Member
  • **
  • Posts: 79
Re: SOLVED: problem with count in sqlite
« Reply #5 on: November 22, 2017, 09:05:49 am »
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.
Mint 19.3 Cinnamon, FPC/ Lazarus Trunk 64Bit

 

TinyPortal © 2005-2018