Transcript ADO

Visual Basic ADO Programming
56:150 Information System Design
Introduction 1
Microsoft ActiveX Data Objects (ADO)
enables you to write an application to access
and manipulate data in a database server
through an OLE DB data provider.
High speed, ease of use, low memory
overhead, and a small disk footprint
Introduction 2
What’s data provider
A control or object that provides data for use
with another control or program. The data
provider makes data connectivity much easier
by hiding most of the implementation of data
storage.
What’s OLE DB
A set of COM-based interfaces provide
applications with uniform access to data stored
in diverse information sources, or data stores
Introduction 3
To use ADO objects in an application, you
must first add a reference to the ADO
component.
Start a Standard EXE project and then select
Project References. In the Reference
window, locate Microsoft ActiveX Data
Objects 2.x Library and check the box
before it.
Main Objects
The ADO object
model defines a
collection of
programmable
objects that can be
used by any of the
Microsoft Visual
languages
The Connection Object
to establish connections between the client
and database server
ConnectionString Property
a long string with several attributes separated
by semicolons
“Provider = Microsoft.Jet.OLEDB.4.0; Data
Source=C:\Program Files\VB98\Nwind.mdb”
“Provider=SQLOLEDB.1;User ID=sa;
password=; Initial Catalog=Northwind; Data
Source=EXPERTNEW”
The Connection Object
Open Method
CN.open
The open method accepts a number of optional
arguments (ConnString, UserID, password,
options)
Close Method
CN.Close
Set CN = Nothing (remove the Connection
Object from memory)
Connection Example
Dim dbcon as ADODB.Connection
Set dbcon = New ADODB.Connection
dbcon.ConnectionString _
="Provider=MSDASQL.1;Persist Security _
Info=False;Data Source=NWIND"
dbcon.ConnectionTimeout = 10
dbcon.Open
dbcon.close
Set dbcon = Nothing
The Command Object
to issue commands, such as SQL queries
and updates, to the database
ActiveConnection Property
If ActiveConnection is set with a reference to a
Connection Object, the Command object uses
an exiting connection.
If ActiveConnection is set with a connection
string, a new connection is established.
The Command Object
Execute Method
Use the Execute method of the Command
object to execute a query, data definition
command, or stored procedure.
Set rs = cmd.Execute(NumRecords,
Parameters, Options)
Options specify the type of query (in the form
of CommandTypeEnum constant) to optimize
processing.
CommandTypeEnum
adCmdStoreProc
The command is the name of a
Stored procedure
adCmdTable
The command is a table’s name.
“Select * from table_name” is passed to the server
adCmdTableDirect
The command is a table’s name.
More efficient that adCmdTable option
adCmdText The command is a SQL statement
adCmdUnknown The command is unknown
(default)
Command Example
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Set cmd = New ADODB.Command
cmd.CommandText = "select distinct ShipCountry
from orders"
cmd.CommandType = adCmdText
Set cmd.ActiveConnection = dbcon
Set rst = New ADODB.Recordset
Set rst = cmd.Execute
Command Example
You can do delete, update, insert using
Command Object with the right sql sentence.
Dim cmd As ADODB.Command
Dim lngAffected As Integer
Set cmd = New ADODB.Command
cmd.ActiveConnection = dbcon
cmd.CommandType = adCmdText
cmd.CommandText = "UPDATE tblOrders SET
ShipCountry = 'United States' WHERE ShipCountry
= 'USA'“
cmd.Execute lngAffected
The Recordset Object
to view and manipulate the results of the query
Open Method
To execute a query
open ( [Source], [ActiveConnection], [CursorType As
CursorTypeEnum = adOpenUnspecified], [LockType
As LockTypeEnum = adLockUnspecified], [Options As
Long = -1]))
Source can be a sql statement, a valid command object,
a table name, a query name (Access), a stored
procedure name (SQL Server)
Options is a constant that indicates how the provider
should evaluate the Source argument if it represents
something other than a Command object
Cursor Type (CursorTypeEnum)
adOpenForwardOnly
This cursor can be scanned forward only, is
suitable for one-pass operations. Less expensive
than other types of cursors (default)
adOpenStatic
A snapshot of the database the moment the cursor
was created. It can be scanned in both directions.
You can’t see modifications made by other users
after the creation of the cursor.
adOpenKeyset
Like a dynamic cursor, except that you can't see
records that other users add. Data changes by other
users are still visible.
adOpenDynamic
Additions, changes, and deletions by other users
are visible, and all types of movement through the
Recordset are allowed.
The Recordset Object
Example
Dim rst As ADODB.Recordset
Dim StrSQL As String
Set rst = New ADODB.Recordset
StrSQL = "select Description from categories where
categoryname = '" & Combocategory.Text & "'"
rst.Open Source:=StrSQL,
ActiveConnection:=dbcon, Options:=adCmdText
The Recordset Object
AddNew: add new rows to recordset
rst.AddNew
rst.Fields("LastName") = "Smith"
rsr.Fields("FirstName") = "Tommy"
rst.Update
Use the update method to save the new row. If
you attempt to close the recordset with an update
pending but haven't explicitly saved the row,
you'll get a runtime error
The Record Object
Change data
Move to the desired row
Make changes
optionally use update method to save updates
rst.Find "[ContactTitle] = 'Owner'"
If rst.EOF Then MsgBox "No Match was
Found!"
Else rst.Fields("ContactTitle") = "Manager"
rst.Update
The Recordset Object
Delete records
Find the desired rows
Use delete method to delete.
rst.Find "[ContactTitle] = 'Owner'"
If rst.EOF Then MsgBox "No Match was
Found!"
Else rst.delete
End if
The Recordset Object
Other frequently used methods
Cancelupdate, Movefirst, Movenext, Movelast,
Moveprevious
Other frequently used Properties
Fields, Filter, RecordCount
ADO Data Control
Nothing new but a wrapper for the ADO
Recordset object.
Unlike the Recordset object, ADODC is
visible at run time.
It will be shown in Sample program.