Solved

Load a database column and its contents into ComboBox

Posted on 2013-01-19
16
3,592 Views
Last Modified: 2013-01-27
Hi all,

First of all, I'm practicing on databases these days so expect further questions next days :p

I have a database and a couple of tables in it. In my application I placed a regular combobox and tried to fill it with a specific column's values.

Let's call the column for instance "products" and it gets updated by  adding(inserting) new products to it.

I want to display all the products in the column in that combobox.

I'm almost there but with a lot of google search I got lost a little bit.

TStringList, TADOQuery, DBLookUpComboBox, TADOTable etc. << all of these techniques of achieving the result got me confused.

By the way, I have a TADOtable component placed on the form. If I can achieve the result without using the TQuery component that would be great. If DBcomboBoxes sets can solve the problem, I would be glad to hear your thoughts about them.

Added: The database is MS-Access
0
Comment
Question by:gxs
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 17

Assisted Solution

by:TheRealLoki
TheRealLoki earned 50 total points
ID: 38797942
if you want the combobox to set a field (in a nother query/table) then yes, you can easily use a TDBCombobox.
make a TADOQuery  just for the products table
add a TDatasource and point it to the query
set that datasource (and field) as the lokoup for the TDBCombox
Make a new TADOQuery for your actual work, and put that filed in the dbcombobox


Other wise, you could do this for a normal combobox

