Visual Basic ADO Programming: 56:150 Information System Design

Download as ppt, pdf, or txt
Download as ppt, pdf, or txt
You are on page 1of 21

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
Whats 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.

Whats 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 tables name.


Select * from table_name is passed to the server

adCmdTableDirect

The command is a tables 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 cant 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.

You might also like