Combobox dengan Dua Kolom (Field)

Menampilkan nilai dari dua field atau lebih dalam satu combobox.

di dalam artikel ini saya akan sharing tips ringan pemrograman Delphi untuk menampilkan nilai dari dua kolom/field dari sebuah table di database menggunakan komponen DBLookupComboBox. disini saya akan menggunakan sebuah database yang dibuat dengan Microsoft Access.

Buat Project baru dan tambahkan sebuah komponen ADOConnection, ADOTable, dan sebuah DataSource ke dalam form. Hubungkan komponen ADOConnection di atas dengan table di database yang kamu pilih. Setelah koneksi dengan Database Microsoft Access sukses, langkah selanjutnya adalah menghubungkan komponen ADOTable ke komponen ADOConnection dan hubungkan juga komponen DataSource ke ADOTable.

Tambahkan komponen DBLookupComboBox (Tab DataControls) ke dalam form. set properties ListSource ke komponen DataSource dan KeyField ke salah satu field dari table yang kamu pakai. Untuk menampilkan dua field atau lebih dalam DBLookupComboBox kamu ketik manual nama-nama field yang ingin kamu tampilkan dalam komponen DBLookupComboBox - setiap field dipisahkan dengan titik koma (;) misalnya : productid;descriptions.

Application Design Time

DBLookupComboBox Properties

Application Run Time

SQL : Using subquery to resolving query problems

When working with databases and records sometimes we find difficulties in displaying data from many tables using SQL join. For example you wish to include some aggregate columns from three tables below :

the structure of the products table :

ProductID text(15),
VendorID text(15),
Descriptions text(50),
UnitsInStock integer,
Price integer

Data in the products table :

ProductID  VendorID Descriptions                               UnitsInStock
---------- -------- ------------------------------------------ ------------
SDB0001    V0001    MS SQL Server Database 2005                           5
SDB0002    V0002    Oracle Database 10g Enterprise                        6
SDB0003    V0003    MySQL 5.0 Enterprise Edition                          8
SDBA0001   V0004    Navicat For MySQL Administration                      6
SDBA0002   V0005    Dreamcoder 4.1 For MySQL Enterprise                   4
SDBA0003   V0005    Dreamcoder 4.1 For MySQL Profesional                  8
SDBA0004   V0005    Dreamcoder 4.1 For MS SQL Server Database             4

the structure of the Purchasing table :

ProductID text(15),
VendorID text(15),
PurchaseDate date,
Quantity integer

Data in the Purchasing table :

ProductID  PurchaseDate Quantity
---------- ------------ --------
SDB0001    7/1/2008           10
SDB0002    7/1/2008           12
SDB0003    7/1/2008           12
SDBA0001   7/1/2008            8
SDBA0002   7/1/2008            3
SDBA0003   7/1/2008            8
SDBA0004   7/1/2008            8
SDB0001    7/2/2008            7
SDB0002    7/2/2008            9
SDB0003    7/2/2008            7
SDBA0001   7/2/2008            7
SDBA0002   7/2/2008            3
SDBA0003   7/2/2008            7
SDBA0004   7/2/2008            7

the structure of the sales table :

ProductID text(15),
VendorID text(15),
SalesDate date,
Quantity integer

Data in the sales table :

ProductID  SalesDate    Quantity
---------- ------------ --------
SDB0001     7/1/2008           8
SDB0002     7/1/2008           8
SDB0003     7/1/2008           9
SDBA0001    7/1/2008           4
SDBA0002    7/1/2008           1
SDBA0003    7/1/2008           2
SDBA0004    7/1/2008           5
SDB0001     7/2/2008           4
SDB0002     7/2/2008           7
SDB0003     7/2/2008           2
SDBA0001    7/2/2008           5
SDBA0002    7/2/2008           1
SDBA0003    7/2/2008           5
SDBA0004    7/2/2008           6

there is two productid that have the same values in each of sales and purchasing table. Now you want to display how many quantity bought, quantity sold, and quantity available now by joining three tables above so that query output give you the clear information concerning all products.

I will show you how this can confusing when its done by a query especially for SQL newbies. below is an example of wrong SQL statement that will give you incorrect information about the data :

SELECT a.productid, a.descriptions,
sum(b.quantity) AS prchqty,
sum(c.quantity) as salesqty, a.unitsinstock
FROM products AS a, purchasing AS b, sales c
WHERE a.productid=b.productid
and a.productid=c.productid
GROUP BY a.productid, a.descriptions, a.unitsinstock

