DELPHI: Databases HOW TO...
 

1. SET UP ADO TABLE
1.1 Create database file in MS Access
1.2 In Delphi: choose ADOTable under the ADO menu on the component palette
1.3 Change settings in the Object Inspector: set the Connection String settings by clicking on the ellipse
1.4 Click on Build?
1.5 Choose: Microsoft Jet 4.0 OLE DB Provider
1.6 Click Next >>
1.7 Select database file (mdb file)
1.8 Erase user name (?Admin?)
1.9 Make sure 'Blank Password' is selected
1.10 Click OK (on 'Data Link Properties' window)
1.11 Click OK (on 'ConnectionString' window)
1.12 Click on ADOTable component: choose Table name for ADOTable
1.13 Set ADOTable Active to TRUE
1.14 Add DataSource (link to table)
1.15 Add DBGrid and DBNavigator (link to table)


2. ADD RECORD TO TABLE USING CODING
2.1 A new record can be added using set data:

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOTable1.Append;
  ADOTable1['Name'] := 'John';
  ADOTable1['Surname'] := 'Doe';
  ADOTable1.Post;
end;

2.2 A new record can be added from an edit box:

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOTable1.Append;
  ADOTable1['Name'] := Edit1.Text;
  ADOTable1['Surname'] := Edit2.Text;
  ADOTable1.Post;
  Edit1.Clear;
  Edit2.Clear;
end;


3. SEARCH FOR A RECORD IN A TABLE

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOTable1.First;
  while not ADOTable1.Eof do
  begin
    if ADOTable1['Name'] = Edit1.Text then
    begin
      ShowMessage('This record is present');
      Exit;
    end
    else
    ADOTable1.Next;
  end;
end;


4. SEARCH AND REPLACE FIELDS WITH SET DATA
procedure TForm1.Button2Click(Sender: TObject);
begin
  ADOTable1.First;
  while not ADOTable1.Eof do
  begin
    if ADOTable1['Name'] = Edit1.Text then
    begin
      ADOTable1.Edit;
      ADOTable1['Name'] := Edit2.Text;
    end;
    ADOTable1.Next;
  end;
end;


5. DO A CALCULATION IN A FIELD USING DATA FROM EXISTING FIELDS

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


6. DELETE RECORD MEETING SEARCH CRITERIA

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOTable1.First;
  while not ADOTable1.Eof do
  begin
    if ADOTable1['Name'] = Edit1.Text then
    begin
      ADOTable1.Delete;
      Exit;
    end
    else
      ADOTable1.Next;
  end;
end;


7. DELETE RECORD CURRENTLY IN FOCUS
7.1 Click on record then click on Button1:

procedure TForm1.Button3Click(Sender: TObject);
begin
  ADOTable1.Delete;
end;


8. FILTER RESULTS
8.1 Filter according to exact search criteria (e.g. Name = 'John'):

procedure TForm1.Button3Click(Sender: TObject);
begin
ADOTable1.Filter := 'Name = ' + '''' + Edit1.Text + '''';
ADOTable1.Filtered := True;
end;

8.2 Filter according to similar search criteria (e.g. Name LIKE 'Jo%'):

procedure TForm1.Button3Click(Sender: TObject);
begin
  ADOTable1.Filter := 'Name LIKE ' + '''' + Edit1.Text + '%''';
  ADOTable1.Filtered := True;
end;

8.3 Filter according to number value:

procedure TForm1.Button3Click(Sender: TObject);
begin
  ADOTable1.Filter := 'Amount > ' + Edit1.Text;
  ADOTable1.Filtered := True;
end;


9. DELETE ALL RECORDS

procedure TForm1.Button3Click(Sender: TObject);
begin
  ADOTable1.First;
  while not ADOTable1.Eof do
  begin
    if ADOTable1['Name'] <> '' then
      ADOTable1.Delete
    else
      ADOTable1.Next;
  end;
end;


10. COUNT RECORDS

procedure TForm1.Button3Click(Sender: TObject);
var
  iCount : Integer;
begin
  iCount := 0;
  ADOTable1.First;
  while not ADOTable1.Eof do
  begin
    if ADOTable1['Name'] <> '' then
    iCount := iCount + 1;
    ADOTable1.Next;
   end;
   Label1.Caption := IntToStr(iCount);
