CODEWITHSUNDEEP
delphidelphi-xe2
ASH
ASH

Reputation: 21

how to get the records from database when I click Listbox

I have two listbox and two tables, one table consist of query_no and query_name and the other one is query_no and Query_item. Now, if I'm going to insert record then it could be:

    Table 1
    Query_no   Query_name
    101        Query1

    Table 2
    Query_no    Query_item
    101         Item1
    101         Item2

My listbox1 display all the query_name from table1 which I put a double click event. If I double click the Query1 from listbox1 then all its items which has the same query_no will be display to my listbox2.

Can anyone could help me to do that? Thanks in advance.

this is my codes so far:

procedure TQueryForm.ListBox3DblClick(Sender: TObject);
var
  i : integer;
begin
  for i := 0  to listbox3.Items.Count-1 do
    if listbox3.Selected[i] then
      sqtrans.SQL.Text := 
        'Select QUERY_ITEMS FROM QUERY_ITEM c '+
        'INNER         JOIN      QUERYTABLE t on t.QUERY_NO=c.QUERY_NO '+
        'where t.QUERY_NO=c.QUERY_NO';
  try
    sqtrans.Open;
    listbox2.Items.Clear;
    while not sqtrans.Eof do
    begin
      listbox2.Items.Add(sqtrans.FieldByName('QUERY_ITEMS').AsString);
      sqtrans.Next;
    end;
  finally
    sqtrans.close;
  end;
end;

Upvotes: 1

Views: 1456

Answers (1)

Ken White
Ken White

Reputation: 125620

You're doing nothing to either use the value from ListBox1 or to properly form your SQL WHERE clause to retrieve only the data you want. (Your current where returns all rows from both tables where the Query_No is the same, without regard for whether it matches the name indicated in ListBox1 or not.)

Something like this should work instead:

procedure TQueryForm.ListBox3DblClick(Sender: TObject);
var
  i : integer;
  SearchVal: string;
begin
  if ListBox1.ItemIndex = -1 then 
    Exit;

  // Disable UI update for ListBox while items are being removed/added
  // to avoid flickering (and improve performance if there are a lot of
  // items).
  ListBox2.Items.BeginUpdate;
  try
    ListBox2.Items.Clear;

    // This presumes that the order of the items in ListBox1
    // are in the same order as the rows in the DB. In other 
    // words, it presumes that the first item in the ListBox
    // is 
    SearchVal := ListBox1.Items[ListBox1.ItemIndex];

    // Populate query, creating parameter that will contain the
    // selected item text from above
    sqTrans.SQL.Text := 'Select Query_Items FROM Query_Item c'#13 +
                        'INNER JOIN QueryTable t'#13 +
                        'ON t.Query_No = c.Query_No'#13 +
                        'WHERE t.Query_Name = :QueryName';

    // Assign the item's text to the parameter to properly form
    // the WHERE clause so that we get only the data we want.
    sqTrans.ParamByName('QueryName').AsString := SearchVal;

    sqTrans.Open;
    try  
      while not sqTrans.Eof do
      begin
        // Populate ListBox2 with the matching rows we want
        ListBox2.Items.Add(sqTrans.FieldByName('Query_Items').AsString;
        sqTrans.Next;
      end;
    finally
      sqTrans.Close;
    end;
  finally
    // Reenable UI update for ListBox2 so newly added items are visible.
    ListBox2.Items.EndUpdate;
  end;
end;

Upvotes: 1

Related Questions