Transcript Week 6

CSE2207/CSE3007
Rapid Applications
Programming for Windows
Week 6
Topics
Databases
VB6.0 and Databases
The ADO Data Control
Bound controls
The Recordset Object
Databases
Database
An organized collection of data relating to one
main (business) function e.g. Sales, Human
Resources, Your Investments, Horse Breeding
Data is held in one or more tables (relations),
hence a relational database
Each table is composed of rows and columns
each column represents a field
each row represents a record
Each table is a group of related records
Databases
Basic Database design steps
Identify the information the user will need
from the database (maps to the ER diagram)
Organize the information into 1 or more tables
Establish relationships between tables (if any)
Create indexes if necessary
Define data validation rules if required
Define/create the database
within the DB package itself e.g MSAccess
from VB6.0 via the Visual Data Manager
Structured Query Language (SQL)
 Manipulating data in a relational database is made easier
by using an English-like language called SQL.
 The user creates an SQL “query.” The database engine
“answers” by returning any database rows that meet the
requirements of the query. The query usually contains
the tables to search, the columns to return, and other
information that sets the scope of the search.
"Select Name, Picture from Authors where Date_of_Birth
= #2/7/1947# "
 This SQL query would return the name and picture of all
authors whose birthday is February 7, 1947. If any rows
were returned, you could use bound controls to display
the values.
Data Access and VB6.0
Business data is stored in numerous formats e.g.
spreadsheets, databases, WP documents, email
Microsoft have developed UDA to provide a
uniform set of standards for data access: consists
of a set of complex interfaces, called OLE DB
can deal with any type of data, regardless of storage
method or format
In VB 6.0, access to the OLE DB interfaces is through
ADO (ActiveX Data Objects)
includes an ADO data provider into ODBC
Project|References|Microsoft ActiveX Data Objects 2.0
Library
Databases and VB6.0
Use the built-in application Visual Data
Manager to create and maintain an MS
Access db
Add-Ins|Visual Data Manager, then File|New etc
Right-click Properties, select New Table
For each table
name it, define each field, with data type (and size)
create at least a primary index
define validation rules for fields if applicable
Note: you can’t create a foreign key here
Databases and VB6.0...
The Visual Data Manager…
NB!! You cannot modify field sizes, attributes,
without losing data
You can add more fields, and change names of
existing fields.
Note that default for Zero-Length strings is
NO
Use Refresh button to update your table data
Utility|Data Form Designer supports creation
of a form with controls that display data from a
database
Databases and VB6.0...
Three approaches:
Use the ADO Data Control to provide visual
navigation, data handling and automatic
database connection/manipulation, together
with bound controls.
Use an invisible/visible ADO Data Control for
database connection and manipulation,
supplemented with code, and provide your own
navigation and data handling, together with
bound controls
Handle everything yourself
Approach #1
The ADO Data Control
 Project|Components|Controls|Microsoft ADO Data Control
6.0 (OLEDB) adds the control to the toolbox.
 Double-click to place it on the form
 Set its ConnectionString property to link it to a data
provider (e.g. an Access DB)
On the General Tab, select Use Connection String option
Then click Build -> Data Link Properties dialog box
On Provider tab, select Microsoft Jet……, then Next
On the Connection tab browse/enter an Access DB
name
Click Test Connection. If ok, then Apply
The ADO Data Control...
 To create a permanent OLE DB Data Link to a database:
In Windows Explorer move to an appropriate
directory e.g. VB98
Right-click the right pane of Explorer, then New, then
Microsoft Data Link
Rename the new file e.g. NorthWind.MDL
Right-click the new file, then Properties
Complete Dialog box entries for Provider and
Connection tabs as before
 OLE DB Data Source (Data Link) can also be created as
follows: Control Panel->Data Links icon -> Organise
Data Link Files -> New
The ADO Data Control...
Set the RecordSource property
On its Property Pages, if Command type is adCmdTable,
then select the DB table where the data will come from.
If Command type is adCmdText, then type the required
SQL Select command in the text box (no ; at the end!)
Now place one or more data-aware controls (textbox, label, datalist etc) on the form, and bind
them to the ADO Data Control
(i) DataSource property is the name of the ADO Data
Control e.g. adoTitles
(ii) DataField property is the name of the table field
which the bound control will display e.g. Title
The ADO Data Control...
By default, displays records in entry order
Use a Select…..Order by for the ADO Data Control’s
RecordSource property (with commandType property
as adcmdText)
Can dynamically change which data is displayed see Example 1
RecordSource can be an existing QueryDef e.g.
ADOQuery.RecordSource = “VicContacts”
ADOQuery.Refresh
 UserName, Password needed if DB is password-protected
 Setting Mode to read-only will increase performance (but
user will not be able to change data)
Run the Application
Use the four arrow buttons on the data control to
move to the 1st, previous, next or last record in
the Recordset
Changes made to values in bound controls are
automatically written to the database when the
user moves to a new record.
The ADO Data Control...
 One data control can access one or many DB tables,
depending on the SQL query which it contains
Can specify that data will be retrieved
from a single table
via an existing Stored Procedure in the database
via SQL you write at design time, or assign at runtime
 The user may view, change and add new records, but
