Databases in Delphi Grade 11

In Grade 11 you learn how to connect a Delphi program to a Microsoft Access database, display its data on screen, and add, edit or delete records using code — without SQL (SQL comes in Grade 12).

The Big Picture — How the Pieces Connect

Think of connecting to a database like setting up a water supply to a tap:

Understanding Each Component

TADOConnection — The Door to the Database

A TADOConnection is a non-visual Delphi component that establishes and manages the connection between your program and an external database file (such as a Microsoft Access .mdb file), using a connection string that specifies the file's location and the database provider (driver) to use.

ConnectionString  → Build... → Choose "Microsoft Jet 4.0 OLE DB Provider"
                             → Browse to your .mdb file
LoginPrompt       → False   (stops the "Enter password" popup appearing)

TADOTable — One Table from the Database

A database can have many tables (Students, Subjects, Classes…). A TADOTable represents one of those tables. You need one TADOTable for each table you want to work with.

Connection  → select your ADOConnection (dm.conDB)
TableName   → choose the table from the dropdown (e.g. "tblStudents")
Active      → True   (this "opens" the table so data loads)

TDataSource — The Go-Between

This is the part students find confusing. Why do we need it?

A TDataSource is a non-visual Delphi component that links a dataset (such as a TADOTable) to one or more data-aware controls — visual components, such as DBGrid, DBEdit or DBLabel, that are aware of and can display database data. You cannot connect a DBGrid directly to a TADOTable: every data-aware control must connect through a TDataSource.

DataSet → select your TADOTable (dm.tblStudents)

TDBGrid — The Table You See on Screen

The DBGrid is a component you drop onto your main form (not the Data Module). It displays the database records as rows and columns — just like a spreadsheet. It updates automatically as you navigate through records.

DataSource → select your TDataSource (dm.dsStudents)

Step-by-Step Setup

1. Create a Data Module: File → New → Data Module. Save it as DataModule_u.pas.
2. Drop TADOConnection onto the Data Module. Set up the connection string (browse to your .mdb file). Set LoginPrompt = False.
3. Drop TADOTable onto the Data Module. Set Connection to the ADOConnection. Set TableName. Set Active = True.
4. Drop TDataSource onto the Data Module. Set DataSet to the TADOTable.
5. On your main form: add the Data Module name to the uses clause at the top of your unit.
6. Drop TDBGrid onto your main form. Set its DataSource to the DataSource you created in the Data Module.
7. The grid should now show your database records!

Navigating Records

Think of records as pages in a book. These methods let you turn pages:

dm.tblStudents.First;                        // start at record 1
while not dm.tblStudents.Eof do           // keep going until past last
begin
  memOut.Lines.Add(dm.tblStudents['Name']); // do something with this record
  dm.tblStudents.Next;                       // move to next record
end;

Accessing Field Values in Code

Once connected, you can read the value of any field in the current record using this syntax:

variable := DataModuleName.TableName['FieldName'];

sName    := dm.tblStudents['Name'];        // reads the Name field
sSurname := dm.tblStudents['Surname'];    // reads the Surname field
iGrade   := dm.tblStudents['Grade'];      // reads the Grade field

// Display in a label
lblInfo.Caption := dm.tblStudents['Name'] + ' ' + dm.tblStudents['Surname'];

Dataset States

A TADOTable is always sitting in one of a small set of named states, and that state controls what you're allowed to do with the current record. You've actually been relying on these states already — calling Edit or Insert doesn't touch any data by itself, it just switches the dataset into a state where changing data is legal.

Inserting a New Record

Adding a new record is a 3-step process: prepare → fill in values → save.

dm.tblStudents.Insert;                    // Step 1: prepare a blank new record
dm.tblStudents['Name']    := edtName.Text;     // Step 2: fill in the fields
dm.tblStudents['Surname'] := edtSurname.Text;
dm.tblStudents['Grade']   := sedGrade.Value;
dm.tblStudents.Post;                      // Step 3: save to the database

function GetNewStudentNo: Integer;
var
  iLargest : Integer;
begin
  dm.tblStudents.First;
  iLargest := 0;
  while not dm.tblStudents.Eof do
  begin
    if dm.tblStudents['StudentNo'] > iLargest then
      iLargest := dm.tblStudents['StudentNo'];
    dm.tblStudents.Next;
  end;
  Result := iLargest + 1;
end;

Editing an Existing Record

// First navigate to the record you want to change, then:
dm.tblStudents.Edit;                      // Step 1: put the record into edit mode
dm.tblStudents['Grade'] := 12;            // Step 2: change the value
dm.tblStudents.Post;                      // Step 3: save the change

Deleting a Record

// Navigate to the record first, then:
dm.tblStudents.Delete;   // removes the current record permanently

Changing a Field for All Records at Once

