Transcript VBFC7 - University of Wolverhampton

CP2030
Visual Basic For C++ Programmers

Week 7
Databases Concepts

Using the Visual Basic Data Manager
–
–
–

Creating a Table
Creating Fields
Adding Indexes
Accessing the Database within V.B.
–
–
Using the Data Control
Data Aware Controls
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 1
What is a Database ?

A Database is a store of information

Databases are divided into one or more tables

Each table consists of a series of records

Each record contains a number of distinct fields
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 2
What is a Field ?
Fields are a store for an individual item of data
e.g. they may contain someones name or their age
 For example a name field could contain the name
“Jeremy”
 Or an age field could contain the number 18

Name field
Age field

“Jeremy”
18
Each field contains a specific data type e.g. a string or
a number
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 3
What is a Record ?
A Record is a collection of fields , which describe
some entity
 For example a book can be described as having a

–
–
–
–
Title
Author
ISBN
Publisher
Book Record
Title
Author
ISBN
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Publisher
Component 7, Slide 4
What is a Table ?
A table is a collection of related records
 For example all the records for books in a library
would make up a table

Table of books
Title
Author
Oliver
Charles Dickens 012-9877-5643
McGraw Hill
VB Design
John Smith
Prentice Hall
:
:
Pasta Fasta
ISBN
:
:
Gill Jones
Publisher
230-7692-1287
:
:
890-6023-5028
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
:
:
Penguin
Component 7, Slide 5
Multiple Tables
Databases are made up of one or more tables
 Databases should be divided into tables containing a
related type of data e.g.
A company could have a staff database, split into
tables as below for different departments to access

Training Courses
(Training Dep’t)
Salaries
(Finance)
Personal
Details
(Personnel)
Company
Staff Database
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 6
What is a Query ?

Splitting databases into tables of related data allows
quicker access,
–

for most searches only one smaller set of data (a table) needs
to be searched, not the whole database
When we need data from more than one table or
need data of one type from a table
–
e.g. all personnel with the surname ‘Jones’
These groups of data are obtained by running an SQL
statement to produce a subset of the data (a ‘Query’)
 Databases provide ways of running queries to produce
a subset of data from one or more tables

–
e.g. All staff called ‘Jones’ who have been trained in ‘First
Aid’
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 7
Visual Basic Links to Database
- Methods

Visual Basic will link to most common databases
–
–
–

e.g. MS Access
Foxpro,dBase
ODBC structures
Two Methods
– Data Control - (Level 2 module)
 Control is placed on to a form and a link is then set to a
table or query
– Code to Create a Recordset - (Level 3 - module)
 The code specifies the link and set of data from a table or
query
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 8
Visual Data Manager

Allows you to create/modify a database from within
V.B

Can create/modify tables and queries

Can insert/amend data within tables
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 9
Database to be created:

Create database “Students” with the following tables
–

Table Students:
–
–
–

Table Module
–

Field Name
StudentNo
Surname
Forename
DOB
Type
Text
Text
Text
Date
Size
7
20
20
ModCode
ModName
Text
Text
6
20
Text
Text
Integer
20
6
Table StudOnMod StudentNo
–
–
ModCode
Grade
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 10
VisData Manager - Starting it!
VB6 - Add_Ins menu - select Visual Data Manager
 Create a new database…..

CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 11
VisData Manager - creating a new
Database

Enter database name
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 12
VisData manager - Create new Table
Table designer window etc appears
 To create a table - right click on window

CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 13
VisData Manager - New table
(design mode)

Enter Table name

To add fields to
this table click the
“Add Field”
button
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 14
VisData manager - Adding Fields to
Table

Enter
–
–
–
–
field name,
type,
length
etc

When field
complete click
“OK” to save it and
enter next field

Click “Close” to
return to Table
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 15
VisData Manager - Adding Indexes

Table Structure
form shows fields

No primary key
field yet defined

Click “Add
Index” to create
indexes
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 16
VisData Manager - Add Index

Give name and
select field(s) to be
indexed

Note Primary key
tickbox

Can make several
indexes

