unit MainForm;

interface

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

const
 ConnectionString =
  'Data Source=c:\Program Files\Common Files\Borland Shared\Data\DBDemos.mdb;' +
  'Provider=Microsoft.Jet.OLEDB.3.51';

type
 TForm1 = class(TForm)
  StringGrid1: TStringGrid;
  btnLoadGrid: TButton;
  btnAdd: TButton;
  btnAddDuplicate: TButton;
  btnDelete: TButton;
  btnSearch: TButton;
  btnSort: TButton;
  procedure FormCreate(Sender: TObject);
  procedure btnLoadGridClick(Sender: TObject);
  procedure btnAddClick(Sender: TObject);
  procedure btnAddDuplicateClick(Sender: TObject);
  procedure btnDeleteClick(Sender: TObject);
  procedure btnSearchClick(Sender: TObject);
  procedure btnSortClick(Sender: TObject);
 private
  { Private declarations }
  FConnection: _Connection;
  FRecordset: _Recordset;
  FByOrder: Boolean;
 public
  { Public declarations }
 end;

var
 Form1: TForm1;

implementation

{$R *.DFM}

procedure ShowADOErrors(ErrorList: Errors);
var
 I: Integer;
 E: Error;
 S: String;
begin
 for I := 0 to ErrorList.Count - 1 do begin
  E := ErrorList[I];
  S := Format('ADO Error %d of %d:'#13#13'%s',
   [I + 1, ErrorList.Count, E.Description]);
  ShowMessage(S);
 end;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
 FConnection := CoConnection.Create;
 FConnection.Open(ConnectionString, 'admin', '', -1);

 FRecordset := CoRecordset.Create;
 FRecordset.Open('SELECT * FROM Orders ORDER BY OrderNo',
  FConnection, adOpenKeyset, adLockOptimistic, adCmdText);

 FByOrder := True;

 btnLoadGridClick(Sender);
end;

procedure TForm1.btnLoadGridClick(Sender: TObject);
var
 V: OleVariant;
 S: String;
 Row, Col: Integer;
begin
 try
  FRecordset.MoveFirst;

  StringGrid1.ColCount := FRecordset.Fields.Count;
  StringGrid1.RowCount := FRecordset.RecordCount + 1;

  for Col := 0 to FRecordset.Fields.Count - 1 do
   StringGrid1.Cells[Col, 0] := FRecordset.Fields.Item[Col].Name;

  Row := 1;
  while not FRecordset.EOF do begin
   for Col := 0 to FRecordset.Fields.Count - 1 do begin
    V := FRecordset.Fields.Item[Col].Value;
    if VarType(V) <> varNull then
     S := V
    else
     S := '';
    StringGrid1.Cells[Col, Row] := S;
   end;
   Inc(Row);
   FRecordset.MoveNext;
  end;
 except
  ShowADOErrors(FConnection.Errors);
 end;
end;
procedure TForm1.btnAddClick(Sender: TObject);
var
 Fields: OleVariant;
 Values: OleVariant;
begin
 Fields := VarArrayCreate([1, 3], varVariant);
 Values := VarArrayCreate([1, 3], varVariant);

 try
  Fields[1] := 'OrderNo';
  Fields[2] := 'CustNo';
  Fields[3] := 'EmpNo';
  Values[1] := 99999;
  Values[2] := 3615;
  Values[3] := 2;
  FRecordset.AddNew(Fields, Values);
  btnLoadGridClick(Sender);
  ShowMessage('OrderNo 99999 was added');
 except
  ShowADOErrors(FConnection.Errors);
 end;
end;

procedure TForm1.btnAddDuplicateClick(Sender: TObject);
var
 Fields: OleVariant;
 Values: OleVariant;
begin
 Fields := VarArrayCreate([1, 3], varVariant);
 Values := VarArrayCreate([1, 3], varVariant);

 try
  Fields[1] := 'OrderNo';
  Fields[2] := 'CustNo';
  Fields[3] := 'EmpNo';
  Values[1] := 1035;
  Values[2] := 1645;
  Values[3] := 2;
  FRecordset.AddNew(Fields, Values);
 except
  ShowADOErrors(FConnection.Errors);
 end;
end;

procedure TForm1.btnDeleteClick(Sender: TObject);
begin
 FRecordset.MoveFirst;
 FRecordset.Find('OrderNo=99999', 0, adSearchForward, 0);
 if not FRecordset.EOF then begin
  FRecordset.Delete(adAffectCurrent);
  btnLoadGridClick(Sender);
  ShowMessage('OrderNo 99999 was deleted.');
 end else
  ShowMessage('OrderNo 99999 was not found.');
end;

procedure TForm1.btnSearchClick(Sender: TObject);
begin
 FRecordset.MoveFirst;
 FRecordset.Find('OrderNo=1070', 0, adSearchForward, 0);
 if not FRecordset.EOF then
  ShowMessage('OrderNo 1070 was found.')
 else
  ShowMessage('OrderNo 1070 was not found.')
end;

procedure TForm1.btnSortClick(Sender: TObject);
begin
 FByOrder := not FByOrder;

 FRecordset.Close;
 if FByOrder then
  FRecordset.Open('SELECT * FROM Orders ORDER BY OrderNo',
   FConnection, adOpenKeyset, adLockOptimistic, adCmdText)
 else
  FRecordset.Open('SELECT * FROM Orders ORDER BY CustNo, OrderNo',
   FConnection, adOpenKeyset, adLockOptimistic, adCmdText);

 btnLoadGridClick(Sender);
end;
