Transcript Database Access - USA School of Computing

ITE 370: Database Access: The ADO Data Control
Database Access: The ADO Data Control
• Database Connectivity Overview
• Recordsets
• The ADO Data Control
– Properties, Methods, Events
• Bound controls
University of South Alabama School of CIS
Last Modified: 7/16/2015
1
ITE 370: Database Access: The ADO Data Control
Database Connectivity-An Overview
COMPANY.MDB
Employees Table
ID:
Payroll Table
Name:
Department Table
Database
any of a variety of
database formats,
spreadsheet, text file
ADO data control
Bound controls
• opens database
• data-aware
• creates a recordset
• provides access to
field in a table
University of South Alabama School of CIS
Last Modified: 7/16/2015
2
ITE 370: Database Access: The ADO Data Control
Recordsets
• Recordset
– A dynamic representation of records coming from one
of two sources: a base table or query results
• Tables are rectangular collections of data
– Rows and columns or, equivalently, records and fields
• Query results
– A temporary collection of records satisfying the query
criteria
– Queries may be constructed from multiple tables
University of South Alabama School of CIS
Last Modified: 7/16/2015
3
ITE 370: Database Access: The ADO Data Control
Principles of Recordsets
• Creating a recordset in VB gives no automatic access to
the data
– No visible results
– No automatic manipulation
• Programmer must provide access through interface and
code
• Only one record is current at any time
• Native database format is Microsoft Access
• Other database formats are available through ODBC (open
database connectivity)
University of South Alabama School of CIS
Last Modified: 7/16/2015
4
ITE 370: Database Access: The ADO Data Control
The ADO Data Control
• The link between VB and a data source
– gets “attached” to a data source
– used with “bound” controls to manipulate information
in a database
• By setting the appropriate properties, the data control
will automatically:
– open a database
– create a recordset
• Clicking on a data control changes the current record
– buttons move to first, previous, next and last records,
respectively
University of South Alabama School of CIS
Last Modified: 7/16/2015
5
ITE 370: Database Access: The ADO Data Control
ADO Data Control: Overview
• ADO - ActiveX Data Objects
• Language-independent
• Combines functionality of the DAO and RDO data
controls
– connections to native Microsoft Access databases
– connections to databases by other providers
• Must be added to project
– Project | Components | Microsoft ADO Data Control
6.0 (OLEDB)
University of South Alabama School of CIS
Last Modified: 7/16/2015
6
ITE 370: Database Access: The ADO Data Control
ADO Data Control Properties
• Properties
– ConnectionString: contains information used to
establish a live link to the data source, such as:
• provider, database name and location, server name
• username and password, & security information
– RecordSource: Table name or query text
– CommandType: Is RecordSource a table name, query
text, or something else?
– Orientation: Horizontal or Vertical
– Visible & Enabled
University of South Alabama School of CIS
Last Modified: 7/16/2015
7
ITE 370: Database Access: The ADO Data Control
Refresh Method
• Refresh Method (of the ADO data control)
– closes and rebuilds underlying recordset
– old recordset is closed; new connection is established
– changing RecordSource at run-time does nothing; must
do a Refresh to activate change
• Example:
adoEmployee.RecordSource = “Select * From Employee”
adoEmployee.Refresh
University of South Alabama School of CIS
Last Modified: 7/16/2015
8
ITE 370: Database Access: The ADO Data Control
Data Control Events
• Two data control events enable action when current
record changes
– WillMove event fires before the current record
changes
– MoveComplete event fires after the current record
changes
• Recordset operations that may cause current record
to change:
Open, Move, MoveFirst, MoveLast, MoveNext,
MovePrevious, Bookmark, AddNew, Delete,
Requery, and Resync
University of South Alabama School of CIS
Last Modified: 7/16/2015
9
ITE 370: Database Access: The ADO Data Control
Bound Controls
• bind: to attach, or connect, a data-aware control to a data
control and, thus, a database
• Some controls that may be bound:
text box, label, list box, combo box, check box,
picture box, image control, masked edit box
• Bindable controls have two data properties:
– DataSource: name of data control
– DataField: field name in data control’s recordset the
bound control will display
University of South Alabama School of CIS
Last Modified: 7/16/2015
10
ITE 370: Database Access: The ADO Data Control
Bound Controls (cont.)
• A bound control is data-aware, or “live”
– Displays data in a field of the recordset created by the
data control
– Contents change when current record changes
– Edits made in bound controls will be updated to the
database unless your code prevents this
University of South Alabama School of CIS
Last Modified: 7/16/2015
11
ITE 370: Database Access: The ADO Data Control
Bound Controls Example
Jones
Alberts
Bechtold
Edwards
Jones
Michaels
Wysnewski
Yancy
University of South Alabama School of CIS
382
123
654
983
382
947
725
039
True
True
False
True
False
False
True
Last Modified: 7/16/2015
12
ITE 370: Database Access: The ADO Data Control
Common Data Control Errors
• Wrong CommandType for RecordSource
– i.e. using a query as RecordSource for an adCmdTable
• Forgetting to bind a control
– Symptom: control does not change when navigating
• Typo on RecordSource or DataField
• Failure to use Refresh to build or rebuild recordset
University of South Alabama School of CIS
Last Modified: 7/16/2015
13