Transcript ActiveX Data Control

ActiveX Data Control
BICS 546
Intrinsic and ActiveX Controls
• VB supports two types of controls:
– Intrinsic controls (command button, listbox,
etc): When compile a program the code to call
the intrinsic controls becomes embedded in the
executable file.
– ActiveX controls: the code containing the
methods and events for an ActiveX control is
stored in a .OCX file and not stored in the
executable.
Intrinsic and ActiveX Controls
VB Program
Executable .EXE
Intrinsic
Controls
Windows
Library
ActiveX
Controls
ActiveX .OCX
ActiveX Controls
ActiveX is build upon COM, a contract that
defines a standard interface by which objects
communicate.
ActiveX controls support Property Pages. You
set properties by using either property page or
property window.
ADO Data Control
• An ActiveX control
ADO Object Model
• There are three main objects on which the ADO
object models is built:
– Connection object: make connection to the database.
– Command object: run SQL against the database.
– Recordset object: holds some or all of the records in
database.
• Note: Command and recordset object can make
connections independently.
Three Ways of Connecting to
Databases Using ADO
• Data Environment
• ADO Data Control
• Connecting in code using ADO Connection
object.
ADO Data Control
• Project/Components/Microsoft ADO 6.0
• Double Click ADO to insert ADO
• Right Click and select ADODC Properties
– General : Source of Connection
• For connection String, data providers’ property dialog are
different
– RecordSource:
• 1 – adCmdText: SQL statement
• 2 – adCmdTable: Table in the database
• 4 - adCmdStoredProc
ADO Properties
• BOF Action, EOF Action
• Command TimeOut: Amount of time in seconds
the ADO control will allow for commands to be
executed on the server.
• ConnectionString
• CursorLocation: server site, client site
• CursorType
• MaxRecords: 0 means no limit.
• RecordSource
Data-Bound Controls
• Data Source
• Data Field
Navigating the Database
• Use the navigation buttons on the data control.
• Code the recordset object created by the data
control.
– To open the recordset: Use the Refresh method in the
FormLoad event.
• DataControlName.Refresh
– To access the recordset’s properties and methods:
• DataControlName.Recordset.Property
• DataControlName.Recordset.Method
BOF and EOF in a Record Set
BOF
Record # 1
Record # 2
Record #3
EOF
Loop through a Recordset
DO Until Recordset.EOF
‘Perform action on data
Recordset.MoveNext
Loop
Do While Not Recordset.EOF
‘Perform action on data
Recordset.MoveNext
Loop
Recordset Methods
• AddNew, Update, Delete
– After delete, MoveNext
• MoveFirst, MoveLast, MoveNext,
MovePrevious
• Find: Search a recordset.
Recordset Navigation Methods
MoveNext, MoveLast, MovePrevious, MoveFirst
ADODC1.Recordset.MoveNext
IF ADODC1.Recordset.EOF THEN
ADODC1.Recordset.MoveLast
End IF
ADODC1.Recordset.MovePrevious
IF ADODC1.Recordset.BOF THEN
ADODC1.Recordset.MoveFirst
End IF
Referring to Database Fields
• DataControName.Recordset.Fields(“fieldName”)
• DataControName.Recordset(“fieldName”)
• DataControName.Recordset!fieldName
ADO Control Recordset Update Methods
Adding records: ADODC1.Recordset.AddNew
(AddNew clears bound controls. The new record is added when
move to the next record or added by:
ADODC1.Recordset.Update (with a SaveNew button)
Deleting records (after deletion, move to the next)
ADODC1.Recordset.Delete
ADODC1.Recordset.MoveNext
IF ADODC1.Recordset.EOF THEN
ADODC1.Recordset.MoveLast
END IF
Updating record: The database is automatically updated if the
user moves to a new record. Otherwise it can be updated by:
ADODC1.Recordset.Update (with a SaveChange button)
Record set Find Method
• Syntax: RecordsetName.Find criteria
• Only accept simple comparison criteria.
• Example:
– Adodc1.recordset.Find “CID = ‘” & txtCID & “’”
• Finding employees hired between 2/15/2000 and 5/15/2000:
– Adodc1.recordset.Find “Hdate > #2/15/2000#”
Then use IF statement in program to filter out those hired after 5/15/2000
Find Code Example
Private Sub Form_Load()
Adodc1.Refresh
List1.Clear
Do Until Adodc1.Recordset.EOF
List1.AddItem Adodc1.Recordset.Fields("cname")
Adodc1.Recordset.MoveNext
Loop
End Sub
Private Sub List1_Click()
Adodc1.Recordset.MoveFirst
Adodc1.Recordset.Find "CNAME = '" & List1.Text & "'"
End Sub
Demo
Accessing Recordset in ADO Data
Control and Data Environment
• Data Control
– Adodc1.Recordset.MoveNext
• Data Environment’s command object
– DataEnvironment1.rsCommand1.MoveNext
Events of ADO Data Control
• Error
• WillChangeField, WillChangeRecord,
WillChangeRecordSet, WillMove.
• FieldChangeComplete,
RecordChangeComplete,
RecordsetChangeComplete
• EndOfRecordSet
• Other mouse events
Parameters Passed to the Event
Handlers
• Error: ErrorNumber, Description, Scode, Source,
etc.
• WillChange: (Illustrate ADODB Enum)
– adReason: adRsnAddNew, adRsnDelete, adRsnUpdate
– adStatus: adStatusCancel,
adStatusCantDeny,adStatusOK
– etc.
• ChangeComplete: adReason, pError, adStatus
Data Validation with the ADO
Data Control
• WillChangeField, WillMove,
WillChangeRecord, WillChangeRecordset
• You can cancel the pending change if the
adStatus is not set to adStatusCantDeny by
changing the adStatus to adStatusCancel.
• The ADO control must be refreshed after an
event handler is fired using the Refresh
method.
WillChangeField Validation
Example
IF adStatus <> adStatusCantDeny THEN
IF MsgBox(“DO you want to update” & Fields(0), vbYesNO) = vbNo THEN
adStatus = adSTatusCancel
END IF
END IF
ADODC1.Refresh
WillChangeRecord Validation
Example
IF adStatus <> adStatusCantDeny THEN
IF MsgBox(“DO you really want to update?”, vbYesNO) = vbNo THEN
adStatus = adSTatusCancel
END IF
END IF
ADODC1.Refresh
Building ADO Control from
Code
Adodc1.ConnectionString = “provider=microsoft.jet.oledb.4.0;persist " & _
"security info=false;data source=c:\program files\microsoft visual
studio\vb98\nwind.mdb"
Adodc1.CommandType = adCmdText
Adodc1.RecordSource = "select * from customers "
Set Text1.DataSource = Adodc1 ‘Note: Use Set because DataSource is object
Text1.DataField = "CustomerID“
Set Text2.DataSource = Adodc1
Text2.DataField = "CompanyName“
ADO Programming Object
Model
Connection
Errors
Error
Command
Parameters
Parameter
RecordSet
Fields
Field
Reference ADO Objects
• Project/References
– Microsoft Activex Data Objects 2.5
• Use the ADODB to define ADO objects
– DIM myConn AS ADODB.Connection
– Dim myCommand AS ADODB.Command
– Dim myRecordset AS ADODB.Recordset
• Use the Set and New to create objects:
– Set myConn = New ADODB.Connection
– Set myCommand = New ADODB.Command
– Set myRecordset = New ADODB.Recordset
• View/Object Browser
Connection with a Connection
Object
• A connection object represents a unique
session with a data source.
• Connection string: database, OLE DB
provider, password, if any, security, etc.
• Use the Open method to open a connection.
• Use the Execute method to execute SQL.
• Manage transaction: BeginTrans,
CommitTrans, rollBackTrans.
Providers
• MSDASQL
– Microsoft OLE DB Provider for ODBC
– The default provider for ADO
•
•
•
•
Microsoft.Jet.OLEDB.3.51
Microsoft.Jet.OLEDB.4.0
MSDAORA – For Oracle
SQLOLEDB – Microsoft SQL Server
Connecting String
• (ADO Data Control will build the connecting
string automatically)
• Containing information about database, OLE DB
provider, password, if any, security, etc.
• For Jet database:
– Provider=Microsoft.Jet.OLEDB.4.0;Persist Security
info = False;Data Source=c:\ …\Nwind.mdb
• For SQL Server:
– Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=sa;Initial Catalog=Northeind
ODBC DSN Connection
• When using the ODBC provider, the connection
string can be a DSN. For example:
–
–
–
–
–
Dim Cn as ADODB.Connection
Set Cn = New ADODB.Connection
Cn.Provider = “MSDASQL”
Cn.ConnectionString = “DSN=Sales”
Cn.Open
• Or:
– cn.Open ("provider=msdasql;dsn=Sales")
Connection Object Methods
• OPEN
– cn.Open("provider=microsoft.jet.oledb.4.0;data
source=c:\program files\microsoft visual
studio\vb98\nwind.mdb;")
– Connection string, UserID, Password, etc.
• Close
• Execute:
– Return a recordset after executing a SQL Select
statement.
– Usually is used to execute SQL Insert and Delete that
don’t return set of records.
• Demo
ADO Command Objects
• Using Commands in ADO Data Controls
• Using Commands in Data Environment.
– Right Click Connections/Add Command
• Reach the commands in a Data Environment in
code. For example
• DataEnvironment1.Commands(“Command1”).CommandText =
“Select * from Customers”
• DataEnvironment1.Commands(0).CommandText = “Select *
from Customers” If Command1 is the first command in the
Data Environment.
• DataEnvironment1.Commands(0).Execute
– The recordset created by the command has name:
rs+Command name, for example, rsCommand1, and we
can use recordset’s navigation methods, update methods.
Command Objects Properties &
Methods
• Command object properties:
– ActiveConnection: Current connection
– CommandType: adCmdText, adCmdTable,
adCmdStoredProc, etc.
– CommandText: Table name if command type is
adCmdTable, SQL statement if command type
is adCmdText.
• Command object methods:
– Execute
Command Object’s Parameter
Object
• Parameter properties
– Name
• “Select * From Customers Where CustomerID = ?”
• For the above parameter query, the parameter name is
CustomerID.
– Type: Parameter data type
• adDouble, adSingle, adChar, adVarChar, adVariant, etc.
– Size: Paramter maximum size in bytes.
– Value: Value passed to the parameter query.
• Use the Append method to add the parameter to
the Parameters collection.
Parameter Query Code Example
Option Explicit
Private cn As Connection
Private rs As Recordset
Private cm As Command
Private pm As Parameter
Private Sub Form_Load()
Set cn = New Connection
cn.Open ("provider=microsoft.jet.oledb.4.0;data source=c:\program files\microsoft visual
studio\vb98\nwind.mdb;")
Set cm = New Command
cm.CommandType = adCmdText
cm.CommandText = "select * from customers where CustomerID = ?"
Set pm = New Parameter
pm.Name = "CustomerID"
pm.Type = adVarChar
pm.Value = "DRACD"
pm.Size = 20
cm.Parameters.Append pm
Set cm.ActiveConnection = cn
Set rs = cm.Execute
Text1.Text = rs.Fields(0)
Text2.Text = rs.Fields(1)
End Sub
Creating ADO Recordset Objects
•
•
•
•
•
Connection object’s Execute method
Command object’s Execute method
ADO Data Control
Data Environment’s command object
Recordset’s Open method
Creating Recordset Using Connection
Option Explicit
Private cn As Connection
Private rs As Recordset
Private Sub Form_Load()
Set cn = New Connection
cn.Open ("provider=msdasql;dsn=Sales")
Set rs = cn.Execute("select * from customers", adCmdText)
Set Text1.DataSource = rs
Text1.DataField = "CustomerID"
Set Text2.DataSource = rs
Text2.DataField = "CompanyName“
Note: Text1 and Text2 are bound.
Creating Recordset Using Command
Option Explicit
Private cn As Connection
Private rs As Recordset
Private cm As Command
Private Sub Form_Load()
Set cn = New Connection
cn.Open ("provider=msdasql;dsn=Sales")
Set cm = New Command
cm.CommandType = adCmdText
cm.CommandText = "select * from customers“
Set cm.ActiveConnection = cn
Set rs = cm.Execute
Text1.Text = rs.Fields(0)
Text2.Text = rs.Fields(1)
End Sub
Note: Text1 and Text2 are not bound.
Recordset Properties
•
•
•
•
•
•
•
•
•
BOF, EOF
BookMark
CursorLocation
CursorType
LockType
MarshalOption
MaxRecords
RecordCount
Sort
Recordset Methods
• AddNew, Update, Delete
– After delete, MoveNext
•
•
•
•
•
•
•
Clone: Create a duplicate recordset.
Open, Close
Find: Search a recordset.
MoveFirst, MoveLast, MoveNext, MovePrevious
Requery: Re-Execute the query.
GetRows: Fetch records to an array.
GetString: Gets the recordset as a string.
Recordset Open Method
• RecordsetName.Open Source,
ActiveConnection, CursorType, LockType,
Options
• Source: Command object name, an SQL
statement, a table name, a stored procedure.
• ActiveConnection: Connection string or
DSN. Needed if no connection object
defined.
Recordset’s Open Method with a
Connection
Private Sub Form_Load()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "provider=msdasql;dsn=sales")
rs.Open "customer", cn
Text1.Text = rs.Fields!cid
Text2.Text = rs.Fields!cname
End Sub
Recordset’s Open Method without a
Connection
Private Sub Form_Load()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "customer", "DSN=Sales"
Text1.Text = rs.Fields!cid
Text2.Text = rs.Fields!cname
End Sub
Cursor Type
• adoOpenDynamic: Dynamic cursor lets you view
changes made by other users, and allows all types
of movement through the recordset.
• adoOpenKeyset: Similar to Dynaset except it
prevents you from seeing records that other users
add.
• adoOpenStatic: A static copy for genrating reports.
Changes by other users will not be visible.
• adoOpenForwardOnly: Same as Dynaset but only
let you scroll forward.
LockType
• adLockReadOnly (Default): Read only
• adLockPessimistic: The data provider does what is
necessary to ensure successful editing of the
records by locking records at the data source
immediately when you start editing record’s fields.
• adLockOptimistic: The data provider locks record
only when you call the Update method.
• adLockBatchOptimistic: Batch updates.
Recordset Events
•
•
•
•
FetchProgress, FetchComplete
WillChangeField, FieldChangeComplete
WillMove, MoveComplete
WillChangeRecord,
RecordChangeComplete
• WillChangeRecordset,
RecordsetChangeComplete
• EndOfRecordSet
Using SQL with ADO
• SQL Select commands:
– Connection object’s Execute method
– Command object’s Execute method
– Record object’s Open method
• SQL Update commands: Insert, Delete, Update
– Connection object’s Execute method
• Cn.Execute “Update EmpFile Set Salary = Salary*1.05”
– Command object’s Execute method
sqlSTr = " Update EmpFile Set Salary = Salary*1.05”
cm.CommandType = adCmdText
cm.CommandText = sqlSTr
cm.ActiveConnection = cn
cm.Execute
Recordset’s Open Method with SQL
Private Sub Form_Load()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "select * from customer", "DSN=Sales"
Text1.Text = rs.Fields!cid
Text2.Text = rs.Fields!cname
End Sub
Dim cn As ADODB.Connection
Dim cm As ADODB.Command
Dim rs As ADODB.Recordset
Dim sqlStr As String
Private Sub Command1_Click()
sqlStr = "update customer set rating = " & "'" & Text1.Text & "'"
sqlStr = sqlStr & "where Cname = " & "'" & List1.test & "'"
Debug.Print sqlStr
cm.CommandType = adCmdText
cm.CommandText = sqlStr
cm.ActiveConnection = cn
cm.Execute
End Sub
Private Sub Form_Load()
Set cn = DataEnvironment1.Connection1
cn.Open
Set rs = New ADODB.Recordset
rs.Open "customer", cn
List1.Clear
Do Until rs.EOF
List1.AddItem rs.Fields("cname")
rs.MoveNext
Loop
rs.Close
End Sub
Private Sub List1_Click()
sqlStr = "Select Rating from Customer where CName = "
sqlStr = sqlStr & "'" & List1.text & "'"
rs.Open sqlStr, cn
Text1.Text = rs.Fields("Rating")
rs.Close
End Sub
Note: This program demonstrate working with the data environment. You have to
declare cn, rs, and sqlStr in the general section.