combobox1.items.clear;
adoquery1.open;
adoquery1.first;
while not adoquery1.EOF do
begin
  combobox1.items.add(adoquery1.FieldByName('product').AsString;
  adoquery1.Next;
end;

edit: note, you don't have to use a query, you could use a tadotable
0
 
LVL 25

Expert Comment

by:Sinisa Vuk
ID: 38798877
If you new in delphi/db programming I propose some texts before:
http://delphi.about.com/od/database/l/aa090401a.htm
For your task you should use TDBLookupCombobox component. This component gets values from secondary query where you set your table where values are. You must set few properties (shown in article in upper link).
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 38799309
don't be afraid of a query
it's the most powerfull component to write a fast database app

the rest is simply built around a query
0
 
LVL 1

Author Comment

by:gxs
ID: 38799994
Thanks for all of your answers,

Well, i'll start of with TheRealLoki,

Your first section of your comment is not what i'm looking for but thanks I appreciate the effort for real.

The second section, that's what i'm looking for partially. I have seen that code many times before and was wondering why the code has to with EOF ? { End of File ? } as I know ?

In addition, I tried to apply it with previously knowing that it won't work because of the missing SQL property and I was right.

Note: your code is missing a ")" at the end of "AsString;"

--------------------------------------------------------------------------------

Sinisav, Thanks for your source, I have seen most of the chapters but nothing solved my problem so far.

--------------------------------------------------------------------------------

Greet_Gruwez, I appreciate your participation in my question,

What you said is true and I did a lot of search. TQuery is the heart of everything. Thanks for opening my eyes. I wasn't afraid when I said I want to achieve the result without the query component but I just wanted to avoid having too many dbgrids, tadotables, connectors etc. on the form. Tquery will add to the complication.

The thing that shaked my head today is when I discovered that SQL is not about MySQL and MSSQL only. It is a query language for every database no matter its type. I was avoiding SQL because I thought they had to do with MySQL and MSSQL only but it turned out that it is the boss of everything.

Because of your advice, i'm diving into these tutorials:

http://delphi.about.com/od/database/l/aa050101a.htm

--------------------------------------------------------------------------------

My problem was a little misunderstood and I apologize in advance.

I don't want to combobox to be inside a grid. I want it to be out.

Imagine the form has nothing in it except a couple of TEdits and a combobox only filled with products name.

I found the query that I want a couple of minutes ago:

'SELECT DISTINCT Products FROM warehouse';

I tried to execute it via ADOCommand or TQuery but couldn't figure out how to capture and return the values plus filling them in the combobox.

I will do more research on that and I thank you all for your comments. If you have ideas, solutions, please post them.
0
 
LVL 25

Assisted Solution

by:Sinisa Vuk
Sinisa Vuk earned 75 total points
ID: 38800144
So, again. I propose to use TDBLookupCombobox. It is quite easy. You need two datasource and two query/dataset components. First one is your main tables where you have something like Orders. Second dataset is for lookup in Customers. Each Order is linked to Customer.
Put TDBLookupCombobox component on form,  set DataSource property to DataSource1 (Orders), property DataField to field ID which is link field to table Customers. For lookup thing set property ListSource to DataSource2 (Customers), set property KeyField to ID field in table Customers and ListField to field Desription which is visually representaion of table Customers. You can list more fields separated by semicolon sign. Look for example I attached here.

If you affraid of using lots of datasets on form I propose to do:
- try to write optimized design/code - reuse query or create query on run time or use TDataModule sub-unit/form where you can put all db datasets, datasources, connection components (add TDataModule in existing project and add unit in uses)

- create query on run time:
...
  dbTable:=TADODataSet.Create(Self);
  try
      dbTable.CommandType:=cmdText;
      dbTable.CommandText:=sSQL;
      dbTable.Connection:=ADOConn1;
      dbTable.AfterClose:=DBTableClose;
      dbTable.Open;     
  except  
  end;

...

procedure TForm1.DBTableClose(DataSet: TDataSet);
begin
  DataSet.Free;
end;

Open in new window

ado-example.zip
0
 
LVL 36

Accepted Solution

by:
Geert Gruwez earned 375 total points
ID: 38801460
lol, first steps in query land (aka database land)
very nice to see you jumping in the deep end.
>> this always makes the best swimmers, or the ones who never swim again ...

good start with the distinct

i assume you want to display a form with all information about a product
for this your going to use a combobox to select the specific product

steps in general:
- initialize form
-- setup connection to database
-- reset all entries in display (make empty for short)
-- fill the combo with products
- upon select from combo
-- fill all entries in display with specific product

setup connection is easiest with a TADOconnection on the form
in the formcreate, you might want to open it
connection loss, reconnect , etc ... is out of scope for this atm

reset all entries will be required as the initial display
i guess you don't want to load a default which will have the user wait for 1 sec more on startup, combobox selected index will be -1 in this case

fill the combo
procedure TForm1.FillCombo(Items:   TStrings);
var Q: TAdoQuery;
begin
  Item.BeginUpdate;
  try
    Items.Clear;
    Q := TAdoQuery.Create(Self);
    try
      Q.Connection := Conn1; // connection object on form
      Q.SQL.Text := 'SELECT DISTINCT ID, PRODUCTS FROM WAREHOUSE';
      Q.Open;
      while not Q.Eof do 
      begin
        Items.AddObject(Q.FieldByName('PRODUCTS').AsString, TObject(Q.FieldByName('ID').AsInteger));
        Q.Next;
      end;
    finally
      Q.Free;
    end;
  finally
    Items.EndUpdate;
  end;
end;

Open in new window


the combo you'll attach the OnSelect event
and in that event, find what ID or products was selected
with ID:
Id := -1;
if Combobox1.ItemIndex > -1 then 
   Id := Integer(Combobox1.Items.Objects[Combobox1.ItemIndex]);

Open in new window

with that id you can then select more info
and then fill all the display entries with that query data

procedure TForm1.FillDisplay(ID: integer);
var Q: TAdoQuery;
begin
  // reset all display entries
  edtName.Text := '';
  edtDescr.Text := '';
  // etc ...
  ///
  Q := TAdoQuery.Create(Self);
  try
    Q.Connection := Conn1; // connection object on form
    Q.SQL.Text := 'SELECT * FROM WAREHOUSE WHERE ID = :ID';
    Q.ParamByName('ID').AsInteger := ID;
    Q.Open;
    if not Q.Eof then
    begin
      edtName.Text := Q.FieldByName('NAME').AsString;
      edtDescr.Text := Q.FieldByName('DESCR').AsString;
      // etc
    end;
  finally
    Q.Free;
  end;
end;

Open in new window


follow how it works ?
0
 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 375 total points
ID: 38801472
if you don't have an ID column, or other integer primary key
it also works using the text of combobox as parameter

Id := '';
if Combobox1.ItemIndex > -1 then 
   Id := Combobox1.Items.[Combobox1.ItemIndex];

procedure TForm1.FillDisplay(ID: string);
var Q: TAdoQuery;
begin
  // reset all display entries
  edtName.Text := '';
  edtDescr.Text := '';
  // etc ...
  ///
  Q := TAdoQuery.Create(Self);
  try
    Q.Connection := Conn1; // connection object on form
    Q.SQL.Text := 'SELECT * FROM WAREHOUSE WHERE PRODUCTS = :ID';
    Q.ParamByName('ID').AsString := ID;
    Q.Open;
    if not Q.Eof then
    begin
      edtName.Text := Q.FieldByName('NAME').AsString;
      edtDescr.Text := Q.FieldByName('DESCR').AsString;
      // etc
    end;
  finally
    Q.Free;
  end;
end;

Open in new window

0
 
LVL 1

Author Comment

by:gxs
ID: 38803854
sinisav, your solution is cool. I liked it and i will accept it when we end this discussion.

-----------------------------------------------------------------------------------------------------

Greet_Gruwez, woow. you opened my eyes wider and wider.

Seriously, I haven't touched on advanced techniques yet. But I have a moderate knowledge about them. I mean by that your procedural declarations.

I took your code and declared the procedure in the main Type section as:

procedure FillCombo(Items:   TStrings);

Open in new window


Then, the red lines (errors) disappeared.

The first thing popped in my mind was, why is he using TStrings instead of TStringList ?

I'm familiar with TStringList, but TStrings? that's my first experience with it. I discovered that combobox.items use TStrings then I was like ahaaaa. :p

Your code does make since except that it doesn't fill the combobox with items ( who knows I might be wrong ).

So I edited your code and end up with the following:

procedure TMainForm.FillCombo(Items: TStrings);
var Q: TAdoQuery;
begin
  Items.BeginUpdate;
  try
    Items.Clear;
    Q := TAdoQuery.Create(Self);
    try
      Q.Connection := databaseconnector; // connection object on form
      Q.SQL.Text := 'SELECT DISTINCT ID, nameofcustomer FROM customers';
      Q.Open;
      while not Q.Eof do
      begin
        Items.AddObject(Q.FieldByName('customers').AsString, TObject(Q.FieldByName('ID').AsInteger));
  here >>>>>      productscombobox.Items.AddStrings(items);
        Q.Next;
      end;
    finally
      Q.Free;
    end;
  finally
    Items.EndUpdate;
  end;
end;

Open in new window



and by the way, in your code:   Items.BeginUpdate; << items is missing an s (item)

The result was that the combobox is still empty and I don't know why. Is it because the procedure needs an event to be executed? Does the code have an error ?

I hope you clarify my misunderstanding.

Appreciated.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 1

Assisted Solution

by:gxs
gxs earned 0 total points
ID: 38803878
This code does fill the combobox perfectly:

procedure TMainForm.FormCreate(Sender: TObject);
begin
     with ADOTable1 do begin
      Close;
      Open;
           while not Eof do begin
               ComboBox1.Items.Add(ADOTable1.FieldByName('Products').AsString);
           Next;
           end;
    end;
end;

Open in new window


But I didn't like it to be honest. It does fill the combobox with strings only. I liked your way Gruwez because it is more organized and it is based on objects, indexes so they can be catch-able but still, i'm not able to figure out how to get these objects out and pass them to the combobox.

The other thing I hope you all clarify is why the code is touching on EOF ? I didn't get that at all.

Thanks and i'm really sorry if this discussion feels heavy.
0
 
LVL 36

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 375 total points
ID: 38804454
lol, i'm still argueing with my fingers about the typos they put in
in the end they agreed to type an update.

my fingers are blaming me for not testing in delphi all the solutions i give
(my index finger likes hitting F9, and who can blame em ?)

i see i forget to call the fillcombo ... my bad:
procedure TForm1.FormShow(Sender: TObject);
begin
  FillCombo(productscombobox.Items);
end;

Open in new window


TStrings is the parent of TStringList
all the "lists" are basically descendant of TStrings, or descendants of descendants.

with this approach you can even move the FillCombo to a different unit ... :)
or fill any number of combos
> good for a library of reusable code

