An example for how to deal with OLE DB directly, using its interfaces.

Sometimes we need to deal with OLE DB directly without using data aware components. Interfaces provided by Microsoft allow us work with them easily. And Borland has translates them into Delphi in ADOINT.PAS. Because I love Interbase/Firebird then I try to search the Internet for free Interbase OLE DB provider. And I found SIBProvider, here are the information about it (I have no relationship with SIBProvider).
SIBPROvider - Interbase OLE DB Provider v. 1.00
Web site: www.sibprovider.com
Developed by: Marcio Wesley Borges
e-mail: marciowb@technologist.com
ICQ#: 105147566

This OLE DB Provider works fine for my example but didn’t work for ADOExpress and I don’t know why. OK enough talking here’s the source code. Thank you to delphi3000 admins, you can download the source code at the link above.

unit ufrmProject;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, ExtCtrls, Grids, ADOInt;

type
  TfrmProject = class(TForm)
    pnlRight: TPanel;
    pnlLeft: TPanel;
    strgrdMaster: TStringGrid;
    spltrMiddle: TSplitter;
    strgrdDetail: TStringGrid;
    btnPopulate: TButton;
    btnCommit: TButton;
    btnRollback: TButton;
    btnAppendMstr: TButton;
    btnDeleteMstr: TButton;
    procedure FormCreate(Sender: TObject);
    procedure btnPopulateClick(Sender: TObject);
    procedure strgrdMasterSelectCell(Sender: TObject; ACol, ARow: Integer;
      var CanSelect: Boolean);
    procedure strgrdMasterSetEditText(Sender: TObject; ACol, ARow: Integer;
      const Value: String);
    procedure btnCommitClick(Sender: TObject);
    procedure btnRollbackClick(Sender: TObject);
    procedure btnAppendMstrClick(Sender: TObject);
    procedure btnDeleteMstrClick(Sender: TObject);
  private
    { Private declarations }
  public
    cmd:_Command;
    con:_Connection;
    rs:_RecordSet;
    proj_id:string;//last row project id
    mstLastRow:integer;
    mstEdits:array of boolean;//which columns of last row are edited
  end;

var
  frmProject: TfrmProject;

implementation

{$R *.DFM}

procedure TfrmProject.FormCreate(Sender: TObject);
begin
//create instances using their factories
  con:=CoConnection.Create;
  cmd:=CoCommand.Create;
  rs:=CoRecordSet.Create;
  with con do
  begin
    Provider:='sibprovi.SIBProvider';
    ConnectionString:='Data Source=C:\Temp\Employee.gdb;Location=localhost:;Extended Properties="";Persist Encrypted=True;Encrypt Password=True;Mask Password=True;Cache Authentication=True;Persist Security Info=True;CHARACTER SET="";ROLE=""';
    Open(ConnectionString,'sysdba','masterkey',0);
    BeginTrans;
  end;
  cmd.Set_ActiveConnection(con);
  strgrdMaster.ColWidths[0]:=20;
  strgrdDetail.ColWidths[0]:=20;
//make last row invalid
  mstLastRow:=-1;
end;

procedure TfrmProject.btnPopulateClick(Sender: TObject);
var
  i,j,upr:integer;
  recAfctd:OleVariant;
  flag:boolean;
begin
  with cmd do
  begin
    CommandText:='select a.*,b.full_name from project a left join employee b on b.emp_no=a.team_leader';
    rs:=Execute(recAfctd,EmptyParam,0);
  end;
  with rs,strgrdMaster do
  begin
//clean up the grid's row
    RowCount:=2;
//no column has edited
    SetLength(mstEdits,Fields.Count);
    ColCount:=Fields.Count+1;
    upr:=Fields.Count-1;
//set column's name
    for i:=0 to upr do
    begin
      mstEdits[i]:=false;
      Cells[i+1,0]:=Fields[i].Name;
      Cells[i+1,1]:='';
    end;
    i:=0;
    while not Eof do
    begin
      inc(i);
      RowCount:=i+1;
      for j:=0 to upr do
        if VarIsNull(Fields[j].Value) then
          Cells[j+1,i]:=''
        else
          Cells[j+1,i]:=Fields[j].Value;
      MoveNext;
    end;
    Close;
  end;
//populate detail by imitating select cell
  if i>0 then
    with strgrdMaster do
      strgrdMasterSelectCell(strgrdMaster,1,1,flag);
end;

procedure TfrmProject.strgrdMasterSelectCell(Sender: TObject; ACol,
  ARow: Integer; var CanSelect: Boolean);
var
  i,j,upr:integer;
  recAfctd:OleVariant;
  anyEdit:boolean;
  updStmt,temp:string;
  grid:TStringGrid;