Some tasks need to touch every record in a table, not just one — for example handing out 5 bonus marks to every student, or applying a fee discount to a whole grade. This is really the same "loop through all records" pattern used earlier, just with an Edit / Post pair added inside it:

dm.tblStudents.First;
dm.tblStudents.DisableControls;      // stop the DBGrid refreshing on every single record
while not dm.tblStudents.Eof do
begin
  dm.tblStudents.Edit;
  dm.tblStudents['Mark'] := dm.tblStudents['Mark'] + 5;
  dm.tblStudents.Post;
  dm.tblStudents.Next;
end;
dm.tblStudents.First;
dm.tblStudents.EnableControls;       // switch the grid's updates back on

Searching for a Record

To find a specific record, loop through all records and check each one:

dm.tblStudents.First;
while not dm.tblStudents.Eof do
begin
  if dm.tblStudents['Surname'] = edtSearch.Text then
  begin
    ShowMessage('Found: ' + dm.tblStudents['Name']);
    Break;   // stop searching once found
  end;
  dm.tblStudents.Next;
end;

Filtering Records

A TADOTable can also show only the records that match a condition, without writing any SQL. Set Filter to a condition string, then set Filtered to True to apply it.

dm.tblStudents.Filter := 'Grade = 11';         // only Grade 11 records
dm.tblStudents.Filtered := True;               // switch the filter on
...
dm.tblStudents.Filtered := False;              // switch it off again — shows all records

Sorting Records

dm.tblStudents.Sort := 'Surname ASC';         // A to Z by surname
dm.tblStudents.Sort := 'Mark DESC';           // highest mark first
dm.tblStudents.Sort := 'Grade ASC, Surname ASC'; // by grade, then surname

Complete Example — Finding the Highest Mark

var
  iHighest : Integer;
  sTopName : String;
begin
  iHighest := 0;
  sTopName := '';
  dm.tblStudents.First;
  while not dm.tblStudents.Eof do
  begin
    if dm.tblStudents['Mark'] > iHighest then
    begin
      iHighest := dm.tblStudents['Mark'];
      sTopName := dm.tblStudents['Name'];
    end;
    dm.tblStudents.Next;
  end;
  lblResult.Caption := sTopName + ' scored the highest: ' + IntToStr(iHighest);
end;

Editing Records Without Code — Data-Aware Controls

Everything else on this page shows how to insert, edit and delete records using code. Delphi also provides data-aware controls that let a user browse and change records directly on the Form, with no code at all.

TDBNavigator — A Toolbar for the Dataset

A TDBNavigator (from the Data Controls tab) is a data-aware control with a row of buttons for moving through and maintaining a dataset. Set its DataSource property the same way you would for a DBGrid.

DBEdit, DBText and DBCheckBox — Single-Field Controls

Sometimes a full grid is more than you need — you might just want one field of the current record shown somewhere on the Form, such as a name next to a photo. Each of these controls links to exactly one field, using the same two properties:

DataSource → select your TDataSource (dm.dsStudents)
DataField  → choose the field from the dropdown (e.g. "Surname")

Delphi doesn't write a change to the table the instant it's made — it sits in a buffer until something posts it. That happens automatically the moment the user moves to a different record, or you can trigger it sooner yourself, either in code or via a DBNavigator's Post button.

Connecting a Database Dynamically (in Code)

Everything above used components dropped onto a Data Module at design time. You can also build the whole connection at runtime with code — no components needed. This is called a dynamic connection, and it is useful when the database path is only known while the program runs (e.g. the user browses to the file).

Declare the three objects (usually under public in the Data Module, or in the form):

public
  conDB  : TADOConnection;
  tblStudents : TADOTable;
  dsStudents  : TDataSource;

Create and wire them together — build the connection string, point the table at the connection, then link the DataSource:

procedure TdmData.DataModuleCreate(Sender: TObject);
begin
  // 1. Create the connection object
  conDB := TADOConnection.Create(Self);
  conDB.LoginPrompt := False;        // don't ask for a password
  conDB.ConnectionString :=
    'Provider=Microsoft.Jet.OLEDB.4.0;' +
    'Data Source=' + ExtractFilePath(Application.ExeName) + 'Students.mdb;';
  conDB.Connected := True;

  // 2. Create the table and link it to the connection
  tblStudents := TADOTable.Create(Self);
  tblStudents.Connection := conDB;
  tblStudents.TableName  := 'tblStudents';
  tblStudents.Active      := True;

  // 3. Create the DataSource and link it to the table
  dsStudents := TDataSource.Create(Self);
  dsStudents.DataSet := tblStudents;
end;

Finally, link your on-screen TDBGrid to the DataSource. Do this in the main form's OnShow event:

procedure TfrmMain.FormShow(Sender: TObject);
begin
  dbgStudents.DataSource := dmData.dsStudents;
end;