my best advice:
avoid circular unit reference
> never "uses mainform" in any other unit.
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 38807567
lol, you want pro

try looking for rem objects
0
 
LVL 1

Author Comment

by:gxs
ID: 38808078
Woooow, that's whats up man << looked into rem objects.

Regarding the circular reference. I'm thinking about it like mmmmm.

In the MotherUnit (as I call it) I call other units and type their names in the uses section of the interface.

While in the other units I call the MotherUnit in the uses section of the implementation.

Using this method so far doesn't raise flags. But if I called the units whether the mother or the children in the interface section? It gives an error about circulating.

I would like you to just give me a little hint or a keyword to search if there are different methods of calling units than using "uses".

Finally, I don't know how am I suppose to pay you back for your effort and patience on my stupidity :p

Here is a thing that i'm applying on practices: (you may know it, I don't know :$)

http://edn.embarcadero.com/article/39115

Appreciated
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 38808903
From your ref: RockSolid has nothing to do with just adding a error logging tool
they should rename that to ErrorProof
i use MadExcept from Madshi (aka Matthias Rauen)
> check the overal point ladder in delphi (662k poinx)

circular unit problem:
consider designing a form for loading a image (unit uLoadImage)
parameters: optional start dir, returns filename and image
> yes this exists already, i know
when you add uses MainUnit to the uses clause of uLoadImage, the compiler will see that dependency and do the following