not delete records
 Records returned are held in a ‘Recordset’ object
Fields from the ‘current record’ can be displayed via
‘bound controls’, or referenced in code.
The ADO Data Control...
 Use BOFAction, EOFAction to control what happens
when user moves past the 1st, last record in the
recordset
BOFAction property
adDoMoveFirst
0
Keeps the first record as the current record.
adStayBOF
1
BOF: Moving past the beginning of a Recordset triggers the
Data control Validate event on the first record, followed
by a Reposition event on the invalid (BOF) record. At
this point, the Move Previous button on the Data control
is disabled
Using the EOFAction &
BOFAction Properties
EOFAction property
adDoMoveLast 0
Invokes the MoveLast method, positioning the current
record at the last record in the recordset and effectively
preventing the user from scrolling past the end of the
recordset.
 adStayEOF 1
Positions the current record to the invalid (EOF) record
and disables the MoveNext button on the data control.
Using the EOFAction &
BOFAction Properties...
EOFAction property: adDoAddNew
2
When the user moves past the last record, validates
the data in the last record, automatically invokes the
AddNew method, and then positions the data control
on the new record.
If new data is entered, moving off the current (new)
record will automatically trigger an update and save the
new record in the database. If the user moves off the
new record without adding data, the new record is
discarded.
Convenient way of entering many new consecutive
records i.e. move to end, then MoveNext, enter data,
MoveNext, enter data etc
The Recordset Object
 Created by the ADO Data control at run-time based on
the CommandType property (e.g. adCmdTable)
 If the Recordset object contains no records, the BOF
and EOF properties are set to True, and the
Recordset object's RecordCount property setting is 0
Any MOVE action will generate an error - so use the
AddNew method to create a valid first record
 When you open a Recordset object that contains at
least one record, the first record is the current record
and the BOF and EOF properties are False.
 CursorLocation: adUseClient or adUseServer (default)
The ADO Data Control…
CursorType property
 Dynamic cursor (adOpenDynamic) allows additions,
changes, and deletions by all users to be viewed
 Keyset cursor (adOpenKeyset) behaves like a
dynamic cursor, except you cannot see
records that other users add,
records that other users delete.
 Static cursor (adOpenStatic) provides a static copy of
a set of records for you to use to find data or generate
reports
 Static cursor (adOpenForwardOnly)
Approach #2
Use an invisible/visible ADO Data Control for
database connection, manipulation,
supplemented with code, and provide your
own navigation and data handling controls
 1. Create an ADO Data control and link it to a data
provider e.g. MS Access database
 2. Design form(s) with bound controls to display the data
that is retrieved by the ADO Data Control
 3. Either make the DC invisible, and provide
navigation, tasks via buttons, menu options
OR leave the DC visible (maybe disabled until
needed)
Approach #2...
4. Write code to perform navigation (move first,
move last etc), validation, deletion, undo
changes, add, update
So, use a combination of
the ADO Data Control, properties, events, methods
the Recordset object
bound controls
code
The Data Control…
UpdateControls Method
 Gets the current record from a Data Control’s Recordset
object and displays the appropriate data in controls
bound to the same Data Control
Use to restore contents to original values e.g. when
user chooses Cancel
 WillChangeRecord Event
Occurs just before a record in the underlying
recordset is changed - perform validation (See ex. 2)
 MoveComplete Event
(See Example 2)
The Recordset Object
Methods (See Example 2)
AddNew: add a new, blank record to the end of the
recordset
CancelUpdate: cancel changes made to the current
record
Delete: Delete the current record from the recordset
MoveFirst, MovePrevious, MoveNext, MoveLast
Update: Save the changes made to the current record
Properties
BOF, EOF : Beginning, end of the recordset
Searching for Records in a
Recordset
Find Method
 Searches a Recordset for the record that satisfies the
specified criteria. If the criteria is met, the recordset
position is set on the found record; otherwise, the
position is set on the end of the recordset.
 Syntax:
Find (criteria, SkipRows, searchDirection, start)
 The comparison operator in criteria may be ">"
(greater than), "<" (less than), "=" (equal), or "like"
(pattern matching). E.g.
adoDC1.Recordset.Find (”custID = 5”)
Dynamic SQL
Sub FindContact(strColumn as String, strOperator as String)
Dim strSQL as String
strSQL = “SELECT * FROM Contacts”
strSQL = strSQL & “ WHERE “ & strColumn & strOperator
strSQL = strSQL & “’” & txtSearch.Text & “’”
adoDC1.RecordSource = strSQL
adoDC1.Refresh
If adoDC1.Recordset.Recordcount <> 0 Then
txtFound.Text = adoDC1.Recordset!Contact
Else txtFound.Text = “(Contact Not Found)”
End If: End Sub
Recordset Bookmark
property
 Bookmarks allow you to save a current record pointer and
reposition directly to a specific record.
 Save the Bookmark into a Variant.
