Scroll to English VersionManipulasi Data Menggunakan SQL Statement di Delphi
Aku menggunakan sebuah table di Microsoft Access dengan nama poducts pada database tutorial.mdb dan disimpan di C:\DelphiTutorial\sqldml. tutorial ini menjelaskan bagaimana cara melakukan manipulasi data menggunakan sql statement dengan komponen ADOCommand di delphi 7. hal ini mencakup statement sql select,sql insert, sql update dan sql delete. berikut ini struktur dari table products :
productid
text(10),
description text(50),
price number(long integer)
- Masuk ke Delphi 7.
- Buat sebuah project baru, simpan di C:\DelphiTutorial\sqldml dengan nama
sqldml.dpr.
- tambahkan sebuah komponen ADOConnection (ada di tab ADO) set property
ConnectionStringnya menjadi :
Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=tutorial.mdb;Persist Security Info=False
tambahkan sebuah komponen ADOCommand (ada di tab ADO) set property Connectionnya menjadi ADOConnection1.
SQL Insert statement
Pertama-tama saya akan mengisi table products dengan beberapa record agar perubahan data didalam table karena manipulasi data dapat terlihat.
- tambahkan tiga buah komponen Edit masing-masing beri nama eproductid, edescription, dan eprice dan sebuah komponen Button biarkan komponen button dengan nama defaultnya.
- set property Caption Button1 menjadi 'Save'.
- dobel klik button1, kemudian tambahkan kode perintah berikut pada code editor delphi :
with ADOCommand1 do begin
CommandText := 'insert into products values(:a,:b,:c)';
Parameters[0].Value := eproductid.Text;
Parameters[1].Value := edescription.Text;
Parameters[2].Value := eprice.Text;
Execute;
end;
application.messagebox('Data berhasil disimpan','Informasi',mb_ok);
- run applikasi dengan menekan F9, kemudian isikan beberapa record ke dalam table.
SQL Select Statement
Sebenarnya fungsi utama ADOCommand ini adalah untuk mengeksekusi statement sql yang tidak mengembalikan hasil (result sets) seperti statement SQL insert,SQL update, SQL delete dan statement DDL (Data Definition language) misalnya statement Create Table, Create Procedure, dll. sedangkan contoh statement sql yang mengembalikan hasil (result sets) adalah statement sql select.
kita akan melakukan pencarian record pada table products berdasarkan field description. Untuk menerima hasil kembalian (result sets) dari statement sql ini kita akan menggunakan komponen ADODataSet.
- tambahkan sebuah komponen edit, beri nama esearch dan sebuah komponen button dan beri nama bsearch.
- Ubah property caption dari button bsearch menjadi 'Search'.
- dobel klik tombol bsearch, kemudian tambahkan kode perintah berikut pada code editor delphi :
with ADOCommand1 do begin
CommandText := 'select * from products where description = :desc';
Parameters[0].Value := ecari.Text;
ADODataset1.Recordset := execute;
end;
MessageDlg('Ditemukan '+ inttostr(ADODataset1.recordcount) + ' Record(s)', mtInformation, [mbOk], 0);
Coba lakukan pencarian data barang yang telah dientri sebelumnya dengan cara menginputkan deskripsi barang tersebut pada kotak edit esearch dan tekan tombol Search.
SQL Update Statement
Idenya begini : saya akan menambahkan fitur pencarian sebelum input data dan menambahkan fitur update pada tombol button1.
- klik edit eproductid, pada object inspector klik tab Events, cari event OnKeyDown, dobel klik, kemudian tambahkan kode perintah berikut ini :
if key = vk_return then
begin
with ADOCommand1 do begin
CommandText := 'select * from products where productid = :pid';
Parameters[0].Value := eproductid.Text;
ADODataset1.Recordset := execute;
end;
if ADODataset1.RecordCount > 0 then
if messagedlg('Data produk dengan id '''+eproductid.Text
+''' sudah ada'+chr(10)
+'apakah anda akan merubah data
tersebut',mtconfirmation,[mbok,mbcancel],1) = mrok then
begin
edescription.Text :=
ADODataset1.FieldValues['description'];
eprice.Text := ADODataset1.FieldValues['price'];
edescription.SetFocus;
button1.Caption := 'Update';
{ bdelete.Enabled := true; }
end
else
begin
{ button1.Caption := 'Save'; }
eproductid.SelectAll;
exit;
end
else if ADODataset1.RecordCount < 1 then
begin
{ bdelete.Enabled := false; }
button1.Caption := 'Save';
edescription.clear;
eprice.clear;
end;
edescription.SetFocus;
end;
Pada listing kode diatas ada perintah untuk merubah caption dari tombol button1 yaitu kode perintah dengan teks yang saya tebalkan. caption tersebut akan menetukan mode manipulasi data yang akan dilakukan. biar lebih jelas ubah dulu listing kode perintah pada button1 menjadi seperti dibawah ini :
if button1.Caption='Save' then
begin
with ADOCommand1 do begin
CommandText := 'insert into products values(:a,:b,:c)';
Parameters[0].Value := eproductid.Text;
Parameters[1].Value := edescription.Text;
Parameters[2].Value := eprice.Text;
Execute;
end;
application.messagebox('Data berhasil disimpan','informasi',mb_ok);
end
else if button1.Caption='Update' then
with ADOCommand1 do begin
CommandText := 'update products set description = :a, price = :b where productid = :c';
Parameters[0].Value := edescription.Text;
Parameters[1].Value := eprice.Text;
Parameters[2].Value := eproductid.Text;
execute;
application.messagebox('Perubahan berhasil disimpan','informasi',mb_ok);
end;
eproductid.Clear;
edescription.Clear;
eprice.Clear;
eproductid.SetFocus;
run aplikasi, coba masukkan kode produk yang telah disimpan sebelumnya, maka akan ditampilkan messagebox yang berisi konfirmasi untuk update record atau tidak.
SQL Delete Statement
- tambahkan sebuah komponen button, beri nama bdelete dan ubah Captionnya menjadi 'Delete'.
- ubah property Enable button bdelete menjadi False
- dobel klik tombol bdelete, kemudian tambahkan kode perintah berikut pada code editor delphi :
if messagedlg('Hapus data barang '''+eproductid.Text
+'''',mtconfirmation,[mbok,mbcancel],1) = mrok then
begin
with ADOCommand1 do begin
CommandText := 'delete from products '+
'where productid = :pid';
Parameters[0].Value := eproductid.Text;
execute;
end;
eproductid.Clear;
edescription.Clear;
eprice.Clear;
button1.Caption := 'Save';
bdelete.Enabled := false;
eproductid.SetFocus;
end;
setelah anda menginputkan kode ini pada code editor delphi, selanjutnya anda hilangkan semua tanda '{' dan '}' (blok komentar) pada listing kode program Event OnKeyDown edit eproductid agar tombol bdelete bisa aktif pada saat mode update record dan non aktif pada saat mode insert record. Semoga bermanfaat.
Scroll to Indonesian VersionI use a table in Microsoft Access called products in a file called tutorial.mdb and saved in c:\DelphiTutorial\sqldml. this tutorial is about how to manipulate data using sql statement with ADOCommand component in Delphi 7. this is included sql select statement, sql insert statement, sql update statement and sql delete statement. lets get started.
the following is data structure from products table :
productid
text(10),
description text(50),
price number(long integer)
- Run Delphi.
- Create new project then save as sqldmp.dpr in c:\DelphiTutorial\sqldml.
- Add an ADOConnection component, set its ConnectionString Property to :
Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=tutorial.mdb;Persist Security Info=False
- Add an ADOCommand component, set its Connection property to ADOConnection1.
SQL Insert statement
First of all i will fill the table with some data so we can see the changes that sql statement do to the table's data.
- Add three Edit component eproductid, edescription, and eprice and a Button component, Button1.
- Change Button1 Caption property to 'Save'.
- double klick Button1, then add the following code to the Delphi's code editor :
with ADOCommand1 do begin
CommandText := 'insert into products values(:a,:b,:c)';
Parameters[0].Value := eproductid.Text;
Parameters[1].Value := edescription.Text;
Parameters[2].Value := eprice.Text;
Execute;
end;
application.messagebox('Data saved successfully','Information',mb_ok);
- Run application, fill the table with some data by pressing Button1.
SQL Select Statement
Actually the main purpose of ADOCommand component is to execute sql statements that don't return a result sets including SQL Insert statement, SQL Update statement, SQL delete statement and DDL statement (Data Definition Language) like Create Table, Create Procedure, etc.
To retrieve data using sql select statement i use an ADODataSet component along with ADOCommand component.
- Add an Edit Component, esearch and a Button component, bsearch.
- Change the Caption of bsearch button to 'Search'.
- Double click bsearch, then add the following code to the Delphi's code editor :
with ADOCommand1 do begin
CommandText := 'select * from products where description = :desc';
Parameters[0].Value := ecari.Text;
ADODataset1.Recordset := execute;
end;
MessageDlg('Found '+ inttostr(ADODataset1.recordcount) + ' Record(s)', mtInformation, [mbOk], 0);
After writing the code above, run application then try searching specific record in the Products table by entering product description in the Edit text box esearch then press 'Search' button.
SQL Update Statement
the idea is : i will add searching record feature before entering the data and add updating feature on button1(Save button) button.
- klick eproductid, in the object inspector klick Events tab, look for OnKeyDown event, double klick, then add the following code :
if key = vk_return then
begin
with ADOCommand1 do begin
CommandText := 'select * from products where productid = :pid';
Parameters[0].Value := eproductid.Text;
ADODataset1.Recordset := execute;
end;
if ADODataset1.RecordCount > 0 then
if messagedlg('Product with id '''+eproductid.Text
+''' already exist'+chr(10)
+'do you want to change the data
',mtconfirmation,[mbok,mbcancel],1) = mrok then
begin
edescription.Text :=
ADODataset1.FieldValues['description'];
eprice.Text := ADODataset1.FieldValues['price'];
edescription.SetFocus;
button1.Caption := 'Update';
{ bdelete.Enabled := true; }
end
else
begin
{ button1.Caption := 'Save'; }
eproductid.SelectAll;
exit;
end
else if ADODataset1.RecordCount < 1 then
begin
{ bdelete.Enabled := false; }
button1.Caption := 'Save';
edescription.clear;
eprice.clear;
end;
edescription.SetFocus;
end;
in the code listing above there are a command (the bold one) to change the caption of button1. that caption will determine manipulation mode to the data that will be perform. for more clearly, change the code listing in the button1 as follow :
if button1.Caption='Save' then
begin
with ADOCommand1 do begin
CommandText := 'insert into products values(:a,:b,:c)';
Parameters[0].Value := eproductid.Text;
Parameters[1].Value := edescription.Text;
Parameters[2].Value := eprice.Text;
Execute;
end;
application.messagebox('Data saved successfully','informasi',mb_ok);
end
else if button1.Caption='Update' then
with ADOCommand1 do begin
CommandText := 'update products set description = :a, price = :b where productid = :c';
Parameters[0].Value := edescription.Text;
Parameters[1].Value := eprice.Text;
Parameters[2].Value := eproductid.Text;
execute;
application.messagebox('Changes saved successfully','informasi',mb_ok);
end;
eproductid.Clear;
edescription.Clear;
eprice.Clear;
eproductid.SetFocus;
run application, try to input product id that have entered earlier, then a message box will appear displaying confirmation to update the record or no.
SQL Delete Statement
- Add a button, bdelete and change the Caption to 'Delete'.
- change Enable to False
- double klick bdelete button, then add the following code to the Delphi's code editor :
if messagedlg('Delete products '''+eproductid.Text
+''' data',mtconfirmation,[mbok,mbcancel],1) = mrok then
begin
with ADOCommand1 do begin
CommandText := 'delete from products '+
'where productid = :pid';
Parameters[0].Value := eproductid.Text;
execute;
end;
eproductid.Clear;
edescription.Clear;
eprice.Clear;
button1.Caption := 'Save';
bdelete.Enabled := false;
eproductid.SetFocus;
end;
After writing that code above in delphi's code editor, next, remove
the '{' and '}' sign (comment block)
in OnKeyDown event eproductid code listing in order to activate bdelete button on update record mode and deactivate it on insert record mode.