step 1: compile interface of uLoadImage
step 2: compile interface of MainUnit
step3: compile implementation of uLoadImage
step 4: compile implementation of MainUnit

now consider a new project, and a new developer
the new developer gives name MotherUnit to the main unit
the new developer has to create a loadimage functionality
he finds the existing unit and adds it to his project
then compiles it
> error MainUnit not found
so instead of just referencing the original unit, he copies it to his project directory
modifies the MainUnit to MotherUnit
then finds more compiler errors:
> frmMain not found
so renames the frmMain to frmMother
etc ... until all is fixed
and compiles and it works ... no problem, except it took a day to rewrite parts of the uLoadImage
and now you have 2 versions of it

and then a new developer arrives and names main unit uMaster with frmMaster

i hope you don't have 20 or more developers your working with ... :)
0
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 38808906
>>A simple solution but not that professional

i guess your talking about circular unit reference ?
0
 
LVL 1

Author Comment

by:gxs
ID: 38808978
I got it now. I got it Gruwez.

About the error logging part. I liked in the article how they combined both tools together. MadExcept is cool. Whether your choose EL or ME, you can't go wrong but I see that EL does provide little more and accurate features. You remind me of Acunetix.com app. It does use MadExcept and I consider that Acunetix to be a pride of Delphi since Skype is gone (Microsoft) because it is really complicated & advanced.

Loool. Yeah, you're absolutely right about the developer team thing. I got the picture now. I'm just going solo and training myself for the time being but thanks for the hint.

About the "A simple solution but not that professional". It is about my answer only when I selected it to be as a multiple answer. The one of filling the combobox.

I appreciate your effort and time.
0
 
LVL 1

Author Closing Comment

by:gxs
ID: 38823817
A simple solution but not that professional
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now