[SOLVED]Is there a better way to load a combox with data from an SQL query

[Knipfty]

Hi,

I'm creating a filter on a form that filters rows returned from an SQL query.  The query returns rows from look up table.  In this case properties (as in houses).  The code below simply steps thru the rows returned by the query.  I'm thinking of generalizing it as I need the same routine in several places.  Before I do that, I just want to check to see if this is already done by some method I haven't come across yet.

Code: [Select]

  HMLDataModule.AdsQueryProp.First;
  while not HMLDataModule.AdsQueryProp.EOF do
  begin
    ComboBoxPropFilt.Items.Add(HMLDataModule.AdsQueryProp.FieldByName('shortname').AsString);
    HMLDataModule.AdsQueryProp.Next
  end;

Thanks

Knipfty

[Knipfty]

On the probability that there is no such method, I created the following.  Feel free to use it.  I decided to go against a datasource instead of a query or table object just to keep it fairly generalized.

Code: [Select]

procedure LoadComboBoxFromSQL(DS: TDatasource; FieldName: string;
  ComboBox: TComboBox);
var IsActive: boolean;
begin
  IsActive := DS.DataSet.Active;
  if not IsActive then            // If the dataset is not active, then make it active
    DS.DataSet.Active := True;
  DS.DataSet.First;               // 1st record
  while not DS.DataSet.EOF do
  begin
    ComboBox.Items.Add(DS.DataSet.FieldByName(FieldName).AsString);  // Add field to combobox
    DS.DataSet.Next
  end;
  if not IsActive then            // If dataset was closed when we started, close it now
    DS.DataSet.Active := False
end;

Basically, you provide the procedure with the datasource and field you want to load from.  The combobox you want to load the data to.  The procedure will copy the data to the combobox.  Note that is the dataset was cloded at teh start, the procedure will open it, perform its tasks, close it.  If the dataset was open, it will remain so.

[taazz]

Why not use a DBLookupComboBox with out setting the datasource and datafield properties, it behaves as a selection combobox, you don't have to manually fill it up with data and translate between combobox and dataset to get the selected item you simple read the keyValue property. In any case I don't think there is any method sorter or better that the code you propose to fill a comboBox with data you can always make more generic but other than that its the same code.

eg.

Code: [Select]

procedure Dataset2ComboBox(const aDTS:TDataset; const aComboBox:TComboBox; const aFieldName:string);
var
  Tmp : TField;
  BkMark:TBookmark;
begin
  if not aDTS.Active then Exit;
  Tmp := aDTS.FieldByName(aFieldName);
  BkMark := aDTS.GetBookmark;
  aDTS.First;
  repeat
   aComboBox.Items.Add(Tmp.AsString);
   aDTS.Next;
  until aDTS.EOF;
  aDTS.GotoBookmark(BkMark);
  aDTS.FreeBookmark(BkMark);
end;   

be warned this procedure was typed directly in my web browser and it was never tested, its here to show the idea not provide a solution.

[Knipfty]

I did try to play with a DBLookupComboBox.  I was setting the datasource and datafield properties though.  And I was getting a lot of different issues that I was trying to work around.  I finally just gave up and went with the solution I presented.

I do like your idea with the bookmark as I just assumed the query was a throw away because that is what I am doing.  Your solution would preserve the current position if its not.

Thanks

Knipfty

[taazz]

I did try to play with a DBLookupComboBox.  I was setting the datasource and datafield properties though.  And I was getting a lot of different issues that I was trying to work around.  I finally just gave up and went with the solution I presented.

Yeah I do not like it linked to a datasource either it requires that the datasource is in edit or insert mode prior to choosing and that's a bit restrictive in various situations.

But If you set the listsource, listField and KeyField properties only you have a data aware selection ComboBox that is a lot easier to manage and use.

I do like your idea with the bookmark as I just assumed the query was a throw away because that is what I am doing.  Your solution would preserve the current position if its not.

Thanks

Knipfty

In that case you need to also add code to DisableControls and re EnableControls to avoid random scrolls on visible forms eg

Code: [Select]

procedure Dataset2ComboBox(const aDTS:TDataset; const aComboBox:TComboBox; const aFieldName:string);
var
  Tmp : TField;
  BkMark:TBookmark;
begin
  if not aDTS.Active then Exit;
  Tmp := aDTS.FieldByName(aFieldName);
  BkMark := aDTS.GetBookmark;
  aDTS.DisableControls;
  try
    aDTS.First;
    repeat
     aComboBox.Items.Add(Tmp.AsString);
     aDTS.Next;
    until aDTS.EOF;
  finally
    aDTS.GotoBookmark(BkMark);
    aDTS.FreeBookmark(BkMark);
    aDTS.EnableControls;
  end;
end;


[Knipfty]

Hi Tazz,

Well I've added your suggestions.  I used a while loop instead of the the repeat loop you used.  Both will work.  The repeat loop expects their to be at least one record in the query.

Code: [Select]

procedure LoadComboBoxFromSQL(const DS: TDatasource; const FieldName: string;
  ComboBox: TComboBox);
var
  IsActive: boolean;
  B: TBookmark;
begin
  ComboBox.Items.Clear;
  IsActive := DS.DataSet.Active;    // Capture current active status
  if not IsActive then              // If the dataset is not active, then make it active
    DS.DataSet.Active := True       // Open dataset
  else
     B := DS.DataSet.GetBookmark;   // Set a bookmark to revert back to when done
  DS.DataSet.DisableControls;       // disable the combobox while loading the data
  try
    DS.DataSet.First;               // 1st record
    while not DS.DataSet.EOF do     // loop while not EOF
    begin
      ComboBox.Items.Add(DS.DataSet.FieldByName(FieldName).AsString);  // Add field to combobox
      DS.DataSet.Next               // next record
    end;
  finally
    begin
      if not IsActive then          // If dataset was closed when we started, close it now
        DS.DataSet.Active := False  // Close dataset
       else
       begin
        DS.DataSet.GotoBookmark(B); // put the record back to where we started
        DS.DataSet.FreeBookmark(B); // clear the bookmark
       end;
      DS.DataSet.EnableControls;    // enable the combobox
    end;
  end;
end;

Thanks for your feedback!

Knipfty