Get the COUNT(*) value back to Lazarus? lazarus / 16-Databases ---------------------------------------------------------------------- HatForCat · 2017-04-28 21:55 Hi, I have been trying to get this to work and all the examples I have found return a value of 1. 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 begin  myDB.SQL.Text:='SELECT COUNT(*) FROM People;';    // Has 200 records  myDB.ExecSQL;  Label1.Caption:=IntToStr(myDB.Fields[0].AsInteger);         // returns 1  Label1.Caption:=IntToStr(myDB.FieldValues[0].AsInteger);   // returns 1  Label1.Caption:=myDB.Fields[0].AsString;                           // returns 1  // if I do  myDB.SQL.Text:='SELECT COUNT(*) AS Ctr FROM People;';    // Has 200 records  myDB.ExecSQL;  Label1.Caption:=myDB.FieldByName('Ctr').AsString; // "No such Field name as Ctr"end;  What am I missing here? Thanks ---------------------------------------------------------------------- mirce.vladimirov · 2017-04-28 22:16 You should use 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 myDB.ExecSQL;for queries that do not return result, such as INSERT query, DELETE query, UPDATE, USE, ALTER.... When yo want to run a query which returns result, like you do with SELECT statement in your example then : 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 myDB.Open; That's all EDIT: forgot to say, always use aliasing of result-fields, always use 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 SELECT count(*) AS myfieldname FROM mytable This way you can : 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 Label1.Caption:=myDB.FieldByName('myfieldname').Asstring; ---------------------------------------------------------------------- HatForCat · 2017-04-29 22:41 Thanks, mirce.vladimiro, but, none of that makes any difference. "Open" or "ExecSQL" still return zero. Line 8 of my code above is using in Aliasing and it still returns "Field Not Fond: myfieldname" ---------------------------------------------------------------------- Abelisto · 2017-04-29 23:35 Everything works as expected: 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 TForm1.Button1Click(Sender: TObject);begin    try        with SQLite3Connection1 do        begin            DatabaseName := '/tmp/test.db';            Connected := True;            ExecuteDirect('create table People as select * from (values(1),(2),(3))');        end;         with SQLQuery1 do        begin            Close;            SQL.Text := 'select count(*) from People';            Open;            ShowMessage(Fields[0].AsString); // Shows "3"            Close;            SQL.Text := 'select count(*) as cnt from People';            Open;            ShowMessage(FieldByName('cnt').AsString); // Shows "3" again          Close;        end;    finally        SQLite3Connection1.Connected := False;        DeleteFile('/tmp/test.db');    end;end; Try to find the difference. ---------------------------------------------------------------------- miab3 · 2017-04-30 10:22 Or with ZEOS 7.2 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 TForm1.Button1Click(Sender: TObject);begin    try        with ZConnection1 do        begin            Protocol:='sqlite-3';          Database := 'c:/tmp/test.db';            Connected := True;          ExecuteDirect('create table People as select * from (values(1),(2),(3))');        end;         with ZQuery1 do        begin            Connection:=ZConnection1;            Close;          SQL.Text := 'select count(*) from People';            Open;          ShowMessage(Fields[0].AsString); // Shows "3"            Close;          SQL.Text := 'select count(*) as cnt from People';            Open;            ShowMessage(FieldByName('cnt').AsString); // Shows "3" again            Close;        end;    finally        ZConnection1.Connected := False;        DeleteFile('c:/tmp/test.db');    end;end; Michal ---------------------------------------------------------------------- HatForCat · 2017-04-30 18:01 Quote from: Abelisto on April 30, 2017, 01:35:25 amEverything works as expected: Thanks for taking the time, but I get a Syntax error: near "values" I am quite new to SQL, so will do some searching to see if I can get that working first. Am I using the correct components? All with default values. 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   TForm1 = class(TForm)    Button1: TButton;    DataSource1: TDataSource;    SQLite3Connection1: TSQLite3Connection;    SQLQuery1: TSQLQuery;    SQLTransaction1: TSQLTransaction;    procedure Button1Click(Sender: TObject);  private    { private declarations }  public    { public declarations }  end; ---------------------------------------------------------------------- HatForCat · 2017-04-30 18:13 OK, I have it working but it returns "0" for both ShowMessage(... Is this a Lazarus/Ubuntu thing? BUT - the "ShowMessage(FieldByName('cnt').AsString);" is not showing an error for the 'cnt' as it does in my original code. 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 TForm1.Button1Click(Sender: TObject);begin    try        with SQLite3Connection1 do        begin            DatabaseName := '/tmp/test.db';            Connected := True;            ExecuteDirect('CREATE TABLE People (c1 Integer, c2 integer,c3 integer);');        end;         with SQLQuery1 do        begin          Close;            SQL.Text:='UPDATE People SET c1 = 5, c2 = 7, c3 = 9;';            ExecSQL;            Close;            SQL.Text := 'select count(*) from People';            Open;            ShowMessage(Fields[0].AsString); // Shows "0" for me            Close;            SQL.Text := 'select count(*) as cnt from People';          Open;            ShowMessage(FieldByName('cnt').AsString); // Shows "0" for me again            Close;        end;    finally      SQLite3Connection1.Connected := False;        DeleteFile('/tmp/test.db');    end;end; ---------------------------------------------------------------------- ASerge · 2017-04-30 18:32 Quote from: HatForCat on April 30, 2017, 08:13:57 pmOK, I have it working but it returns "0" for both ShowMessage(... 1. You create the table. OK. No records. 2. You update the table. OK. Because there are no records, then nothing is updated. 3.You see how many records in the table. Yes, indeed, zero. And what's strange? ---------------------------------------------------------------------- mangakissa · 2017-05-01 07:06 https://www.w3schools.com/sql/sql_insert.asp https://www.w3schools.com/sql/sql_update.asp https://www.w3schools.com/sql/sql_select.asp https://www.w3schools.com/sql/sql_func_count.asp Read this first before you go further. Tr to understand what you're doing. You have already the answer if you understand what you try to accomplish.