Delphi Database Tutorial

Yus Waroeng Software Blog’s Delphi Tutorial

pemakaian sorting.

Procedure Tform1.Button1Click(Sender:Tobject);
Begin
fdbisorttable(CustomerTable,TempTable,CustID);
End;

Cari Nama Acak dg SQL

var kata : string;
begin
// CariNama secara Acak
if radiobutton1.Checked then
begin
kata := ‘Select * from kawan ‘;
kata := kata + ‘where (upper(nama)) like ‘+’”‘+’%'+edit1.text+’%'+’”‘+’ order by nama’;
query1.Close;
query1.SQL.Clear;
query1.SQL.Add(kata);
query1.Open;

Sumber : Mikrodata

Mengatasi Key Violation pada Duplikasi Indek

const
eKeyViol = 9729;
eRequiredFieldMissing = 9732;
eForeignKey = 9733;
eDetailsExist = 9734;
eSqlGralerror = 13059;
implementation

{$R *.DFM}

procedure TForm1.Table1PostError(DataSet: TDataSet; E: EDatabaseError;
var Action: TDataAction);
begin
if (E is EDBEngineError) then
if (E as EDBEngineError).Errors[0].Errorcode = eKeyViol then
begin
Application.MessageBox(’Duplikasi …’,'Bahaya’,mb_Ok +
mb_IconQuestion);
Abort;
end;
end;

Koneksi SQL Server dg ADO

procedure TForm1.Button1Click(Sender: TObject);
begin
if ADOConnection1.Connected then
begin
ADOConnection1.Close;
ADOConnection1.ConnectionString :=”;
end;
ADOConnection1.ConnectionString :=‘Provider=SQLOLEDB.1;’ +
‘Persist Security Info=false;’ +
‘User ID=’ + Edit1.Text + ‘;’ + // user Name
‘Password=’ + Edit2.Text + ‘;’ + // Password
‘Initial Catalog=’ + Edit3.Text + ‘;’ + // Database
‘Data Source=’ + Edit4.Text; //Nama komputer / server
Screen.Cursor := crHourGlass;
try
ADOConnection1.Open;
Screen.Cursor := crDefault;
MessageDlg(‘Koneksi berhasil !’, mtInformation, [mbOK],0);
except
on E:Exception do
begin
Screen.Cursor := crDefault;
ADOConnection1.Close;
MessageDlg(‘Koneksi Gagal !’+#13#13+‘Exception:’+#13+E.Message, mtError, [mbOK],0);
end;
end;
end;

Kode Auto Number : (ddmmyy000+1)

procedure Tftransaksi.autono;
var i,y:Integer;
s:String;
begin
With DM.Query1 do
begin
if active=true then close;
SQL.Clear;
SQL.Add(‘SELECT MAX(RIGHT(id_Trans, 3)) AS Expr1 ‘+
        ‘FROM   Transaksi WHERE (LEFT(id_Trans, 6) =:id)’);
prepared;
parameters[0].Value := formatdatetime(‘ddmmyy’,date());
open;
i:=Length(fields[0].AsString);
if i>0 then
begin
     s:=IntToStr(fields[0].AsInteger+1);
     y:=Length(s);

     if y=1 then
        eKd_Trans.Text:=formatdatetime(‘ddmmyy’,date())+‘00′+s else
     if y=2 then
        eKd_Trans.Text:=formatdatetime(‘ddmmyy’,date())+‘0′+s else
     if y=3 then
        eKd_Trans.Text:=formatdatetime(‘ddmmyy’,date())+s else
end else
    eKd_Trans.Text:=formatdatetime(‘ddmmyy’,date())+‘001′;
end;
end;

Melihat Perubahan Data

procedure TForm1.DataSource1UpdateData(Sender: TObject);
begin
ShowMessage('Database Berubah');
end;

Menampilkan daftar fields tabel

procedure TForm1.Button1Click(Sender: TObject);
var
i: Integer;
begin
with Table1 do
begin
Active:=False;
DataBaseName:=Edit1.Text;
TableName:=Edit2.Text;
Active:=True;
Memo1.Clear;
for i:=0 to FieldCount-1 do
Memo1.Lines.Add(Fields[i].FieldName);
end;
end;

Insert, Update, Delete dg SQL

// Insert record
procedure TForm1.Button1Click(Sender: TObject);
begin
with Query1 do
begin
Active:=False;
SQL.Clear;
SQL.Add('Insert into country(NAME,CAPITAL,CONTINENT,AREA,POPULATION)
         values(
           ''A_My_Country'',
           ''A_My_Capital'',
           ''A_My_Continent'',
           1,
           1)');
ExecSQL;
end;
Table1.Refresh;
end;

// Delete record
procedure TForm1.Button2Click(Sender: TObject);
begin
with Query1 do
begin
Active:=False;
SQL.Clear;
SQL.Add('Delete from country where name=''A_My_Country''');
ExecSQL;
end;
Table1.Refresh;
end;

//Update record
procedure TForm1.Button3Click(Sender: TObject);
begin
with Query1 do
begin
Active:=False;
SQL.Clear;
SQL.Add('Update country set name=''A_Your_Country''
         where name=''A_My_Country''');
ExecSQL;
end;
Table1.Refresh;