Transcript Chapter 10

Chapter 10:
Advanced
Database
Operations
Revising
Vintage Videos
Setting
RecordSource
at run time
DBGrid Control
DBList and
DBCombo
Controls
Chapter 10:
Advanced Database
Operations
SQL Queries
The Data View
Window
Copyright © 2001
by Wiley. All rights
reserved.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
1
Chapter 10:
Advanced
Database
Operations
Revising Vintage Videos
Revising
Vintage Videos
1.
2.
Setting
RecordSource
at run time
3.
DBGrid Control
4.
DBList and
DBCombo
Controls
5.
6.
SQL Queries
The Data View
Window
Copyright © 2001
by Wiley. All rights
reserved.
7.
Search and display based on a member’s phone number.
Browse the member information form and add new
members or delete existing members.
Display price based on ID number for a video on a Rental
form.
Add ID number, date and phone number to database for
each video that is rented.
Search for and display videos with a particular name or
partial name to determine if they are in stock.
Call the customers who have overdue videos and request
they be returned.
Check videos back in when they are returned, update the
availability status of the video and, if late, calculate late fee.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
2
Chapter 10:
Advanced
Database
Operations
Plan for Revising Vintage Videos
Revising
Vintage Videos
Setting
RecordSource
at run time
DBGrid Control
DBList and
DBCombo
Controls
SQL Queries
The Data View
Window
Copyright © 2001
by Wiley. All rights
reserved.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
3
Chapter 10:
Advanced
Database
Operations
Adding Forms to the Project
Revising
Vintage Videos
Setting
RecordSource
at run time
DBGrid Control
DBList and
DBCombo
Controls
SQL Queries
• In design mode, use the Project|Add File menu
option to add a file to a project.
• As each form is added, you should immediately use
Save frmname.frm as... to save the form.
• Once all forms have been added, the project must
be saved in order for the forms to be made a
permanent part of the project.
The Data View
Window
Copyright © 2001
by Wiley. All rights
reserved.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
4
Chapter 10:
Advanced
Database
Operations
Revising
Vintage Videos
Validating Video Input
•
Before displaying video name and price, several
items should be validated:
1.
2.
Setting
RecordSource
at run time
3.
DBGrid Control
•
DBList and
DBCombo
Controls
SQL Queries
The Data View
Window
Copyright © 2001
by Wiley. All rights
reserved.
•
that the txtVideoID text box is not blank.
that the video ID number matches a video on the
database.
that the video is not already rented.
Since all validations should take place before a
certain command button is clicked, the
GotFocus event for the button may be used for
validation code.
An alternative is the Validate event. The Validate
event occurs before the focus shifts to a (second)
control that has its CausesValidation property
set to True
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
5
Chapter 10:
Advanced
Database
Operations
Revising
Vintage Videos
Setting
RecordSource
at run time
DBGrid Control
DBList and
DBCombo
Controls
SQL Queries
The Data View
Window
Copyright © 2001
by Wiley. All rights
reserved.
Code to Search for Video by ID Number
datVideos.RecordSource = "Videos"
datVideos.Refresh
strTarget = "ID_Num = " & txtVideoID.Text
datVideos.Recordset.FindFirst strTarget
If datVideos.Recordset.NoMatch Then
MsgBox "Video does not exist", vbCritical, _
"Video Status"
txtVideoID = ""
txtVideoID.SetFocus
End If
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
6
Chapter 10:
Advanced
Database
Operations
Revising
Vintage Videos
Setting
RecordSource
at run time
DBGrid Control
DBList and
DBCombo
Controls
SQL Queries
The Data View
Window
Copyright © 2001
by Wiley. All rights
reserved.
Code to Display Video
Private Sub cmdCalc_GotFocus()
Dim strTarget As String
If txtVideoID.Text = "" Then
MsgBox "You must enter a valid ID number."
txtVideoID.SetFocus
Exit Sub
Else
datVideos.RecordSource = "Videos"
datVideos.Refresh
strTarget = "ID_Num = " & txtVideoID.Text
datVideos.Recordset.FindFirst strTarget
If datVideos.Recordset.NoMatch Or _
datVideos.Recordset("Rented") Then
MsgBox "Video does not exist or is rented", _
vbCritical, "Video Status"
txtVideoID.Text = ""
txtVideoID.SetFocus
Else
txtVideoName.Text = datVideos.Recordset("Video_Name")
txtVideoPrice.Text = Format(datVideos.Recordset_
(“Price”),”currency")
End If
End If
End Sub
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
7
Chapter 10:
Advanced
Database
Operations
Revising
Vintage Videos
Setting
RecordSource
at run time
DBGrid Control
DBList and
DBCombo
Controls
SQL Queries
The Data View
Window
Copyright © 2001
by Wiley. All rights
reserved.
Setting the RecordSource at Run Time
• Requires two statements—one to assign the
RecordSource property to the name of a database
table and one to refresh the database with the
data control Refresh method:
datName.RecordSource = "TableName"
datName.Refresh
• In addition to being set equal to a database table,
the RecordSource property can be set equal to an
SQL query or even to a query created in Microsoft
Access.
• Once the RecordSource property has been set
with these two statements, we may work with the
new Recordset.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
8
Chapter 10:
Advanced
Database
Operations
Revising
Vintage Videos
Setting
RecordSource
at run time
DBGrid Control
DBList and
DBCombo
Controls
SQL Queries
The Data View
Window
Copyright © 2001
by Wiley. All rights
reserved.
DB Controls
• Three useful controls for working with
databases are:
– DBList control (dbl)
– DBCombo control (dbc)
– DBGrid control (dgd)
• You will need to add them to the toolbox by
using the Project|Components menu
option.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
9
Chapter 10:
Advanced
Database
Operations
Revising
Vintage Videos
Setting
RecordSource
at run time
DBGrid Control
DBList and
DBCombo
Controls
SQL Queries
The Data View
Window
Copyright © 2001
by Wiley. All rights
reserved.
The DBGrid Control
• The DBGrid control can be used to display an
entire table or selected rows and columns.
• It can also be used to dynamically edit the
database by saving changes that are entered in the
various cells of the control.
• The DBGrid control is linked to the database by
setting its DataSource property equal to a data
control on the same form.
• To prevent the user from changing the contents of
DBGrid cells, set the AllowUpdate property to
False.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
10
Chapter 10:
Advanced
Database
Operations
Revising
Vintage Videos
Setting
RecordSource
at run time
DBGrid Control
DBList and
DBCombo
Controls
SQL Queries
The Data View
Window
Copyright © 2001
by Wiley. All rights
reserved.
The DBList and DBCombo Controls
• The DBList and DBCombo controls are useful for
displaying just one column of the database table
for all or selected rows.
• They are read-only controls in that you cannot edit
the database through them.
• The DBList has two key properties for displaying
records:
– The RowSource property points to the data control that
will provide the database records to be listed.
– The ListField property designates the database field
that will be displayed.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
11
Chapter 10:
Advanced
Database
Operations
Revising
Vintage Videos
Setting
RecordSource
at run time
SQL Queries
• An SQL query may be used as the RecordSource
property.
• The basic form of an SQL query:
SELECT fieldnames FROM tables WHERE query
DBGrid Control
DBList and
DBCombo
Controls
SQL Queries
The Data View
Window
Copyright © 2001
by Wiley. All rights
reserved.
• The query part of the SQL statement looks exactly
like the queries we have been using with the
FindFirst and FindNext methods.
• For example:
SELECT * FROM Members WHERE City = ’Athens’
(the asterisk is used to find all fields.)
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
12
Chapter 10:
Advanced
Database
Operations
Revising
Vintage Videos
Setting
RecordSource
at run time
DBGrid Control
DBList and
DBCombo
Controls
SQL Queries
The Data View
Window
Copyright © 2001
by Wiley. All rights
reserved.
The LIKE Operator
• The “LIKE” operator allows us to look for field
values that partially match a search string input by
the user.
• The query combines the LIKE operator with the
asterisk wild-card character (*).
• The general form of the query using the LIKE
Operator is:
– SELECT * FROM Table WHERE FieldName LIKE
’*SearchString*’
( both the asterisks and the search string are
enclosed in apostrophes in the query string)
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
13
Chapter 10:
Advanced
Database
Operations
The SelectedItem Property
Revising
Vintage Videos
Setting
RecordSource
at run time
DBGrid Control
DBList and
DBCombo
Controls
• The SelectedItem property of the DBList
control can be used to find the bookmark in
the database of a selected item.
• To ensure an item has been selected use
the IsNull( ) function to check the
SelectedItem property.
SQL Queries
The Data View
Window
Copyright © 2001
by Wiley. All rights
reserved.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
14
Chapter 10:
Advanced
Database
Operations
Revising
Vintage Videos
Setting
RecordSource
at run time
DBGrid Control
DBList and
DBCombo
Controls
SQL Queries
The Data View
Window
Copyright © 2001
by Wiley. All rights
reserved.
More On the DB Controls
• The DBList (and DBCombo) control can be linked
to another DB control so that the field name in the
DBList control is updated by clicking a record in
the other DB control.
• To make a link, consider three other properties:
– The DataSource property is the name of the data control
with the recordset to be updated.
– The DataField property is the field to be updated in the
changing recordset.
– The BoundColumn is the name of the field with the
value to be inserted in the table being updated.
• Note that the BoundColumn and DataField
properties point to the same field in different
database tables.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
15
Chapter 10:
Advanced
Database
Operations
Revising
Vintage Videos
Setting
RecordSource
at run time
DBGrid Control
DBList and
DBCombo
Controls
SQL Queries
The Data View
Window
Copyright © 2001
by Wiley. All rights
reserved.
The Data View Window
• The Data View window allows you to view and
modify a database from within Visual Basic.
• Click on the Data View icon in the toolbar or select
Data View window from the View menu bar
option.
• The Data Link Properties window is where you
will need to set the database properties.
• If connection is set properly, you should be able to
display Tables, Views, and Stored Procedures
folders.
Introduction to Programming with Visual Basic
6.0 by McKeown and Piercy
16