PRG 240 - Class #9 Lecture
Submit Ch. 3 VB Auto Center assignments.
Discuss the specs for Ch. 4 VB Auto Center
Ch. 5 Discussion
- Compare and contrast the ADO programming model with ADO data controls &
Data Environments. ADO stands for ActiveX Data Objects.
- important objects, collections, methods, & properties
- How did we view data with ADO data controls in Ch. 3?
- its easy to use, graphical, minimum code is involved
- set its ConnectionString & RecordSource properties &
add a bound control
- How did we view data with Data Environments in Ch. 3?
- With the ADO Programming model, we can access data directly via
code. No controls or designers are necessary. This is more flexible
and saves system resources.
- use the Object Browser to view the objects and collections of ADODB.
(You must add a reference first.)
- Connection - connects project with data source (Access, SQL
Server, Oracle db, etc.)
- Command - queries, existing tables, SQL statements, or SQL server
stored procedures
- Recordset - temporary set of records to display to the user,
must have a connection but not necessarily a command
- Discuss the graphic on p. 208. Note the containment as well as the independence
of the 3 main objects - Connection, Command, & Recordset
- discuss the events of the ADO model
- see the charts on p. 211
- these events give us a place to validate input, cancel operations,
etc.
- We will not be using transaction related events since we are building
desktop VB applications.
- As a class, create Connections, Commands,& Recordsets with ADO object
code in a demo program
- Add a reference to ADODB by using the menu command Project/References...
(not Project/Components...) to add a reference to "Microsoft ActiveX
Data Objects 2.1 Library".
- To use ADO Connection objects, you generally follow these steps:
- Create the Connection object
- Open the connection
- Execute an SQL statement.
- Use the records returned from the query.
- Terminate the connection. (Example - conAVB.Close & Set conAVB
= Nothing ).
- After the ADO Connection object is opened, you will be accessing records
in a Recordset. You will generally be following these steps:
- Declare a recordset object variable. (Dim statement)
- Instantiate the object variable. (Set statement)
- Use the Open method to request records from a data source.
- You must declare object variables to serve as your Connection, Command
or Recordset objects, do this in the general declarations area. Use WithEvents
if you wish to expose the events of the object.
Dim WithEvents conAVB As ADODB.Connection
Dim rsPatient As ADODB.RecordSet
- Within a procedure such as Form_Load, assign a value with a Set statement.
This is late-binding and saves resources.
Set conAVB = New
ADODB.Connection
Set rsPatient = New ADODB.RecordSet
- Set the properties such as ConnectionString, CursorLocation, CursorType,
etc. as well in the procedure.
conAVB.ConnectionString = "Provider ..... (see p. 213)
- Don't forget to call the Open method for your objects. Example,
conAVB.Open
rsPatient.Open "Patient", conAVB, , , adCmdTable
- Review the syntax for the Recordset's Open method on p. 216
- (if time) Practice using selected ADO events by inserting MsgBox or Immediate
window (Debug) statements
- discuss custom code that could be used in these events
- As a class, build Mr. Minich's Ch5 Example1
project. See code on the handout.
- (if time) As a class, execute and analyze the code for the Ch05ObjectModel
project from the Student CD-ROM.
- (if time) Introduction to SQL (Structured Query Language)
- SELECT - allows you to specify which fields will be returned
- FROM - allows you to specify which tables will be used to get the fields
specified in SELECT
- WHERE - optional, allows you to specify criteria
- Examples - see p. 219, note the use of single quotes, double quotes,
brackets, and the ampersand
- Another example -
rsPatient.Open "SELECT * FROM Patient", conAVB, , , adCmdtable
(compare to example from above)
- (if time) review the Query Builder from Ch. 4 by doing Ch04SQLQuery on p.
181. Create the project by following the steps. Copy and paste the code from
the CD-ROM to save time.
- (if time) as a class, write SQL statements that query a database
- (if time) as a class, write SQL statements that update a database
- (if time) Use the Data Form wizard to easily create ADO objects in code.
See p. 230.
- (if time/ if necessary) Review the Ch04 "InChapter Exercises"
from the Student CD-ROM
- Ch04Update - pp. 167+
- Ch04FindListBox - pp. 172+
Review specifications for the Ch. 4 VB Auto Center project.
Homework Assignment:
- Review any notes and/or activities in the above lecture that were not covered
in class.
- Reread Ch. 5 if necessary.
- Read Ch. 6.
- Begin to work on Ch. 4 VB Auto Center. It will be due on April 23.
It is optional to allow the user to sort the customers.
Copyright © 2001 Curtis Minich. All Rights Reserved.