Transcript Programming in Visual Basic 6.0 Update Edition

Programming in Visual Basic 6.0
Update Edition
Chapter 11
Accessing Database Files
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
2
Database Formats Supported
•
•
•
•
•
•
Microsoft Access
dBASE III, IV, V
Excel
FoxPro
Lotus
Paradox
McGraw-Hill/Irwin
• SQL Server
• Oracle
• DB2
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
3
Database Terminology
• Databases
• Tables
– Records
• Fields
• Primary Key
• Foreign Key
• Relationships
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
4
Relational Databases
• Database includes
– Tables - collection of related data
– Queries - SQL designed to select data from tables
• Table
– Record - row of related data for one instance
– Field - column of specific data element
– Keys
• Primary - uniquely identifies a record
• Foreign - links record to related record in related table
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
5
Creating New Access Databases
• Use Microsoft Access
• Use VB's Visual Data Manager
Add-In
– Add-Ins menu, Visual Data Manager
– VisData Application capabilities
• Create a new file
• Modify an existing file
– VisData Includes
• Query Builder Utility
• DataForm Designer
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
6
ADO
• Active Data Object
• Microsoft's latest database object model
– There have been many in previous releases,
some still compatible with
VB 6, some not
– You should use ADO rather than the older
models (DAO, RDO)
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
7
ADO Data Control Connections (ADODC)
• Add active data control to toolbox
– Project, Components, Microsoft ADO Data Control 6.0
(OLEDB)
• Add data control to form
• Set properties
– Prefix - ado
– Custom Property Pages - Build Connect String
• Connect data aware controls to data control
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
8
Data Aware/Data Bound Controls
• Controls bound to database fields
• Linked to the database using ADODC
• Intrinsic Controls
– labels
– text boxes
– check boxes
– images
– picture boxes
McGraw-Hill/Irwin
– list boxes
– combo boxes
– data-bound list boxes
– data-bound combo boxes
– data-bound grids
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
9
ADODC Properties (pp 447-451)
• Connection String
– OLE DB Provider (ex: Jet 3.5.1 or Jet 4.0) (p 99)
– Database path and filename
– Optional userid and password
• RecordSource
– Name of Table, Stored Procedure, or SQL statement
– Command type (ex: adCmdTable, adCmdStoredProc,
adCmdText)
• Access Permissions (ex: read, readwrite)
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
10
Linking Data Aware Controls
• Intrinsic controls
– DataSource ===> ADODC
– DataField ===> field in source
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
11
Navigating the Database
• Use the movement buttons on the ADODC
to move through the database records
OR
• Set the ADODC's Visible property to False
and write code for command buttons to
move through the records using the
Recordset object
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
12
Recordset
• Object that contains a set of records from
ADO data control
– RecordSource property of the data control
determines the recordset
• Recordset has properties and methods
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
13
Using Recordset Object
• Object in ADODC hierarchy
• Has methods and properties to use for
record movement and to determine EOF
and BOF
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
14
Recordset Object - Move Methods Code Examples
adoBooks.Recordset.MoveFirst
adoBooks.Recordset.MoveLast
adoBooks.Recordset.MoveNext
adoBooks.Recordset.MovePrevious
adoBooks.Recordset.EOF
adoBooks.Recordset.BOF
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
15
EOF and Move Next
• Be careful not to move past EOF
Private Sub cmdNext_Click( )
With adoBooks.Recordset
.MoveNext
If .EOF then
.MoveFirst
End IF
End With
End Sub
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
16
BOF and Move Previous
• Be careful not to move past BOF
Private Sub cmdPrevious_Click( )
With adoBooks.Recordset
.MovePrevious
If .BOF then
.MoveLast
End IF
End With
End Sub
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
17
Lookup Table for a Field
• Use a combo box to display a list of acceptable
values for a field
• Populate the combo 1 of 2 ways
– Enter the list of values in the List property at design
time
– Use the AddItem method in Form_Load at run time
• When the user selects an item from the combo's
list update the database field
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
18
Updating a Database File
• Modify existing records
• Add records
• Delete records
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
19
Modifying Records
• With bound controls, saving changes is
practically automatic
(if ADODC's ReadOnly=False)
• Changes to record saved when user moves
to another record (like Access)
• Explicitly save changes in code using the
Recordset's Update method
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
20
Adding Records Automatically
• ADODC
– Set EOFAction property to adDoAddNew in
Properties Window
• At EOF, when user clicks next record
– VB automatically begins Add operation
– VB clears bound controls
– When user clicks arrow button, the Update
method is automatically executed and the new
record is written to the file
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
21
Disadvantages of Using ADODC for Add
• No opportunity for data validation of field
values
• If user enters invalid data you will not be
able to easily trap the error and the program
will terminate
• If user decides not to add the record there is
no way for them to cancel
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
22
Adding Records Manually
• AddNew method clears all bound controls
(New Record cmd button)
adoBooks.Recordset.AddNew
• After the user enters the data, perform data
validation, write to file using Update
method (Save Record cmd button)
adoBooks.Recordset.Update
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
23
Typical Add Record Problems
• Primary Key Field must be entered or a
runtime error occurs
• Make sure you have provided a textbox for
the user to enter the Primary Key or create
the Key programatically
• Use data validation to verify the validity of
the primary key and all other data
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
24
Protecting an Add Record (P468-471)
• When the user clicks the New Record/Add
Record cmd button
– Enable the Save cmd button
– Enable the Cancel cmd button or just change
the caption of Add to Cancel and use code (If
structure or Select Case) to check the caption
and decide what to do
– Disable all other navigational and operational
buttons
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
25
Deleting Records
• Delete method deletes the current record
• Always follow with a Move method since
the current record is no longer valid after it
has been deleted!
With adoBooks.Recordset
.Delete
.MoveNext
End With
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
26
Delete Considerations
• What if the record deleted is the last record?
• What if the record deleted was the only
record?
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
27
Better Delete Code
With adoBooks.Recordset
.Delete
.MoveNext
If .EOF Then
.MovePrevious
If .BOF Then
MsgBox "The recordset is empty"
End If
End If
End With
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
28
Tips For Preventing User Errors
• Set TextBox's MaxLength equal to database
field length
• Use TextBox's Lock property when you don't
want the user to change data, like primary key
when not adding a record
• Once an Add has begun disable all navigation
buttons (see code example pages 468-471)
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
29
Typical Database Errors
• Entering data that violates rules
– Empty key field
– Duplicate key field
– Violates referential integrity
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
30
Additional Special Topics
• Connection String Considerations
• Opening a Database
• Closing a Database
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
31
Connection String Considerations
• The ADODC's ConnectionString can be set using
the custom property pages
– If so ==> the connection string contains a "hard-coded"
path to the database file
– If the database file is moved, the ConnectionString is
no longer valid
• Overcome this problem by using the App.Path
object as we did for Sequential and Random files
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
32
ConnectionString Code Example
adoBooks.ConnectionString=
"Provider=Microsoft.Jet.OLEDB.3.51;
Persist Security Info=False;
Data Source=" & App.Path & "\Biblio.mdb;
Mode=Read"
App.Path points to the folder/directory in which
the VB Project is saved. The database file must
be saved in the same folder for this code to work.
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
33
Open or Reopen a Database (ADODC)
• Use the Refresh method of the ADO control
to open or reopen a database
adoBooks.Refresh
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
34
Ex: Writing Code to Open a Database using ALL
Custom Properties of the ADODC
Private Sub Form_Load( )
With adoBooks
.ConnectionString="Provider=Microsoft.Jet.OLEDB.3.51;" & _
"Persist Security Info=False;" & _
"Data Source=" & App.Path & "\AVB.mdb; " & _
"Mode=Read"
.CommandType=adCmdTable
.RecordSource="Patient"
.Refresh
End With
Note: If you type the ConnectionString on one line in the code
window, the & and _ are not needed!
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.
Programming in Visual Basic 6.0 Update Edition
35
Close a Database (ADODC)
• Use the Close method of the ADO control
to close a database
• Usually place this code in Form_Unload of
the form that opened the database or the
main form's Unload event
adoBooks.Recordset.Close
McGraw-Hill/Irwin
© 2002 The McGraw-Hill Companies, Inc. All rights reserved.