ADO Query, ADO Command, Grafik dan Tips dan Triks

Teddy Marcus, Agus Prijono, Josef Widiahi, 2002, “Pemprograman Delphi dengan ADOExpress:Meangakses Basisdata MS.Access”,Informatika,Bandung

ADOQUERY

With ADOQuery1 do

Begin

Close;

SQL.Clear;

SQL.Add:=’select * from data_parkir’;

SQL.Add:=’order by Operator Desc’;

Open;

End;

With ADOQuery1 do

Begin

Close;

SQL.Clear;

SQL.Add(‘select * from applications where type=:apptype’);

ParamByName(‘apptype’).Value:=QuotedStr(Edit1.Text);

//Properties ADOQuery1.Params : Name : apptype

Open;

End;

Procedure TForm1.FormCreate(Sender:TObject);

Begin

ADOConnection1.GetTableNames(ComboBox1.Items);

End;

Procedure TForm1.ButtonClick(Sender:TObject);

Var tblname:string;

Begin

If ComboBox1.ItemIndex<0 then Exit;

Tblname:=ComboBox1.Items[ComboBox1.ItemIndex];

With ADOQuery1 do

Begin

Close;

SQL.Text:=’select * from ‘+tblname;

Open;

End;

End;

ADOCOMMAND

Procedure TForm1.FormCreate(Sender:TObject);

Begin

Session.GetTableName(‘DBDEMOS’,’*.db’,False,False,ComboBox1.Items); //tab data access

ComboBox1.Text:= ComboBox1.Items.Strings[0];

End;

Implementation

{$R *.dfm}

Function AccessType(fd:TFieldDef):string;

Begin

Case fd.DataType of

ftString : result:=’TEXT(‘+IntToStr(fd.size)+’)’;

ftSmallint: result:=’SMALLINT’;

ftInteger: result:=’INTEGER’;

ftWord: result:=’WORD’;

ftBoolean: result:=’YESNO’;

ftFloat: result:=’FLOAT’;

ftCurrency: result:=’CURRENCY’;

ftDate, ftTime, ftDateTime: result:=’DATETIME’;

ftAutoInc: result:=’COUNTER’;

ftBlob, ftGraphic: result:=’LONGBINARY’;

ftMemo, ftFmtMemo: result:=’MEMO’;

else

result:=’MEMO’;

End;

End;

Procedure TForm1.Button1Click(Sender:TObject);

//create command

Var i:integer;

s:string;

Begin

Table1.TableName:=ComboBox1.Text;

Tabel1.FieldDefs.Update;

s:=’create table ‘+Table1.TableName+’ (’;

with table1.FieldDefs do

Begin

For i:=0 to Count-1 do

Begin

s:=s+’ ‘+Items[i].Name;

s:=s+’ ‘+AccessType(Items[i]);

//Tambahkan function AccessType sebelum procedure TForm1.FormCreate

s:=s+’,’;

end;//for

s[length(s)]:=’)’;

end;//with

Memo1.Clear;

Memo1.Lines.Add(s);

End;

Procedure TForm1.Button2Click(Sender:TObject);

//Create Table and copy data

Var i:integer;

tblname:string;

begin

tblname:=ComboBox1.Text;

//refresh

Button1Click(Sender);

//menghapus table, jika table sudah ada

ADOCommand1.CommandText:=’drop table ‘+tblname;

ADOCommand1.Execute;

//create table

ADOCommand1.CommandText:=Memo1.Text;

ADOCommand1.Execute;

ADOTable1.Tablename:=tblname;

//salin data

Table1.Open; //table paradox

ADOTable1.Open; //table MS Access

Try

While not Table1.Eof do

Begin

ADOTable1.Insert;

For i:=0 to Table1.Fields.Count-1 do

Begin

ADOTable1.FieldByName(Table1.FieldDefs[i].Name).Value:=Table1.Fields[i].Value;

End;//for

ADOTable1.Post;

Table1.Next;

End;//while

Finally

Table1.Close;

ADOTable1.Close;

End; //try

End;

CREATE DATABASE

Procedure TForm1.BtnNewDatabaseClick(Sender:TObject);

Var DataSource:String;

DbName:String;

Begin

DbName:=’SampleData.mdb’;

DataSource:=’Provider=Microsoft.Jet.OLEDB.4.0’+’;Data Source=’+DbName+’;Jet OLEDB:Engine Type=4’;

ADOXCatalog1.Create1(DataSource);//tab ActiveX page

End;

CREATE AND LINK TABLES

