How can I display only specified database results based on TListbox items?

I have two forms: frmMakeQuote and frmQuoteTemp

In the frmMakeQuote there are two TListboxes: lboMtrlList and lboSelectedMtrl

lboMtrlList displays the Product Description column from the database.

procedure TfrmMakeQuote.FormCreate(Sender: TObject);
begin
  con := TFDConnection.Create(nil);
  query := TFDQuery.Create(con);
  con.LoginPrompt := false;
  con.Open('DriverID=SQLite;Database=C:\Users\kasio\Documents\Embarcadero\' +
    'Studio\Projects\ProgramDatabase;');
  query.Connection := con;

  query.SQL.Text :=
    'SELECT [Material Description] FROM MtrlDatabase ORDER BY MtrlID';
  try
    query.Open;
    lboMtrlList.Items.Clear;
    while not query.EOF do
    begin
      lboMtrlList.Items.Add(query.Fields[0].AsString);
      query.Next;
    end;
  finally
    query.Close;
  end;
end;

When the person double clicks on any 'product' in the lboMtrlList, it's moved to the lboSelectedMtrl. (Basically, it shows the selected 'products'.)

procedure TfrmMakeQuote.lboMtrlListDblClick(Sender: TObject);
begin
  lboSelectedMtrl.Items.Add(lboMtrlList.Items.Strings[lboMtrlList.ItemIndex]);
end;

I want to be able to display the Product Description and Price columns from the database, of ONLY the selected 'products' from the lboSelectedMtrl. They should be displayed in the TStringGrid called sgdMaterials on the frmQuoteTemp.

I wrote something like this:

procedure TfrmMakeQuote.performMtrlQuery;
var
  i: integer;
begin
   for i := 1 to frmMakeQuote.lboSelectedMtrl.ItemIndex do
   begin
  query.SQL.Text := 'SELECT [Material Description], Price FROM MtrlDatabase ' +
   'WHERE [Material Description] = "'
   + frmMakeQuote.lboSelectedMtrl.Items.Strings[1]
   + '" ORDER BY MtrlID';
  query.Open;
  query.First;
   end;
end;

It doesn't show any error, but it doesn't work and displays nothing and I'm aware that it's probably completely wrong.

Solution

Your loop inside of performMtrlQuery() is wrong. If nothing is actually selected in lboSelectedMtrl, its ItemIndex will be -1 and the loop will not iterate through any items. Not only that, but even if an item were selected, your loop would not iterate through ALL of the available items. Also, when you are indexing into the Strings[] property, you are using a hard-coded 1 instead of the loop variable i.

As for your TStringGrid, why not use a TDBGrid instead, and tie it to a DataSource that is filtering the database by the desired items? In any case, performMtrlQuery() is not doing anything to populate the grid at all, whether it is to store the search results in the grid directly, or to store the results in a list somewhere that frmQuoteTemp can then read from.

Try this instead:

procedure TfrmMakeQuote.performMtrlQuery;
var
  i: integer;
begin
  for i := 0 to frmMakeQuote.lboSelectedMtrl.Items.Count-1 do
  begin
    query.SQL.Text := 'SELECT [Material Description], Price FROM MtrlDatabase' +
      ' WHERE [Material Description] = "'
      + frmMakeQuote.lboSelectedMtrl.Items.Strings[i]
      + '" ORDER BY MtrlID';
    query.Open;
    query.First;
    // do something with query.Fields[0] and query.Fields[1] ...
    query.Close;
  end;
end;

That being said, searching your materials by their descriptions is not the most efficient search option. You should search by their IDs instead. I would suggest an alternative approach to accomplish that - use your TListBox controls in virtual mode (Style=lbVirtual) instead, and store your search results in separate TStringList objects. That way you can store both IDs and Descriptions together in memory while displaying the descriptions in the UI and using the IDs in queries.

Try something more like this:

procedure TfrmMakeQuote.FormCreate(Sender: TObject);
begin
  allmaterials := TStringList.Create;
  selectedmaterials := TStringList.Create;

  con := TFDConnection.Create(Self);
  con.LoginPrompt := false;
  con.Open('DriverID=SQLite;Database=C:\Users\kasio\Documents\Embarcadero\Studio\Projects\ProgramDatabase;');

  query := TFDQuery.Create(con);
  query.Connection := con;
  query.SQL.Text := 'SELECT MtrlID, [Material Description] FROM MtrlDatabase ORDER BY MtrlID';
  try
    query.Open;
    while not query.EOF do
    begin
      allmaterials.Add(query.Fields[0].AsString + '=' + query.Fields[1].AsString);
      query.Next;
    end;
  finally
    query.Close;
  end;

  lboMtrlList.Count = allmaterials.Count;
end;

procedure TfrmMakeQuote.FormDestroy(Sender: TObject);
begin
  allmaterials.Free;
  selectedmaterials.Free;
end;

// lboMtrlList OnData event handler
procedure TfrmMakeQuote.lboMtrlListData(Control: TWinControl; Index: Integer; var Data: string);
begin
  Data := allmaterials.ValueFromIndex[Index];
end;

// lboSelectedMtrl OnData event handler
procedure TfrmMakeQuote.lboSelectedMtrlData(Control: TWinControl; Index: Integer; var Data: string);
begin
  Data := selectedmaterials.ValueFromIndex[Index];
end;

procedure TfrmMakeQuote.lboMtrlListDblClick(Sender: TObject);
var
  Idx: Integer;
begin
  Idx := lboMtrlList.ItemIndex;
  if Idx = -1 then Exit;
  if selectedmaterials.IndexOfName(allmaterials.Names[Idx]) <> -1 then Exit;
  selectedmaterials.Add(allmaterials.Strings[Idx]);
  lboSelectedMtrl.Count := selectedmaterials.Count;
end;

procedure TfrmMakeQuote.performMtrlQuery;
var
  i: integer;
begin
  for i := 0 to selectedmaterials.Count-1 do
  begin
    query.SQL.Text := 'SELECT [Material Description], Price FROM MtrlDatabase' +
      ' WHERE MtrlID = '
      + selectedmaterials.Names[i];
    query.Open;
    query.First;
    // do something with query.Fields[0] and query.Fields[1] ...
    query.Close;
  end;
end;

Lastly, if you switch to a single TCheckListBox or TListView control instead of 2 TListBox controls, you can take advantage of their ability to have checkboxes on each item, then you don't need to deal with the OnDblClick event anymore, and don't need to show two copies of your materials in your UI. The user can just check the desired items before invoking performMtrlQuery().

I would also suggest using a virtual TListView for the search results instead of a TStringGrid. The UI will look better (TStringGrid is not the best looking UI control), and you can utilize memory more efficiently (TStringGrid can be a memory hog if you have a lot of data to display).