query output:

productid descriptions                               prchqty salesqty unitsinstock
--------- ------------------------------------------ ------- -------- ------------
SDB0001   MS SQL Server Database 2005                     34       24            5
SDB0002   Oracle Database 10g Enterprise                  42       30            6
SDB0003   MySQL 5.0 Enterprise Edition                    38       22            8
SDBA0001  Navicat For MySQL Administration                30       18            6
SDBA0002  Dreamcoder 4.1 For MySQL Enterprise             12        4            4
SDBA0003  Dreamcoder 4.1 For MySQL Profesional            30       14            8
SDBA0004  Dreamcoder 4.1 For MS SQL Server Database       30       22            4

the query output above giving incorrect information for the prchqty and salesqty column. it is caused by there are more than one productid which have the same values in each of the purchasing table and the sales table so that when you join the tables using productid column the query output will display the column quantity become twice from real quantity.

to resolving this problem you can use a view and or a subquery but in this tutorial I will only show how to use subqueries to resolving the problem. below is the right SQL statement which will give a correct information about the data (note : this query will not working in previous version of MySQL 6, paradox database, and interbase 6) :

SELECT a.productid, a.descriptions,
sum(b.quantity) AS prchqty, c.salesqty,a.unitsinstock
FROM products AS a, purchasing AS b,
(select productid,sum(quantity) as salesqty
from sales
group by productid) AS c
WHERE a.productid=b.productid
and a.productid=c.productid
GROUP BY a.productid, a.descriptions,
c.salesqty, a.unitsinstock

query output :

productid descriptions                               prchqty salesqty unitsinstock
--------- ------------------------------------------ ------- -------- ------------
SDB0001   MS SQL Server Database 2005                     17       12            5
SDB0002   Oracle Database 10g Enterprise                  21       15            6
SDB0003   MySQL 5.0 Enterprise Edition                    19       11            8
SDBA0001  Navicat For MySQL Administration                15        9            6
SDBA0002  Dreamcoder 4.1 For MySQL Enterprise              6        2            4
SDBA0003  Dreamcoder 4.1 For MySQL Profesional            15        7            8
SDBA0004  Dreamcoder 4.1 For MS SQL Server Database       15       11            4

in the above query, string which in bold is subquery or which I call inline view. the subquery above will produce productid column and salesqty column which the data is taken from the sales table which then aliased as table C, so that each productid from the products table (A) will be joined to one productid in the purchasing table (B) and sales table (C).

Delphi Tips : Displaying Image in DBGrid Columns

In this tutorial I will share tips about how to displaying image in the DBGrid columns. You can use this method to mark rows that meet certain conditions matching with the conditions you specified. The components which I use is a TDBGrid, a TDatasource, and it is of course a TADOTable which is connected to a table in a Microsoft Access database. Set each of the component properties so that the components is connected to each other.

The following example show how images is used to mark the records base on value on the UnitsInStock column.

condition :

the product which its UnitsInStock value greater or equal to 10 is marked with a checklist image (checklist.bmp) and product which its UnitsInStock less than 10 is marked with a warning image (warning.bmp).

The Code

procedure TForm1.DBGrid1DrawColumnCell
(Sender: TObject; const Rect: TRect;
DataCol: Integer; Column: TColumn;
State: TGridDrawState);

var
  Image : TBitMap;
begin
  Image := TBitmap.Create;

   try
    with Image do begin
      Transparent := True;
      TransParentColor := Image.Canvas.Brush.Color;
      TransparentMode := tmAuto;
    end;

{ Image will only displayed if the table is not empty }
if DataSource1.DataSet.RecNo > 0 then
begin
    if Column.Title.Caption = 'Stock OK' then
    begin
      { if the quantity > 10 }
      if (ADOTable1.FieldValues['unitsinstock'] > 10) then
      begin
      Image.LoadFromFile
      (ExtractFilePath(Application.ExeName)+'\good.bmp');
      DBGrid1.Canvas.Draw(rect.Left + 2,Rect.Top,Image);
      end;

      { if the quantity > 0 and <= 10 }
      if (ADOTable1.FieldValues['unitsinstock'] > 0)
      and (ADOTable1.FieldValues['unitsinstock'] <= 10) then
      begin
      Image.LoadFromFile
      (ExtractFilePath(Application.ExeName)+'\warning.bmp');
      DBGrid1.Canvas.Draw(rect.Left + 2,Rect.Top,Image);
      end;

    end;
end;

  finally
    Image.Free;
  end;

end;

You can download the source code here