1

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?

Improve this question
6
  • 2
    This is the wrong time to populate the combo box. The most severe problem is this: after the first invocation, ADOQueryFillPartners.Free will have made ADOQueryFillPartners a dangling pointer, so the next time you drop down the combo box, you will be using a dangling pointer. You should be using the form's OnCreate handler instead. Also don't forget to use the BeginUpdate; try ... finally EndUpdate end; pattern. Commented Dec 1, 2020 at 21:07
  • Invalid object means just that - for whatever reason a table or view with that name is not visible to the current connection. Wrong database or wrong schema is is often the cause. Commented Dec 1, 2020 at 21:12
  • This question is about populating a combobox from a query. If you have an additional problem, use the Ask Question button at the top of the page and ask about it there in a new post. Commented Dec 1, 2020 at 21:20
  • I was just mentioned that problem, because I'm not sure if that's related to the fact that I can't populate a combobox. Commented Dec 1, 2020 at 21:30
  • Well, clearly if you can't open the query, you can't fill the combobox from that query. If you open the editor for the ADOQuery.SQL property and put your SQL statement in there, and then set the Active property of that component to True, what happens? (Don't forget to set it back to False afterward.) If you can't get that to work, you've not set up the connection string properly for your ADOConnection. Commented Dec 1, 2020 at 21:44

1 Answer 1

Reset to default
2

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

13 Comments

Thank you for your help. I tried it, but still no success, 0 items in the combobox.
See the comment I just added to your question above. If you can't open the dataset (the query), you clearly can't use it to populate the combobox. You should have asked a question about that problem first, before you posted this one.
@scratchingmyhead: See my edited answer for information about your database connection.
I could set the query active this time, but still not working.
Also, if TFormInsertNewItem is auto-created, the datamodule may not exist at the point this code is executed. If that's the case, use Project->Options->Forms, and move the datamodule to the top of the list in the right pane, above all the other items, using the buttons to the right of that list to do so.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.