Dim vntBookMark as Variant
' Save current
vntBookMark = adoDC1.Recordset.Bookmark ’ record pointer.
adoDC1.Recordset.MoveFirst
' Move off the
record…
adoDC1.Recordset.Bookmark = vntBookmark ' Move back
------------------------------------------------------------------------Private Sub dtlContacts_Click(Area As Integer)
adoContacts.Recordset.Bookmark = dtlContacts.SelectedItem
End Sub
Bound Controls
Intrinsic Bound Controls (some)
Check Box
Text Box
Image
List Box
Label
Picture Box
Combo Box OLE Container
Custom Bound Controls (some)
DataList
DataCombo
RichTextBox
DataGrid Masked Edit
Microsoft Chart
DateTimePicker ImageCombo
MonthView
 May need to add the controls to the toolbox via
Project|Controls tab. All are OLEDB enabled.
Bound Controls
Data-Aware Properties
DataChanged
Indicates whether a value
displayed in a bound control has changed. Not
available at design time.
DataSource
Specifies the name of the data
control to which the control is bound. ANY control
with this property can become a ‘bound’ control
DataField
Specifies the name of a field in the
recordset created by the data control, which this
bound control will display
Bound Controls
 When data is displayed in a bound control, the
DataChanged property is set to False. If the user or
any other operation changes the value in the bound
control, the DataChanged property is set to True.
Simply moving to another record doesn't affect the
DataChanged property.
 When the data control starts to move to a different
record, the WillMove event occurs. If DataChanged is
True for any bound control, the data control
automatically invokes the Update method to post the
changes to the database.
 If you don't wish to save changes from a bound control
to the database, you can set the DataChanged
property to False in the WillMove event.
More Data-Bound Controls...
DataList, DataCombo controls
To use with a single Data Control:
Set DataSource & RowSource to same Data
Control e.g. adoTitles
Set DataField & BoundColumn to same field in
the data control’s recordset e.g. PublisherID
Can be bound to 2 different DC’s, linking data
between them
useful in data entry: let the user choose from a list of
Publisher Names, then when they select one, write the
corresponding Publisher ID back to the Titles tables
More Data-Bound Controls...
Binding DBList/DBCombo to 2 ADO Data Controls:
DataSource: The Data Control it is bound to (and which it
will pass data to) e.g. DC1 is linked to the Titles table
DataField: A field in the DataSource recordset. Any list
selection or edit updates this field in the DB e.g.
PublisherID in the Titles Table
RowSource: The Data Control whose recordset (a field in
it) FILLS the list (e.g. DC2 is linked to Publishers table)
ListField: Name of field in the RowSource recordset (DC2)
which will be used to FILL the list i.e. PublisherName
BoundColumn:A field in the RowSource recordset (DC2).
Used to map/link to a field in the Datasource recordset
(field types must match e.g. PublisherID
More Data-Bound Controls...
DataList, DataCombo: More useful properties
SelectedItem property (returns a bookmark)
MatchEntry property (controls the way values
are matched in the list)
When user selects an item in the list, the
BoundText property becomes updated to the
value of the BoundColumn property e.g. if
BoundColumn is PublisherID, and user chooses
‘Amazon’ in the list of Publisher Names, then the
BoundText would contain the ID for ‘Amazon’
 DataGrid control
displays editable data in rows, columns
AllowAddNew, AllowDelete, AllowUpdate
right-click, then “Retrieve Fields” to display at design
time the fields from its ADO Data Control
Then “Edit” to make changes to the display, and
“Properties” to set numerous properties
DataGrid Control Property Pages:
 Use General Tab to control user actions (add, change ..)
 Use Columns tab to change headings, data field
 Use Format tab for data formatting
 Use Layout tab to control column access,visible display
More Data-Bound Controls...
DataGrid control Events:
 BeforeColUpdate, AfterColUpdate, AfterColEdit
occur when focus is moved to another cell, or to
another control in the interface.
Use Before… to validate cell contents.
Use After… to create other fields/cells based on the
value just entered
 BeforeUpdate, AfterUpdate occur ONLY when
focus is moved to a cell in another row (record)
Use Before… to do final validation on the record as a
whole
Linking the DataGrid to
DataList, DataCombo
 Task: User selects a Publisher Name from a
DataCombo, then display all the books published
by that publisher in a DataGrid
Set the DataGrid’s DATASOURCE to adoTitles
Set CommandType of adoTitles to adCmdText
Set the DataCombo’s ROWSOURCE to adoPublishers
Set the BoundColumn properties of the DataCombo to
PublisherID, ListField to PublisherName
Dim strSQL As String ‘code for datacombo Change event
strSQL = “SELECT * FROM Titles WHERE PublisherID = “ _
& dtcNames.BoundText
adoTitles.RecordSource = strSQL
adoTitles.Refresh
Link Multiple DataGrids
Task: User selects a Publisher row in
DataGrid1, then display all the Titles for that
Publisher in DataGrid2
Bind each DataGrid to its own ADO Data Control
Private Sub dtgPubTitles_RowColChange(LastRow As Variant,
ByVal LastCol As Integer)
Dim strSQL As String
strSQL = “SELECT * FROM Titles WHERE PublisherID _
= ” & adoPublishers.Recordset!PublisherID
adoTitles.RecordSource = strSQL
adoTitles.Refresh: End Sub