Populate ComboBox with Table Fields

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
ST3VOAsked:
Who is Participating?
 
bokistCommented:
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
 
Pierre CorneliusCommented:
Try this:

ComboBox1.Items.Assign(ADOQuery1.FieldList);
0
 
Pierre CorneliusCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
ST3VOAuthor Commented:
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
 
ST3VOAuthor Commented:
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
 
diniludCommented:
 ComboBox1.items.clear;
  for i:=0 to ADOQuery1.FieldCount-1 do
    ComboBox1.Items.Assign(ADOQuery1.Fields[i].FieldName);
0
 
ST3VOAuthor Commented:
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
 
diniludCommented:
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
 
ST3VOAuthor Commented:
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
 
diniludCommented:
Then better use DBLookUp Combo box.
0
 
diniludCommented:
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
 
ST3VOAuthor Commented:
I'll try modifing the code to work without the BDE and get back to you. Thanks!
0
 
bokistCommented:
to avoid duplicating:
    Sql.Add('Select distinct(category) from products');
or
    Sql.Add('Select category from products');
    Sql.Add(' group by category');
 
0
 
ST3VOAuthor Commented:
Perfect!!! Exactly what I needed. Thanks a million :o)

Cheers

ST3VO
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.