Procedure TForm1.BtnAddTablesClick (Sender:TObject);

Var DataSource:String;

Cs:String;

Begin

DataSource:=’Provider=Microsoft.Jet.OLEDB.4.0’+’;Data Source=SampleData.mdb’+’;Persist Security Info=False’;

ADOConnection1.ConnectionString:=DataSource;

ADOConnection1.LoginPrompt:=False;

ADOCommand1.Connection:=ADOConnection1;

Cs:=’create table parkir (jeniskendaraan text(6),biayajampertama float,biayaberikut float,gambar longbinary)’;

ADOCommand1.CommandText:=Cs;

ADOCommand1.Execute;

Cs:=’create table operator (operator text(6),namaoperator text(50))’;

ADOCommand1.CommandText:=Cs;

ADOCommand1.Execute;

Cs:=’create index idxPrimary On Parkir (jeniskendaraan) with Primary’;

//with Primary,Disallow Null,Ignore Null, dan Unique

ADOCommand1.CommandText:=Cs;

ADOCommand1.Execute;

Cs:=’create index idxPrimary On operator (operator) with Primary’;

//with Primary,Disallow Null,Ignore Null, dan Unique

ADOCommand1.CommandText:=Cs;

ADOCommand1.Execute;

Cs:=’create table dataparkir (nopolisi text(9),jeniskendaraan text(6) references parkir (jeniskendaraan), jammasuk datetime, jamkeluar datetime, pos text(3), operator text(6) references operator (operator), biaya float);

ADOCommand1.CommandText:=Cs;

ADOCommand1.Execute;

End; //BtnAddTablesClick

GRAFIK (DBCHART)

ADOQuery1.String

Select top 5 customer.company, sum(orders.itemstotal) as sumitems, count(orders.orderno) as numorders

from customer, orders

where customer.custno=orders.custno

group by customer.company

order by sum(orders.itemstotal) desc

RecordSet Chart tanpa Chart Editor

OnCreate Event:

ADOQuery1.Close;

DBChart1.Legend.Visible:=False;

With DBChart1.SeriesList.Series[0] do

Begin

DataSource:=ADOQuery1;

XLabelsSource:=ADOQuery1Company.FieldName;

XValues.ValueSource:=ADOQuery1SumItems.FieldName;

YValues.ValueSource:=’ ‘;

Marks.Style:=smsXValue;

CheckDataSource;

End;

With DBChart1.SeriesList.Series[1] do

Begin

DataSource:=ADOQuery1;

XLabelsSource:=’ ‘;

XValues.ValueSource:=ADOQuery1NumOrders.FieldName;

YValues.ValueSource:=’ ‘;

CheckDataSource;

End;

ADOQuery1.Open;

INDENTASI PENULISAN

-Indent ke kanan : Ctrl+Shift+i

-Indent ke kiri : Ctrl+Shift+u

MENGUBAH UKURAN KOMPONEN

-Tekan Shift + Geser Panah

MEMINDAH KOMPONEN

-Tekan Control + Geser Panah

PINDAH ANTAR JENDELA

-Tekan F11 atau F12

MENAMPILKAN ITEM TERAKHIR YANG TELAH DITAMBAHKAN

-ListBox1.TopIndex:=ListBox1.Items.Count-1;

HINT DALAM BEBERAPA BARIS

Button1.Hint:=’Hint baris-1’+chr(13)+’ Hint baris 2’;

Button1.ShowHint:=True;

MENJALANKAN PROGRAM EXTERNAL

Menggunakan fungsi windows API yaitu: ShellExecute dan tambahkan pada bagian uses yaitu uses ShellApi

1.Aplikasi Umum

ShellExecute(handle,’open’,PChar(‘c:testapp.exe’), nil, nil, SW_SHOW);

2.Aplikasi NotePad

ShellExecute(handle,’open’,PChar(‘notepad’), nil, nil, SW_SHOW);

3.Aplikasi NotePad dan membuka file

ShellExecute(handle,’open’,PChar(‘notepad’), PChar(‘c:testreadme.txt’), nil, SW_SHOW);

4.Aplikasi web

