Transcript Chapter 9

Chapter 9:
Introduction to
Working with
Databases in
Visual Basic
Database
Concepts
Relational
Database
The Data
Control
Chapter 9:
Introduction to Working with
Databases in Visual Basic
The Recordset
Object
Finding
Records
Copyright © 2001
by Wiley. All rights
reserved.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
1
Chapter 9:
Introduction to
Working with
Databases in
Visual Basic
Database
Concepts
Relational
Database
The Data
Control
The Recordset
Object
Finding
Records
Copyright © 2001
by Wiley. All rights
reserved.
Database Concepts
• Database: the storage of different types of data in
such a way that the data can be easily manipulated
and retrieved by an end user. A database is
composed of fields, records, and tables.
• Field: a single fact or data. It is the smallest use of
named data that has meaning in a database. Fields
are given field names to identify them.
• Record: A collection of related data that is treated
as a unit. A collection of fields that pertain to a
single person, place or thing.
• Table: A related collection of records, all having the
same fields. Composed of rows and columns.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
2
Chapter 9:
Introduction to
Working with
Databases in
Visual Basic
A Database Table
Database
Concepts
Relational
Database
The Data
Control
The Recordset
Object
Finding
Records
Copyright © 2001
by Wiley. All rights
reserved.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
3
Chapter 9:
Introduction to
Working with
Databases in
Visual Basic
Database
Concepts
Relational
Database
The Data
Control
The Recordset
Object
Finding
Records
Copyright © 2001
by Wiley. All rights
reserved.
Relational Database
• A relational database is composed of
multiple related tables. It is the most
common form of database in use today.
• A primary key is a field that has a unique
value for each record in a table.
• A foreign key is a field that is a primary key
in another table.
• Foreign keys are used to establish the
relationships between the various tables in
the database.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
4
Chapter 9:
Introduction to
Working with
Databases in
Visual Basic
Relational Database
Database
Concepts
Relational
Database
The Data
Control
The Recordset
Object
Finding
Records
Copyright © 2001
by Wiley. All rights
reserved.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
5
Chapter 9:
Introduction to
Working with
Databases in
Visual Basic
Database
Concepts
Relational
Database
The Data
Control
The Recordset
Object
Finding
Records
Copyright © 2001
by Wiley. All rights
reserved.
Database Operations
• The primary use of a database is to enable
the user to obtain information from it by
constructing queries to it.
• For a relational database, these queries are
constructed using SQL (Structured Queried
Language).
• Once found, records can be displayed,
edited, deleted, or added.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
6
Chapter 9:
Introduction to
Working with
Databases in
Visual Basic
Database
Concepts
Relational
Database
The Data
Control
The Recordset
Object
Finding
Records
Copyright © 2001
by Wiley. All rights
reserved.
Advantages of Databases vs. Arrays
• Databases are superior to arrays for the following
reasons:
– A database table can store multiple types of
data in the same table while arrays are
restricted to a single data type.
– Any changes to the database are immediately
saved to disk as they occur.
– A sophisticated database engine can be used
to handle processing.
– Multiple computers can be connected to the
same database.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
7
Chapter 9:
Introduction to
Working with
Databases in
Visual Basic
Database
Concepts
Relational
Database
The Data
Control
The Recordset
Object
Finding
Records
Copyright © 2001
by Wiley. All rights
reserved.
Using VB to Work with Databases
• Visual Basic can be used to work with existing
databases.
• In our case, we will assume a Microsoft Access
database with an .mdb extension.
• Visual Basic can be used to create a user-friendly
front-end to a database.
• The data control (with dat prefix) is essential to
working with databases in VB.
• It uses an internal pointer to point to the current
record.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
8
Chapter 9:
Introduction to
Working with
Databases in
Visual Basic
Database
Concepts
Relational
Database
The Data
Control
The Recordset
Object
Finding
Records
Copyright © 2001
by Wiley. All rights
reserved.
The Data Control
• DatabaseName property must be set to the
pathname of the database.
• RecordSource property must be set to the table of
the database.
• Textboxes and other controls can be bound to a
data control by assigning their DataSource
property to the data control.
• The bound control’s DataField property is set to a
field of the DataSource table.
• The Recordset object represents the records in
the database.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
9
Chapter 9:
Introduction to
Working with
Databases in
Visual Basic
Database
Concepts
Relational
Database
The Data
Control
The Recordset
Object
Finding
Records
Copyright © 2001
by Wiley. All rights
reserved.
RecordSet Methods
• There are numerous useful methods for the
Recordset object:
– MoveFirst - move to first record
– MoveLast - move to last record
– MoveNext - move to next record
– MovePrevious - move to previous record
– AddNew - add a new record (save by moving
to new record or using UpdateRecord method)
– Delete - delete current record
– UpdateControls - make bound controls the
same as database
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
10
Chapter 9:
Introduction to
Working with
Databases in
Visual Basic
Database
Concepts
Relational
Database
The Data
Control
The Recordset
Object
Finding
Records
Copyright © 2001
by Wiley. All rights
reserved.
Recordset Properties
• There are numerous useful properties for the
Recordset object:
– AbsolutePosition - the current position of
pointer
– RecordCount - the number of records in the
Recordset
– BOF (EOF) - True if at Beginning (End) of File
– Bookmark - A unique identification of each
record
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
11
Chapter 9:
Introduction to
Working with
Databases in
Visual Basic
Database
Concepts
Finding Records
• The form of the FindFirst method to find first
occurrence of a matching record is:
datName.Recordset.FindFirst Query string
Relational
Database
The Data
Control
where the Query string is in quotation marks and is
a combination of field name, comparison operator,
and a value. For example:
datMembers.Recordset.FindFirst “Late_Fees > 0”
The Recordset
Object
Finding
Records
Copyright © 2001
by Wiley. All rights
reserved.
• If the NoMatch property is true, no record
matches the query.
• An Until NoMatch loop can be used with the
FindNext method to find all matches.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
12
Chapter 9:
Introduction to
Working with
Databases in
Visual Basic
Database
Concepts
Relational
Database
The Data
Control
The Recordset
Object
Finding
Records
The Field Property and More on
Query Strings
• To find the value of a field of the current
record, use the RecordSet Field property,
e.g., datMembers.Recordset(“Name”)
• A query string can be the combination of
the field name and the comparison
operator with some value--this enables
variable query strings
• Strings in query strings must be enclosed
in apostrophes, e.g.,
“Phone Number = “ & “’” & PhoneNum & “’”
Copyright © 2001
by Wiley. All rights
reserved.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
13