Select values in listbox based on query results

[delphi6BDE]

I'm building an application that allows the user to select some or all of the provided values in a multiselect listbox. I now need to allow them to edit their choices at a later date, so I would like to do the following:
- load the list box of choices
- load the original selection(s) from the database
- select the original selection(s) and allow the user to make the changes

I have this sort of working, but it's not loading all of the selections that were saved. Does anybody know how to do this?

 

[whosrdaddy]

show us what you already have, we can start from there then...

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[djjd47130]

procedure SaveSelectedItems(ListBox: TListBox);
var
  I: Integer;
  Q: TADOQuery;
begin
  //This is presuming Q is already created and has some results
  //  and the data in Q is the same list which is in the list box
  Q.First;
  while not Q.Eof do begin
    Q.Edit;
    try
      if ListBox.Selected[I] then begin
        Q['Selected']:= 1;
      end else begin
        Q['Selected']:= 0;
      end;
    finally
      Q.Post;
    end;
    Q.Next;
  end;
end;

procedure LoadSelectedItems(ListBox: TListBox);
var
  I: Integer;
  Q: TADOQuery;
begin
  //This is presuming Q is already created and has some results
  //  and the data in Q is the same list which is in the list box
  Q.First;
  while not Q.Eof do begin
    if Q.FieldByName('Selected').AsInteger = 1 then begin
      ListBox.Selected[I]:= True;
    end else begin
      ListBox.Selected[I]:= False;
    end;
    Q.Next;
  end;
end;

JD Solutions

 

[delphi6BDE]

Thanks for the answers..

I have values a through p stored in a database and loaded into a multiselect listbox for the user to select as many as they would like. When the data is saved, I store the selections in a different table to be worked by other users at a later date. However, the order may change and the original user should be able to edit the original choices. I would like to load the original list of a through p and select the items that were saved. Loading the available selections is not on issue at all, finding the saved selections is also not an issue, but having them show up in already selected is.

So far I have loaded the original in listbox1 and ran a query query1 to find the original selections. After doping that, I run the following which is selecting some, but not all of the query results. The listbox is full of strings, and I am trying to select them again by finding the related strings, not the ID's that I store.

listbox1 (* denotes selected):
a
b*
c
d*
e*
f*
g*
h
i
j*
k
l
m
n
o
p

code:
query1.First;
for i := 0 to query1.RecordCount do
begin
if query1.FieldByName('field').AsString=listbox1.Items then
begin
lsitbox1.Selected:=true;
query1.Next;
end;
end;

I want to reload this listbox and have the user see the original choices made so an edit can be done. With this code I may only see b, d, and e selected. How do I fix this?

 

[delphi6BDE]

sorry.. yes I have declared i as an integer

 

[DjangMan]

Something along the lines of:

while not (Query1.eof) do begin
    j := lstItems.IndexOf(Query1.fieldbyname('field').AsString);
    if j > -1 then begin
      lstItems.items[j] := lstItems.items[j] + '*';
    end;
    Query1.Next;
end;

 

[djjd47130]

Also, have you considered using a check list box? It may be easier for the user to use checkboxes for each item rather than holding Ctrl and selecting, or however you have it made.

In situations where I want a list displayed, but need to reference each item to some other object somewhere, I make use of the list items' objects. I also have a class which I uniquely store in that list. Most commonly, I use a common class of mine "TListVal" with just two simple properties: Caption and ID. Caption meaning what to display in the list, and ID meaning the ID in the database. That way, when the user selects one of the items, I can read this object and get the ID...

type
  TListVal = class(TObject)
  private
    fID: Integer;
    fCaption: String;
    fDescription: String;
    fSelected: Bool;
  public
    constructor Create(ID: Integer);
    property ID: Integer read fID write fID;
    property Caption: String read fCaption write fCaption;
    property Description: String read fDescription write fDescription;
    property Selected: Bool read fSelected write fSelected;
  end;

implementation

constructor TListVal.Create(ID: Integer);
begin
  fID:= ID;
end;

Refreshing the items in the list...

procedure TForm1.LoadList;
var
  Q: TADOQuery;
  X: Integer;
  V: TListVal;
begin
  //First clear the items from the list, if any
  for X:= 0 to ListBox1.Items.Count - 1 do 
    TListVal(ListBox1.Items.Objects[X]).Free; //Make sure any existing items are free'd, also upon app termination
  ListBox1.Items.Clear;
  //And fetch the data from the database
  Q:= TADOQuery.Create(nil);
  try
    Q.Connection:= Connection;
    Q.SQL.Text:= 'select * from MyTable';
    Q.Open;
      if not Q.IsEmpty then begin
        Q.First;
        X:= 0;
        //Loop through the results
        while not Q.Eof do begin
          V:= TListVal.Create(Q.FieldByName('ID').AsInteger);
          try
            V.Caption:= Q.FieldByName('Caption').AsString;
            V.Description:= Q.FieldByName('Description').AsString;
            V.Selected:= Q.FieldByName('Selected').AsBoolean;
          finally
            Inc(X); //Just in case you need to use X for anything, not used in this example though
            ListBox1.Items.AddObject(V.Caption, V);
            Q.Next;
          end;
        end;
        //Now loop back through and select them accordingly
        for X:= 0 to ListBox1.Items.Count - 1 do begin
          V:= TListVal(ListBox1.Items.Objects[X]);
          ListBox1.Selected[X]:= V.Selected;
        end;
      end;
    Q.Close;
  finally
    Q.Free;
  end;
end;

Now, at any given time, you just get the object of the currently selected item and you automatically have its ID along with it, as well as a property specifying whether or not it's selected in the database (regardless of current user's selection). Of course it would need a lot of tweaking in your case, I'm not even sure if this is something you'll need, but it's another option to throw out there while we're on the topic of list items.

PS - All this above code was written 100% in the website, and has not even been tested in Delphi, so don't be surprised if there's a bug hidden somewhere in what I posted.

JD Solutions