ShellExecute(handle,’open’,PChar(‘http://www….’), PChar(‘c:testreadme.txt’), nil, SW_SHOW);

5.Aplikasi print

ShellExecute(handle,’print’, PChar(‘c:testreadme.txt’), nil, nil, SW_SHOW);

MESSAGE

-ShowMessage(‘Ini Tombol Pesan’);

-MessageDlg(‘Ini Tombol Pesan’,mtInformation,[mbYes],0);

If MessageDlg(‘Pilih Yes atau No?’,mtConfirmation,[mbYes,mbNo],0)=mrYes then

Begin

Label1.Text:=’Pilihan Anda Yes’;

End;

TMsgDlgtye:

mtWarning, mtError, mtInformation, mtConfirmation, mtCustom

TMsgBtn:

mbYes, mbNo, mbOK, mbCancel, mbAbort, mbRetry, mbIgnore, mbAll, mbHelp

Return Value:

mrYes, mbNo, mrOK, mrCancel, mrAbort, mrRetry, mrIgnore, mrAll, mrHelp

MEMINTA MASUKAN (INPUT BOX)

Var InputString:String;

Begin

InputString:=InputBox(‘Masukkan Nama:’,’Nama Anda Please’,’’);

End;

LISTBOX CASE OF

Procedure TForm1.FormActivate(Sender:TObject);

Begin

ListBox1.ShowHint:=true;

ListBox1.Hint:=’Pilih…!’;

ListBox1.items[0]:=’England’;

ListBox1.items[1]:=’Germany’;

ListBox1.items[3]:=’Spain’;

End;

Procedure TForm1.ListBox1Click(Sender:TObject);

Begin

ListBox1.Caption:=ListBox1.items[ListBox1.itemindex];

Case ListBox1.itemindex of

0 : Label1.caption:=’Hello’;

1 : Label1.caption:=’Oke’;

else

Label1.caption:=’ ’;

end;

end;

LISTBOX FOR TO DO

Procedure TForm1.btn_ulang(Sender:TObject);

Var i:integer;

Begin

ListBox1.Items.Clear;

For i:=1 to 5 do

Begin

ListBox1.items.add(inttostr(i));

End;

End;

LISTBOX WHILE DO

Procedure TForm1.btn_ulang(Sender:TObject);

Var i:integer;

Begin

ListBox1.Items.Clear;

i:=1;

While i<=5 do

Begin

ListBox1.items.add(inttostr(i));

i:=i+1;

End;

End;

LISTBOX REPEAT UNTIL

Procedure TForm1.btn_ulang(Sender:TObject);

Var i:integer;

Begin

ListBox1.Items.Clear;

i:=1;

Repeat

ListBox1.items.add(inttostr(i));

i:=i+1;

Until i>5;

End;

WARNA

Procedure TForm1.btn_warna(Sender:TObject);

Begin

Panel1.color:=clred;

Application.ProcessMessages;//repaint color form

Sleep(3000);//delays 3000 msec

Panel1.color:=clgreen;

End;

SAVE DAN LOAD FILE

1.

Procedure TForm1.loadfromfileTomemo(Sender:TObject);

Begin

If OpenDialog1.Execute then

Memo1.Lines.LoadFromFile(OpenDialog1.FileName);

End;

Procedure TForm1.savememoTofile(Sender:TObject);

Begin

If SaveDialog1.Execute then

Memo1.Lines.SaveToFile(SaveDialog1.FileName);

End;

2.

Procedure TForm1.loadfromfileTocombobox(Sender:TObject);

Begin

If OpenDialog1.Execute then

Combobox1.Items.LoadFromFile(OpenDialog1.FileName);

End;

Procedure TForm1.savecomboboxTofile(Sender:TObject);

Begin

If SaveDialog1.Execute then

Combobox1.Items.saveTofile(SaveDialog1.FileName);

End;

3.

Procedure TForm1.loadfromfileTochecklistbox(Sender:TObject);

Begin

If OpenDialog1.Execute then

Checklistbox1.Items.LoadFromFile(OpenDialog1.FileName);

End;

Procedure TForm1.savechecklistbox.Tofile(Sender:TObject);

Begin

If SaveDialog1.Execute then

Checklistbox1.Items.saveTofile(SaveDialog1.FileName);

End;

4.

Procedure TForm1.loadfromfileToradiobutton(Sender:TObject);

Begin

If OpenDialog1.Execute then

RadioGroup1.Items.LoadFromFile(OpenDialog1.FileName);

If Radiogroup1.Items.Count>5 then

RadioGroup1.Columns:=2;

End;

LAIN-LAIN

Procedure TForm1.FormShow(Sender:TObject);

Begin

If not ADOTable1.Active then ADOTable1.Open;

End;

Procedure TForm1.FormCloseQuery(Sender:TObject; var canclose:boolean);

Begin

If ADOTable1.Active then ADOTable1.Close;

End;

Procedure TForm1.DataSource1StateChange(Sender:TObject);

Var ds:string;

Begin

Case ADOTable1.State of

dsInactive:ds:=’tertutup’;

dsBrowse:ds:=’browse’;

dsEdit:=ds:=’edit’;

dsInsert:=ds:=’insert’;

Else

ds:=’’;

End;

Label1.caption:=ds;

End;

Procedure TForm1.HitungClick(Sender:TObject);

Var vtotal:integer;

Vbookmark:TBookmarkStr;

Begin

Vtotal:=0;

ADOTable1.DisableControls;

//ADOTable1.First;

Vbookmark:=ADOtable1.Bookmark;//simpan posisi record

While not ADOTable1.Eof do

Begin

Vtotal:=Vtotal+ADOTable1.FieldByName(‘Biaya’).AsInteger;

ADOTable1.Next;

End;

Label1.Caption:=IntToStr(Vtotal);

ADOtable1.Bookmark:= Vbookmark;//kembali ke posisi record

ADOTable1.EnableControls;

End;

Procedure TForm1.TampilDataClick(Sender:TObject);

Begin

Edit1.text:=ADOTable1.FieldByName(‘nama operator’).AsString;

End;

Procedure TForm1.EditDataClick(Sender:TObject);

Begin

ADOTable1.Edit;

ADOTable1.FieldByName(‘nama operator’).AsString:= Edit1.text;

ADOTable1.Post;

End;

Procedure TForm1.SimpanDataClick(Sender:TObject);

Begin

ADOTable1.Insert; // atau Append

ADOTable1.FieldByName(‘operator’).AsString:= Copy(Edit1.text,1,4);

ADOTable1.FieldByName(‘nama operator’).AsString:= Edit1.text;

//ADOTable1.InsertRecord(Copy(Edit1.Text,1,4), Edit1.Text);

ADOTable1.Post;

End;

Procedure TForm1.LocateDataClick(Sender:TObject);

Var fnamafield, fvaluefield:string;

Voptcari:TLocateOptions;

Begin

fnamafield:=’operator’;

fvaluefield:=Edit1.text;

voptcari:=[loCaseInsensitive];//pencarian tidak membedakan huruf

//voptcari:=[loPartialKey];//pencarian sebagian atau keseluruhan

if not ADOTable1.Locate(fnamafield, fvaluefield, voptcari) then

ShowMessage(fvaluefield+’ tidak ada ’+fnamafield+#13+’ record ke Eof’)

Else

ShowMessage(fvaluefield+’ ada ’+fnamafield+#13+’ record ke record tersebut’);

End;

Procedure TForm1.LookUpDataClick(Sender:TObject);

Var fnamafield, fvaluefield,fresultfield:string;

hasillookup:variant;

Begin

fnamafield:=’nama operator’;

fvaluefield:=Edit1.text;

fresultfield:=’operator; nama operator’;

hasillookup:=ADOTable1.Lookup(fnamafield, fvaluefield, fresultfield);

if not VarIsNull(hasillookup) then

ShowMessage(‘Data ’+VarToStr(hasillookup[1])+’ditemukan ’+#13+’ record tidak berubah’)

Else

ShowMessage(‘Data ’+VarToStr(hasillookup[1])+’tidak ditemukan ’+#13+’ record tidak berubah’);

End;

Procedure TForm1.SeekDataClick(Sender:TObject);

Var valuefield:string;

Begin

fvaluefield:=Edit1.text;

ADOtable1.Seek(fvaluefield, soFirstEQ);

if ADOTable1.Eof then

ShowMessage(fvaluefield+’ tidak ada ’+fnamafield+#13+’ record ke Eof’)

Else

ShowMessage(fvaluefield+’ ada ’+fnamafield+#13+’ record ke record tersebut’);

End;

Procedure TForm1.CompacDBClick(Sender:TObject);

Var dbSource,dbDestination:widestring;

Const

vcaProvider=’Provider=Microsoft.Jet.OLEDB.4.0; Data Source=’;

Begin

dbSource:=vcsProvider+EdSource.Text;

dbDestination:=vcsProvider+EdDestination.Text;

if FileExists(EdDestination.Text) then

begin

ShowMessage(‘Hapus File ‘+EdDestination.Text);

DeleteFile(EdDestination.Text);

End;

JetEngine1.CompactDatabase(dbSource, dbDestination);

ShowMessage(‘File ‘+EdDestination.Text+’ telah di Compact’);

End;