end;


11. QUERIES
11.1 Set up query
11.1.1 Create database file in MS Access
11.1.2 In Delphi: choose ADOQuery under the ADO menu on the component palette
11.1.3 Change settings in Object Inspector: set Connection String settings by clicking on the ellipse
11.1.4 Click on Build?
11.1.5 Choose: Microsoft Jet 4.0 OLE DB Provider
11.1.6 Click Next >>
11.1.7 Select database file (mdb file)
11.1.8 Erase user name (?Admin?)
11.1.9 Make sure 'Blank Password' is selected
11.1.10 Click OK (on 'Data Link Properties' window)
11.1.11 Click OK (on 'ConnectionString' window)


11.2 Set Query in Object Inspector
- Click on ellipse next to (TStrings) for SQL property of the ADOQuery
- Type in SQL code (see below)
- Set Active property to True

11.2.1 Show all fields and all records
  Select * from tblTableName;

11.2.2 Show certain field(s) and all records
  Select Name, Surname from tblTableName;

11.2.3 Show all fields for records meeting certain exact criteria
  Select * from tblTableName where Name = "John ";

11.2.4 Show all fields for records meeting similar criteria
  Select * from tblTableName where Name LIKE "Jo% ";

11.2.5 Show all fields and all records sorted according to a field (ascending)
  Select * from tblTableName order by Name;

11.2.6 Show all fields and all records sorted according to a field (descending)
  Select * from tblTableName order by Name DESC;

11.2.7 Show all fields and all records within a set range (Unit is an Integer field)
  Select * from tblTableName where Unit between 1 and 6;


11.3 Set Query with coding

procedure TForm1.Button3Click(Sender: TObject);
begin
  with ADOQuery1 do
  begin
    Active := false;
    SQL.Clear;
    SQL.Add('Select * from tblTableName;');
    Active := true;
  end;
end;

SQL coding added to ?SQL.Add? statement.


11.4 Change data (Amount field becomes 10) according to a condition (Unit equals to 4)

procedure TForm1.Button3Click(Sender: TObject);
begin
  with ADOQuery1 do
  begin
    Active := false;
    SQL.Clear;
    SQL.Add('Update tblTableName');
    SQL.Add('set Amount = 10 where Unit = 4');
    ExecSQL
  end;
  ADOTable1.Refresh;
end;


11.5 Filter according to input from an edit box

procedure TForm1.Button3Click(Sender: TObject);
begin
  with ADOQuery1 do
  begin
    SQL.Clear;
    SQL.Add('Select *');
    SQL.Add('from tblTableName');
    SQL.Add('where Name = "' + Edit1.Text + '"');
    Open;
  end;
end;


12. SETUP A QUICKREPORT
12.1 Activate QuickReport (QReport) on component palette (Delphi 7)
12.1.1 Choose on drop down menu: Component > Install Packages
12.1.2 Click on Add
12.1.3 Select file ?dclqrt70.bpl? under C:\Program Files\Borland\Delphi7\Bin
12.1.4 Click Open
12.1.5 Click OK

12.2 Create QuickReport
12.2.1 Choose on drop down menu: File > New > Other
12.2.2 Select Report and press OK
12.2.3 Press F12 (to go to unit)
12.2.4 Add unit (on which the table is located) to the Uses statement of the QuickReport unit.
E.g. uses Windows, SysUtils, Messages, Classes, Graphics, Controls,
StdCtrls, ExtCtrls, Forms, QuickRpt, QRCtrls, Unit1;
12.2.5 Press 12 (to go back to QuickReport form)
12.2.6 Click on QuickReport
12.2.7 Set DataSet property to ADOTable
12.2.8 Double click on QuickReport ? select all bands:

12.2.9 Click on OK
12.2.10 Select items on the QReport tab on the component palette
12.2.11 Use QRLabel for text to be added to the report and use QRDBText to display fields from the database.
12.2.12 With the QRDBText select the ADOTable in the DataSet property.
12.2.13 Choose the relevant field in the DataField property.
12.2.14 Preview report by right clicking and selecting Preview OR use coding:

procedure TForm1.Button4Click(Sender: TObject);
begin
  QuickReport2.Preview;
end;

12.2.15 Click on Yes to add reference to USES list