[solved]multiple sqlite SELECT statements

[oldfart]

Sorry guys - really messed up with this, will post later my code
Thanks for your patience

[oldfart]

The results of the first query are carried on to the results of the second query and persist throughout the results. (If that makes sense).
I get all the unique days from the database in the first query.
Then the second query does a SELECT based on those days; the problem is, I only get the same results - which is wrong.
You might need the sqlite3.dll from _https://www.sqlite.org/2021/sqlite-dll-win64-x64-3360000.zip

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

1. procedure TForm1.doCheckClick(Sender: TObject);
2. var
3.   i, count: Integer;
4.   ndays, mysql, temp: string;
5. begin
6.   SQLite3Connection1.close;
7.   SQLTransaction1.Active := false;
8.    SqlQuery1.SQL.Clear;
9.    StringGrid1.visible:= true;
10.    SqlQuery1.SQL.Text := 'select DISTINCT number_of_days from certificates;';
11.    SQLite3Connection1.Open;
12.    SQLTransaction1.Active := true;
13.    SqlQuery1.Open;
14.    If SQLQuery1.Eof then showmessage('no check found');
15.  
16.    // put the results of the SELECT into a listbox, so I can check correct execution
17.    ListBox1.Items.Clear;
18.  
19.   while not SqlQuery1.Eof do
20.   begin
21.     temp:= SqlQuery1.Fields[0].AsString;
22.     // temporary variable, so I can set a break point to check
23.  
24.     if temp <> '' then ListBox1.Items.Add(temp);
25.     SqlQuery1.Next;
26.   end;
27. // ListBox1 now contains:
28. // 1
29. // 8
30. // 21
31. // 31
32.  
33.   count:= 1;
34.   for i:= 0 to ListBox1.Items.Count -1 do
35.   begin
36.    ndays:= ListBox1.Items[i];  // get number of days from listbox
37.  
38.     SqlQuery1.SQL.Clear;
39. // using any these, does not affect the outcome of the query
40. //   SqlQuery1.Close;
41. //   SqlQuery1.Free;
42. //   SQLite3Connection1.Close;
43. //   SQLTransaction1.Active := false;
44.    mysql:= 'select COUNT(*) from certificates where '+
45.            'number_of_days = '+ ndays +
46.            ' and used = "unused";';
47.    //
48.      SqlQuery1.SQL.Text := mysql;
49. // tried this code instead of mysql = etc .. still same result
50. //   SqlQuery1.SQL.Text := 'select COUNT(*) from certificates '+
51. //                         'where number_of_days = :ndays' +
52. //                         ' and used = "unused";';
53. //    SqlQuery1.Params.ParamByName('ndays').AsString := ndays;
54.  
55.      SQLite3Connection1.Open;
56.      SQLTransaction1.Active := true;
57.      SqlQuery1.Open;
58.  
59.    temp:= SqlQuery1.Fields[0].AsString;
60. // temp now contains the last result of the previous SELECT statement
61. // which is wrong
62.  
63. // put query results ino a string grid
64.    StringGrid1.Cells[0,count]:= ndays;
65.    StringGrid1.Cells[1,count]:= temp;
66.    count +=1;
67.   end;
68.  
69.  
70. end;

         

[korba812]

Maybe you should close TSQLQuery at end of loop?

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

1.    temp:= SqlQuery1.Fields[0].AsString;
2. // temp now contains the last result of the previous SELECT statement
3. // which is wrong
4.  
5. // put query results ino a string grid
6.    StringGrid1.Cells[0,count]:= ndays;
7.    StringGrid1.Cells[1,count]:= temp;
8.    count +=1;
9.    SqlQuery1.Close;   // <----------
10.   end;
11.  
12.  

[oldfart]

solved with
myquery:=TSQLQuery.create(self);
..
..
myquery.destroy