begin
//row change detected
  if (ARow<>mstLastRow)and(ARow>0) then
  begin
    anyEdit:=false;
    grid:=TStringGrid(Sender);
    upr:=grid.ColCount-2;
//are there any edits
    for i:=0 to upr do
      if mstEdits[i] then
      begin
        anyEdit:=true;
        break;
      end;
    if anyEdit then
    begin
//it has been updated
      if proj_id<>'' then
      begin
//bla, bla, bla and presto here comes the update statement
        updStmt:='update project set ';
        temp:='';
        for i:=0 to upr do
          if mstEdits[i] then
          begin
            if temp<>'' then
              temp:=temp+',';
            temp:=temp+grid.Cells[i+1,0]+'=:'+grid.Cells[i+1,0];
          end;
        updStmt:=updStmt+temp;
        updStmt:=updStmt+' where proj_id=:proj_id';
        with cmd do
        begin
          CommandText:=updStmt;
          Parameters.Refresh;
          for i:=0 to upr do
            if mstEdits[i] then
            begin
              Parameters[grid.Cells[i+1,0]].Value:=grid.Cells[i+1,mstLastRow];
              mstEdits[i]:=false;
            end;
          Parameters['proj_id'].Value:=proj_id;
          Execute(recAfctd,EmptyParam,0);
        end;
      end
//it has been inserted
      else
      begin
//generate insert statement
        updStmt:='insert into project (';
        temp:='';
        for i:=0 to upr do
          if mstEdits[i] then
          begin
            if temp<>'' then
              temp:=temp+',';
            temp:=temp+':'+grid.Cells[i+1,0];
          end;
        updStmt:=updStmt+StringReplace(temp,':','',[rfReplaceAll])+') values (';
        updStmt:=updStmt+temp+')';
        with cmd do
        begin
          CommandText:=updStmt;
          Parameters.Refresh;
          for i:=0 to upr do
            if mstEdits[i] then
            begin
              Parameters[grid.Cells[i+1,0]].Value:=grid.Cells[i+1,mstLastRow];
              mstEdits[i]:=false;
            end;
          Execute(recAfctd,EmptyParam,0);
        end;
      end
    end;
//get new project id
    proj_id:=TStringGrid(Sender).Cells[1,ARow];
//populate detail using new project id
    with cmd do
    begin
      CommandText:='select a.*,b.full_name from employee_project a,employee b where a.proj_id=:proj_id and b.emp_no=a.emp_no';
      Parameters.Refresh;
      Parameters['proj_id'].Value:=proj_id;
      rs:=Execute(recAfctd,EmptyParam,0);
    end;
    with rs,strgrdDetail do
    begin
      RowCount:=2;
      ColCount:=Fields.Count+1;
      upr:=Fields.Count-1;
      for i:=0 to upr do
      begin
        Cells[i+1,0]:=Fields[i].Name;
        Cells[i+1,1]:='';
      end;
      i:=0;
      while not Eof do
      begin
        inc(i);
        RowCount:=i+1;
        for j:=0 to upr do
          if VarIsNull(Fields[j].Value) then
            Cells[j+1,i]:=''
          else
            Cells[j+1,i]:=Fields[j].Value;
        MoveNext;
      end;
      Close;
    end;
    mstLastRow:=ARow;
  end;
end;

procedure TfrmProject.strgrdMasterSetEditText(Sender: TObject; ACol,
  ARow: Integer; const Value: String);
begin
//a cell is edited
  mstEdits[ACol-1]:=true;
end;

procedure TfrmProject.btnCommitClick(Sender: TObject);
begin
  with con do
  begin
    CommitTrans;
    BeginTrans;
  end;
end;

procedure TfrmProject.btnRollbackClick(Sender: TObject);
begin
  with con do
  begin
    RollbackTrans;
    BeginTrans;
  end;
end;

procedure TfrmProject.btnAppendMstrClick(Sender: TObject);
begin
  strgrdMaster.RowCount:=strgrdMaster.RowCount+1;
end;

procedure TfrmProject.btnDeleteMstrClick(Sender: TObject);
var
  i,upr:integer;
  recAfctd:OleVariant;
begin
  with cmd do
  begin
    CommandText:='delete from project where proj_id=:proj_id';
    Parameters.Refresh;
    Parameters['proj_id'].Value:=proj_id;
    Execute(recAfctd,EmptyParam,0);
  end;
  with strgrdMaster do
  begin
    upr:=ColCount-1;
    for i:=1 to upr do
      Cells[i,mstLastRow]:='';
  end;
end;

end.