Wednesday 6 June 2007

Storing database fields to a TComboBox

While there is a TDBLookupComboBox that allows the developer to attach the TDBLookupComboBox to a table directly, it does have some issues in its use. I prefer to use a normal TComboBox and feed it from a Query. It is then a standalone ComboBox that can be used for more than just updating a field in one table with a field in another.

But how do I fill the ComboBox? And once filled, how do I know which record the user has selected?

First the Query. I am assuming that, like me, you create a field in every table that is filled with a unique ID. I can use this ID in other tables to create a join. For example, I may have a Customer table ...

CustomerID: Integer
Name: String (e.g. VarChar)

Lets say I want a ComboBox of all my customers for the user to select. The query would look something like this ...

SELECT CustomerID, Name
FROM Customer
ORDER BY Name

I am always sure to ask for the ID first and then the text that the user will want to see. Now lets fill the ComboBox with that detail. Because I may wish to do this with many comboboxes in my application and with many different queries on other tables, I have built a procedure to fill the ComboBox using AddObject.


Procedure FillCombo(cb: TComboBox; Q: TQuery);
begin
cb.items.clear;
Q.First;
while not Q.Eof do
begin
cb.Items.AddObject(Q.Fields[1].AsString, TObject(Q.Fields[0].AsInteger));
Q.Next;
end;
end;



Now I want to use that procedure to fill my ComboBox.

FillCombo(cbCustomer, MyQry);

So how then can we tell which record the user has selected? Simple, I have created a function that will return the ID of the selected record, in this case the selected Customer. If none has been selected, it will return -1.


function GetSelected(cb : TComboBox) : integer;
begin
if cb.ItemIndex = -1 then
result := -1
else
result := Integer(cb.Items.Objects[cb.ItemIndex]);
end;




So all we need to do is call it.

CustomerID := GetSelected(cbCustomer);
if CustomerID = -1 then
// none selected

3 comments:

  1. The main issue with using TDBLookupComboBox is that it expects to be attached to one table and used to lookup a field on another table. Sometimes all you want to do is have the user choose a name or some other field from a table, and nothing more.

    Using the process I described, you will be able to do that easily and without leaving the table open. Of course you will still have to handle the problem where a selected record is no longer available (someone else deleted the record while you were waiting for the user to select from the Combobox), but the pros of this aproach far outweigh the cons.

    ReplyDelete
  2. thanks Steve for telling me about .AddObject!
    this is great way of doing such things (which i was fighting with for few years).
    it would be much easier now!

    Before this i was using TjvDBLookupCombobo from Jedi JVCL:
    You had to select _just_ LookupSource, LookupField and LookupDisplay and u get the proper id from .KeyValue method - as variant which can be null sometime.

    ReplyDelete
  3. i want to fill T combobox from database in LAZURAS using MYSQL55connection & SQL Query1.
    so can u provideme code for it ????

    ReplyDelete

Note: only a member of this blog may post a comment.