How can I populate a combobox from a SQL Server database server in Delphi?

Question

First of all, I'm new to Delphi, and I checked all the relevant threads on here, but nothing seems to be working.

I'm just trying to fill a TComboBox with values of a column from a SQL Server database.

So basically, I have a VCL form and a DataModule, which holds an ADOConnection and an ADOQuery. The ADOConnection connects to my database (test connection says it's successful) and the ADOQuery connects to my ADOConnection. The VCL form and the DataModules are "connected".

(And actually here comes another problem: whenever I'm trying to enter SELECT name FROM Partners; in the properties of my ADOQuery and try to set it active, it keeps saying "Invalid object name 'Partners'". The SQL statement is correct, I tested it in SSMS and it retrieves the data without any error. But I'm not sure if it's the main issue here).

So basically, here's my code:

procedure TFormInsertNewItem.ComboBoxPartnersDropDown(Sender: TObject);
begin
  with DataModule1 do
  begin
    ComboBoxPartners.Items.Clear;
    ADOQueryFillPartners.SQL.Clear;
    ADOQueryFillPartners.SQL.Add('SELECT name FROM Partners;');
    ADOQueryFillPartners.Active:=true;
    ADOQueryFillPartners.Open;
    while not ADOQueryFillPartners.Eof do begin
      ComboBoxPartners.Items.Add(ADOQueryFillPartners.FieldByName('name').AsString);
      ADOQueryFillPartners.Next;
    end;
    ADOQueryFillPartners.Active:=false;
    ADOQueryFillPartners.Free;
  end;
end;

Whenever I click to drop it down (or maybe it would be better if this runs when the form is loaded, but I have no idea how can I exactly refer to that event), it should fill it up. Well, it won't. I tried it with TDBComboBox and TDBLookUpComboBox, with setting their datasource, but it only added 1 item to the combobox.

Can anyone help me with what I messed up and how can I get it to work, please?

Answer 1

You have multiple problems in the code you've posted.

Clearly, the first thing you have to do is to get your query to open properly. If you can't run the query to get the data, you can't use that data to populate the combobox. Test your ADOConnection on your datamodule to see if you can connect to the database by setting its Connected property to True.

If that doesn't work, then you need to set up your ConnectionString properly. (Make sure you set the Connected property back to False. You should be connecting in your datamodule's OnCreate event, and disconnecting in the datamodule's OnDestroy event.)

If it does work, check to make sure you set the ADOQuery's Connection property to point to your ADOConnection, and then use the ADOQuery.SQL property to enter your SQL statement. Once you've done that, set the ADOQuery.Active property to True. (Again, don't forget to set it back to False and open it in your code at runtime.)

Next, you've used the wrong event. You should be doing this in the form's OnCreate event instead.

Also, you don't need both Active and Open for the query. They do the same thing; the only difference between them is that Active is a Boolean property that can be tested (if Query1.Active then) while Open is simply a method. There's no reason to open a query that's already active or make a query active that's already open.

In addition, you free the query at the end of your method, which means that the next time the combobox OnDropDown event is called, you're accessing an invalid class instance, which will cause an access violation.

And finally, you should be calling BeginUpdate before clearing and populating the combobox, and then EndUpdate when you're finished.

A more correct version of the code would be something like

procedure TFormInsertNewItem.FormCreate(Sender: TObject);
begin
  with DataModule1 do
  begin
    {
      I'm not sure why you're doing this at all. If you set the ADOQuery.SQL property
      at designtime, and it never changes, you can remove the next two lines.
    }
    ADOQueryFillPartners.SQL.Clear;
    ADOQueryFillPartners.SQL.Add('SELECT name FROM Partners;');
    ADOQueryFillPartners.Active:=true;
    try  
      ComboBoxPartners.Items.BeginUpdate;
      while not ADOQueryFillPartners.Eof do begin
        ComboBoxPartners.Items.Add(ADOQueryFillPartners.FieldByName('name').AsString);
        ADOQueryFillPartners.Next;
      end;
    finally
      ComboBoxPartners.Items.EndUpdate;
    end;
    ADOQueryFillPartners.Active:=false;
  end;
end;