Click “Close” when
done.
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 17
VisData Manager Tables & Indexes complete

Completed Table,
including index.

When ready click “Build the
Table” to append
the new table to
the current
database.
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 18
VisData Manager - Tables built

The tables,
their fields,
properties etc
are shown in
the Table
window
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 19
VisData Manager - Adding Data (1)

Right-click on a table
name to open it for
adding data etc.
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 20
VisData Manager - Adding data(2)

“Dynaset” window opens - click “Add” to enter new
data
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 21
VisData Manager - Adding data (3)

Enter data, then click “Update” to save it
Note:
Dates may be
expected in U.S.
format (mm/dd/yy)
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 22
VisData Manager - Creating a Query

On VisData windows open “Query Builder”
– Right-click on Database Window
or
– Select
“Utility”,
“Query Builder”
from menu

Fill in
“Query Builder”
window
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 23
VisData Manager - Creating a Form

Select
“Utility”,
“Data Form Designer”
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 24
VisData Manager - Summary

Visual Data Manager (Add-Ins Visual Data Manager…)
can be used to create databases, tables, fields

Can be used to
enter, update,
delete, find, etc
data in tables

So - how do we access the data from within our V.B.
program?
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 25
Visual Basic Links to Database
- Recordset
A recordset is a set of data from a database table or
query to which Visual Basic links
 A recordset can be thought of as a two dimensional
array of data. where the rows are records and columns
are fields

Data Controls create a recordset, but this is invisible to
the programmer.
 Recordsets can also be created using code

CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 26
Visual Basic Links to Database
There are three types of recordsets which can be
created using code
 Table

–
–

Dynaset
–
–

Copy of database table
Read and write access, slow
Query on table / tables
Read and write access, faster
Snapshot
–
–
Query on table / tables
Read access only, fastest
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 27
Visual Basic Links to Database
- Table
The table is the easiest to create
 It is a complete copy of all of the data in the original
database’s table
 If a table is large the recordset will be large creating
problems in

–
–

access speed and
quantity of random access memory
A table recordset allows data to be both read and
written back to the original table
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 28
Visual Basic Links to Database
- Dynaset
Dynasets are based on queries on one or more tables
 It creates a recordset which is a sub-set of the original
database tables data
 As Dynasets are a sub-set of data they are normally
smaller, hence easier to handle
 Dynasets, like tables, allow data to be read and
written back to the original table or tables

CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 29
Visual Basic Links to Database
- Snapshot
Snapshots, like Dynasets, are based on queries on one
or more tables
 They also create a recordset which is a sub-set of the
original database tables data
 Unlike tables and dynasets they only allow data to be
read from the original tables - not written to tables
 As Snapshots are read-nly they are faster still than
Dynasets
 Being read-nly they stop the accidental overwriting of
data

CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 30
Visual Basic Links to Database
- Example
Database
Recordset Type
Table
TelephoneDirectory
Fields may include
- Name
- Department
- Extension
Dynaset
Snapshot
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
VB Interface
(Admin Access)
Full access rights
to all data
(Record Owner)
Access only own
record for mods
(Other)
Access to read
records only
Component 7, Slide 31
Data Control

The Data Control forms a link to external databases,
such as the ones described under the visual data
manager.
VB Application
Database
Data Control
Data Aware Controls
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Tables
Queries
Component 7, Slide 32
Data Control

Below are the various Data Control items
Data Control
Properties
Control
on form
Icon on
Toolbar
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 33
Data Control Properties - Connect
The connect property of a data control
identifies the format of the database which
is being opened
 For example, a data control’s connect
property setting to open a Paradox database
would be:

–
Data1.Connect = “Paradox;"
Connect properties for other databases
includes DBase and Foxpro
 Note. When using a Microsoft Access
database, leave this property blank as this is
the default.

CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 34
Data Control Properties DatabaseName

The value of the DatabaseName property of a data
control is the filespec of the file containing the
database
–
e.g.
‘Students.mdb’
is our Access Database
By clicking the ellipsis (...), you can search for the
filespec
 This may contain the path as well as the filename,
