?
Solved

Populate ComboBox with Table Fields

Posted on 2007-09-28
14
Medium Priority
?
409 Views
Last Modified: 2010-04-05
Hi all,

I'm writing an application which connects to a database using ADO.
Up to there all ok.
In that Database I have create 2 tables.
For the connection I am using TADOQuery.
I have a comboBox which I have populated manually with the names of the 'Categorys' field, which is OK but I would have to check on the ComboxBox manual IF the Category names changed.

Is there a way to import the Categories from the Table into the ComboxBox?

This way I wouldn't have to do it manually.

Thanks again

ST3VO
0
Comment
Question by:ST3VO
  • 6
  • 4
  • 2
  • +1
14 Comments
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 19977756
Try this:

ComboBox1.Items.Assign(ADOQuery1.FieldList);
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 19977765
Maybe I misunderstood. If you want the combo to show the values from the categories field why not use a TDBLookupCombo instead of a normal combo
0
 

Author Comment

by:ST3VO
ID: 19977885
Good question.

The answer is because I tried and only got back the contents of the 'Categories' and not just the Different categorys.

Basically, the Lookup... returned ALL the data from the categories and with the comboBox i'm doing the following:

ADOQuery2.Close;
ADOQuery2.SQL.Clear;
ADOQuery2.SQL.Add('select * from products');
ADOQuery2.SQL.Add('where Category ='''+sComboBox1.Text+'''');
ADOQuery2.Active:=True;
ADOQuery2.Open;

I have a list of the Category names hardcoded...and the code above then gives me all of just the selected Category.

What I want to do is just have the Different Categories populated automatically.

Hope this explaination helps!

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:ST3VO
ID: 19978492
I'll try the explain it a bit further:

I've got a Database to which I'm connected which contains 2 tables.

1. Products
2. Category

Catergory is the column name ---> Under Category I have:

Cereals
Frozen Food
Bakery & Cakes
etc...

I need the above populated in a comboxbox.

Hope this helps

Thanks

ST3VO
0
 
LVL 10

Expert Comment

by:dinilud
ID: 19979106
 ComboBox1.items.clear;
  for i:=0 to ADOQuery1.FieldCount-1 do
    ComboBox1.Items.Assign(ADOQuery1.Fields[i].FieldName);
0
 

Author Comment

by:ST3VO
ID: 19979469
Hi dinilud,

I got an error when I tried your code:

Here is the error Msg:

[Pascal Error] Product.pas(141): E2010 Incompatible types: 'TPersistent' and 'WideString'

0
 
LVL 10

Expert Comment

by:dinilud
ID: 19983232
Sorry my mistake. i didn't check that code.

var i:Integer;
begin
  ComboBox1.items.clear;
  for i:=0 to ADOQuery1.FieldCount-1 do
    ComboBox1.Items.Add(ADOQuery1.Fields[i].FieldName);
0
 

Author Comment

by:ST3VO
ID: 19983287
I'm still misunderstood....sorry about that.

Please read this!

I've got a Database to which I'm connected which contains 2 tables.

1. Products
2. Category

Catergory is the column name ---> Under Category I have:

Cereals                               <---- I want to display this list (Avoiding Duplicates if possible)
Frozen Food
Bakery & Cakes
etc...

I need the above populated in a comboxbox.

Hope this helps

Thanks

ST3VO
0
 
LVL 10

Expert Comment

by:dinilud
ID: 19983500
Then better use DBLookUp Combo box.
0
 
LVL 10

Expert Comment

by:dinilud
ID: 19983551
This is Using BDE.

Unit1.dfm
=========
object Form1: TForm1
  Left = 103
  Top = 168
  Width = 458
  Height = 299
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  OnClose = FormClose
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object DBGrid1: TDBGrid
    Left = 32
    Top = 64
    Width = 369
    Height = 137
    DataSource = DataSource2
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object DBLookupComboBox1: TDBLookupComboBox
    Left = 80
    Top = 16
    Width = 321
    Height = 21
    KeyField = 'Category'
    ListField = 'Category'
    ListSource = DataSource1
    TabOrder = 1
    OnClick = DBLookupComboBox1Click
    OnKeyDown = DBLookupComboBox1KeyDown
  end
  object Table1: TTable
    FieldDefs = <
      item
        Name = 'ItemName'
        DataType = ftString
        Size = 30
      end
      item
        Name = 'Category'
        DataType = ftString
        Size = 20
      end>
    StoreDefs = True
    TableName = 'Item'
    Left = 72
    Top = 32
  end
  object Query1: TQuery
    SQL.Strings = (
      'Select Distinct Category from Item')
    Left = 232
    Top = 56
  end
  object DataSource1: TDataSource
    DataSet = Query1
    Left = 312
    Top = 64
  end
  object Query2: TQuery
    SQL.Strings = (
      'Select * from Item')
    Left = 216
    Top = 136
  end
  object DataSource2: TDataSource
    DataSet = Query2
    Left = 224
    Top = 144
  end
end

Unit1.pas
=========

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBTables, Grids, DBGrids, StdCtrls, DBCtrls;

type
  TForm1 = class(TForm)
    Table1: TTable;
    Query1: TQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    DBLookupComboBox1: TDBLookupComboBox;
    Query2: TQuery;
    DataSource2: TDataSource;
    procedure FormCreate(Sender: TObject);
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure DBLookupComboBox1KeyDown(Sender: TObject; var Key: Word;
      Shift: TShiftState);
    procedure DBLookupComboBox1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
begin
  Table1.CreateTable;
  Table1.Open;
  Table1.AppendRecord(['Item1','Categ1']);
  Table1.AppendRecord(['Item2','Categ1']);
  Table1.AppendRecord(['Item3','Categ1']);
  Table1.AppendRecord(['Item4','Categ2']);
  Table1.AppendRecord(['Item5','Categ3']);
  Table1.AppendRecord(['Item6','Categ3']);
  Query1.Open;
  Query2.Open;
end;

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Query2.Close;
  Query1.Close;
  Table1.Close;
  Table1.DeleteTable;
end;

procedure TForm1.DBLookupComboBox1KeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if Key =VK_BACK then
  begin
     DBLookupComboBox1.KeyValue:=Null;
     DBLookupComboBox1Click(nil);
  end;
end;

procedure TForm1.DBLookupComboBox1Click(Sender: TObject);
begin
  Query2.Close;
  if DBLookupComboBox1.KeyValue<>Null then
    Query2.SQL.Text:='Select * from Item where Category='+
                                         QuotedStr(DBLookupComboBox1.KeyValue)
  else Query2.SQL.Text:='Select * from Item';
  Query2.Open;
end;

end.
0
 

Author Comment

by:ST3VO
ID: 19983825
I'll try modifing the code to work without the BDE and get back to you. Thanks!
0
 
LVL 6

Accepted Solution

by:
bokist earned 2000 total points
ID: 19984178
Hi,
try this way:

   ComboBox.Text := '';
   ComboBox.Items.Clear;
   with Query do
     begin
     Close;
     Sql.Clear;
     Sql.Add('Select * from products');
     Sql.Add(' order by category');
     Open;
     if not EOF  then
        while not (EOF)  do
           begin
           if length(trim(fieldbyname('category').asstring)) > 0  then
                               ComboBox.Items.Add(fieldbyname('category').asstring);
           Next;
     end;
     Close;
   end;
0
 
LVL 6

Expert Comment

by:bokist
ID: 19984194
to avoid duplicating:
    Sql.Add('Select distinct(category) from products');
or
    Sql.Add('Select category from products');
    Sql.Add(' group by category');
 
0
 

Author Comment

by:ST3VO
ID: 19984946
Perfect!!! Exactly what I needed. Thanks a million :o)

Cheers

ST3VO
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month15 days, 16 hours left to enroll

850 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