DELPHI: Databases
 

2. Video on using ADO Tables

3. Sample data files see the Data page

4. Database HOW TO... guide

EXAMPLE: Manipulating an ADO database with coding in Delphi

1) Create database file in MS Access (orders.mdb)
Table: tblOrders
Fields: (No = AutoNumber)

2) In Delphi: choose ADOtable under the ADO menu on the component palette
3) Change settings in the Object Inspector: change the Connection String settings by clicking on the ellipse
4) Click on Build?
5) Choose: Microsoft Jet 4.0 OLE DB Provider
6) Click Next >>
7) Select database file (orders.mdb)
8) Erase user name
9) Make sure 'Blank Password' is selected
10) Click OK (on 'Data Link Properties' window)
11) Click OK (on 'ConnectionString' window)
12) Click on ADOTable component: choose Table name for ADOTable (tblOrders)
13) Set ADOTable Active to TRUE
14) Right click on ADOTable and choose Fields Editor
15) Right click and choose ?Add all fields?
16) Set the 'currency' property for the Amount and Total fields to 'true'
17) Add DataSource (link to tblOrders)
17) Add DBGrid and DBNavigator (link to tblOrders)
18) Add other components (SaveDialog, OpenDialog, buttons and edit boxes):

{Delete selected record}
procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOTable1.Delete;
end;

{Add records from Edit boxes}
procedure TForm1.Button2Click(Sender: TObject);
begin
  ADOTable1.Append;
  ADOTable1['Name'] := Edit1.text;
  ADOTable1['Surname'] := Edit2.text;
  ADOTable1['Address'] := Edit3.text;
  ADOTable1['Town'] := Edit4.text;
  ADOTable1['Code'] := Edit5.text;
  ADOTable1['Unit'] := SpinEdit1.Value;
  ADOTable1['Amount'] := StrToFloat(Edit6.text);
  ADOTable1['Total'] := SpinEdit1.Value * StrToInt(Edit6.text);
  ADOTable1.Post;
  Edit1.Clear;
  Edit2.Clear;
  Edit3.Clear;
  Edit4.Clear;
  Edit5.Clear;
  Edit6.Clear;
  SpinEdit1.Value := 0;
end;

{Calculate Total}
procedure TForm1.Button3Click(Sender: TObject);
begin
  ADOTable1.First;
  While not ADOTable1.Eof do
  begin
    ADOTable1.Edit;
    ADOTable1['Total'] := ADOTable1['Unit'] * ADOTable1['Amount'];
    ADOTable1.Next;
  end;
end;

{Import records from text file}
procedure TForm1.Button4Click(Sender: TObject);
var
  fImport : TextFile;
  sTemp, sName, sSurname, sAddress, sTown, sCode : String;
  iCount, iUnit : Integer;
  rAmount : Real;

begin
  If OpenDialog1.Execute then
  begin
    AssignFile(fImport, OpenDialog1.FileName);
    Reset(fImport);
    While not eof(fImport) do
    begin
      Readln(fImport,sTemp);
      iCount := Pos(',',sTemp);
      sName := Copy(sTemp,1,iCount-1);
      Delete(sTemp,1,iCount);

      iCount := Pos(',',sTemp);
      sSurname := Copy(sTemp,1,iCount-1);
      Delete(sTemp,1,iCount);

      iCount := Pos(',',sTemp);
      sAddress := Copy(sTemp,1,iCount-1);
      Delete(sTemp,1,iCount);

      iCount := Pos(',',sTemp);
      sTown := Copy(sTemp,1,iCount-1);
      Delete(sTemp,1,iCount);

      iCount := Pos(',',sTemp);
      sCode := Copy(sTemp,1,iCount-1);
      Delete(sTemp,1,iCount);

      iCount := Pos(',',sTemp);
      iUnit := StrToInt(Copy(sTemp,1,iCount-1));
      Delete(sTemp,1,iCount);

      iCount := length(sTemp);
      rAmount := StrToFloat(Copy(sTemp,1,iCount));
      Delete(sTemp,1,iCount);

      ADOTable1.Append;
      ADOTable1['Name'] := sName;
      ADOTable1['Surname'] := sSurname;
      ADOTable1['Address'] := sAddress;
      ADOTable1['Town'] := sTown;
      ADOTable1['Code'] := sCode;
      ADOTable1['Unit'] := iUnit;
      ADOTable1['Amount'] := rAmount;
      ADOTable1['Total'] := iUnit * rAmount;
      ADOTable1.Post;
    end;
    CloseFile(fImport);
  end;
end;

{Save records to text file}
procedure TForm1.Button5Click(Sender: TObject);
var
  fExport : TextFile;

begin
  If SaveDialog1.Execute then
  begin
    AssignFile(fExport, SaveDialog1.FileName);
    Rewrite(fExport);
    ADOTable1.First;
    While not ADOTable1.Eof do
    begin
      Writeln(fExport, ADOTable1['Name'] + ',' +
      ADOTable1['Surname'] + ',' +
      ADOTable1['Address'] + ',' +
      ADOTable1['Town'] + ',' +
      ADOTable1['Code'] + ',' +
      IntToStr(ADOTable1['Unit']) + ',' +
      FloatToStr(ADOTable1['Amount'])); {Total not saved!}
      ADOTable1.Next;
    end;
  end;
  CloseFile(fExport);
end;
end.

Activities
Using the above coding as reference, add procedures for the following:
1) Display a list of all the names and surnames in a RichEdit. Fields must be separated with tabs (#9)
2) Calculate the total sales and display in a ShowMessage.
3) Change the Total field so that tax of 14% is added to Unit x Amount.
4) Find and replace the Name field as specified by the user. Add two edit boxes (one for the Name to be searched and one for the new name).