although care should be taken if your program is
installed in a different sub-directory as this property
would need changing

CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 35
Data Control Properties RecordSource
The value of the RecordSource property of a data
control is a table or query in the database
determined by the DatabaseName property.
 The value can also be an SQL statement used to
specify a query (virtual table)
 Double click on the RecordSource property to scroll
through the available tables
 We’ll look at inserting queries in a subsequent lecture

CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 36
Data Control - Functionality
When a data control is linked to it creates a set of
records called a ‘dynaset’
 The dynaset allows adding, modifying and deletion
of records from the underlying table / tables

Data Controls Caption
Move to
Start
Move Forward
one record
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Move Back
one record
Move to
End
Component 7, Slide 37
Data Aware Controls
Data aware controls are controls which can be bound
to fields within the linked table / query to allow the
display / modification of their values
 There are five data aware controls these are

–
–
–
–
–
Picture boxes
Text boxes
Labels
Images
Check boxes
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 38
Data Aware Control Properties DataSource
To bind a data-aware control to a data
control at design time, set the value of the
DataSource property of the data-aware
control to the name of the data control
 e.g. DataSource = Data1
 Double click to view available data controls

CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 39
Data Aware Control Properties DataField
After the DataSource property of the data-aware
control has been set to bind the control to a data
control, the DataField property is set to a field of the
table accessed by the data control
 e.g. DataField = “StudentNo”
 Clicking the arrow in the property box gives a list of
available fields

CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 40
Code with Data Controls

The following are code methods which can be used
with a ‘Recordset’ (set of records)
MoveFirst, MoveLast, MoveNext, MovePrevious
Edit
AddNew
UpDate
Delete
FindFirst, FindLast, FindNext, FindPrevious
e.g. Data1.Recordset.Delete

Note. the Recordset property is a reference to the
underlying Dynaset object
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 41
Data Controls - Moving

MoveFirst, MoveLast, MoveNext and MovePrevious do the same as the 4 arrows of the Data Control
Code to do these functions is e.g.
Data1.Recordset.MoveNext
Data1.Recordset.MovePrevious
‘Move to next record
‘Move to previous record
Data1.Recordset.MoveFirst
Data1.Recordset.MoveLast
‘Move to first record
‘MOve to last record
Data Controls
Name
Recordset
Property
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 42
Data Controls - Update
Update - causes the values in data aware controls to
be used to update the recordset
 Can be invoked in two ways

i. Using any of the data control’s arrows causes an
update
ii. Code can be used to initiate the update
e.g. Data1.Recordset.Update
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 43
Data Controls - Editing Records
Edit - can be undertaken directly in data aware
controls such as text boxes,
 An update must be invoked to cause the edited values
to be saved

e.g. Typical code to change a value:
Data1.RecordSet.Edit
Data1.RecordSet.Fields(“Surname”) = “Beardsmore”
Data1.RecordSet.Update
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 44
Data Controls - New Records
AddNew - This clears the data buffer (data aware
controls) ready for a new record to be added to the
database
 An Update needs to be invoked to store the new
values into the database
 e.g.

Data1.Recordset.AddNew
Text1.Text = “Watson”
‘ Text1 and Text 2 are data
Text2.Text = “Andrew”
‘ aware controls
Data1.Recordset.Update
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 45
Data Controls - Deleting Records
Delete - This deletes the current record in the buffer
 The deleted record remains current but is invalid, any
references to it will invoke an error

e.g.
Data1.Recordset.Delete
Data1.Recordset.MoveNext
If Data1.Recordset.EOF Then Data1.Recordset.MovePrevious
–

It is normally a good idea to confirm the deletion with
the user first i.e. by displaying a message box
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 46
Databases in V.B. - Summary
Range of databases “supported” by Visual Basic
 Creation of a database using Data Manager
 Access of database using standard controls

–
–

Data Control
Data Aware Controls
Accessing a database using Code.
–
Recordset methods
CP2030 Visual Basic for C++ Programmers, ‘The VB Team’ Copyright © University of Wolverhampton
Component 7, Slide 47