How do you use the SQLQuery component to run multiple queries?

Question

I have a ListBox showing all the records for a single field (first) in a database. When users click the ListBox item I'd like the value for a different field in the db (last), same record, to be displayed. My code ListBox OnClick event code is:

 SQLQuery2.SQL.Text:='SELECT * FROM Names WHERE first= :FIRST';
 SQLQuery2.Params.ParamByName('FIRST').AsString := ListBox1.Items[ListBox1.ItemIndex];
 SQLQuery2.Open;
 ShowMessage('You selected '+SQLQuery2.FieldByName('last').AsString);

When you click an item, the expected field comes up in the MessageBox. However, if you then click a different item, nothing changes--the MessageBox shows the original field.

I don't know enough about SQLQuery components and how they interact with the underlying db to know what's happening.

[P.S. The db is sqlite3, if that matters, and I'm using Lazarus rather than Delphi, if that matters.]

Answer 1

You need to close your query before opening it again. If you look at the code for Open on TDataSet it sets the Active property to true. The setting code for Active first checks that the value is different before doing any work:

procedure TDataSet.SetActive(Value: Boolean);
begin
  ..
    if Active <> Value then
    begin
    end;
end;

So in your case, the active property is already true and the code just exits.