Active X Data Control BICS 546 Intrinsic and

Active. X Data Control BICS 546

Intrinsic and Active. X 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. – Active. X controls: the code containing the methods and events for an Active. X control is stored in a. OCX file and not stored in the executable.

Intrinsic and Active. X Controls VB Program Executable. EXE Intrinsic Controls Windows Library Active. X Controls Active. X. OCX

Active. X Controls Active. X is build upon COM, a contract that defines a standard interface by which objects communicate. Active. X controls support Property Pages. You set properties by using either property page or property window.

ADO Data Control • An Active. X 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 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 – Record. Source: • 1 – ad. Cmd. Text: SQL statement • 2 – ad. Cmd. Table: Table in the database • 4 - ad. Cmd. Stored. Proc

ADO Properties • BOF Action, EOF Action • Command Time. Out: Amount of time in seconds the ADO control will allow for commands to be executed on the server. • Connection. String • Cursor. Location: server site, client site • Cursor. Type • Max. Records: 0 means no limit. • Record. Source

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 Form. Load event. • Data. Control. Name. Refresh – To access the recordset’s properties and methods: • Data. Control. Name. Recordset. Property • Data. Control. Name. 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. Move. Next Loop Do While Not Recordset. EOF ‘Perform action on data Recordset. Move. Next Loop

Recordset Methods • Add. New, Update, Delete – After delete, Move. Next • Move. First, Move. Last, Move. Next, Move. Previous • Find: Search a recordset.

Recordset Navigation Methods Move. Next, Move. Last, Move. Previous, Move. First ADODC 1. Recordset. Move. Next IF ADODC 1. Recordset. EOF THEN ADODC 1. Recordset. Move. Last End IF ADODC 1. Recordset. Move. Previous IF ADODC 1. Recordset. BOF THEN ADODC 1. Recordset. Move. First End IF

Referring to Database Fields • Data. Contro. Name. Recordset. Fields(“field. Name”) • Data. Contro. Name. Recordset!field. Name

ADO Control Recordset Update Methods Adding records: ADODC 1. Recordset. Add. New (Add. New clears bound controls. The new record is added when move to the next record or added by: ADODC 1. Recordset. Update (with a Save. New button) Deleting records (after deletion, move to the next) ADODC 1. Recordset. Delete ADODC 1. Recordset. Move. Next IF ADODC 1. Recordset. EOF THEN ADODC 1. Recordset. Move. Last END IF Updating record: The database is automatically updated if the user moves to a new record. Otherwise it can be updated by: ADODC 1. Recordset. Update (with a Save. Change button)

Record set Find Method • Syntax: Recordset. Name. Find criteria • Only accept simple comparison criteria. • Example: – Adodc 1. recordset. Find “CID = ‘” & txt. CID & “’” • Finding employees hired between 2/15/2000 and 5/15/2000: – Adodc 1. 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() Adodc 1. Refresh List 1. Clear Do Until Adodc 1. Recordset. EOF List 1. Add. Item Adodc 1. Recordset. Fields("cname") Adodc 1. Recordset. Move. Next Loop End Sub Private Sub List 1_Click() Adodc 1. Recordset. Move. First Adodc 1. Recordset. Find "CNAME = '" & List 1. Text & "'" End Sub Demo

Accessing Recordset in ADO Data Control and Data Environment • Data Control – Adodc 1. Recordset. Move. Next • Data Environment’s command object – Data. Environment 1. rs. Command 1. Move. Next

Events of ADO Data Control • Error • Will. Change. Field, Will. Change. Record. Set, Will. Move. • Field. Change. Complete, Recordset. Change. Complete • End. Of. Record. Set • Other mouse events

Parameters Passed to the Event Handlers • Error: Error. Number, Description, Scode, Source, etc. • Will. Change: (Illustrate ADODB Enum) – ad. Reason: ad. Rsn. Add. New, ad. Rsn. Delete, ad. Rsn. Update – ad. Status: ad. Status. Cancel, ad. Status. Cant. Deny, ad. Status. OK – etc. • Change. Complete: ad. Reason, p. Error, ad. Status

Data Validation with the ADO Data Control • Will. Change. Field, Will. Move, Will. Change. Recordset • You cancel the pending change if the ad. Status is not set to ad. Status. Cant. Deny by changing the ad. Status to ad. Status. Cancel. • The ADO control must be refreshed after an event handler is fired using the Refresh method.

Will. Change. Field Validation Example IF ad. Status <> ad. Status. Cant. Deny THEN IF Msg. Box(“DO you want to update” & Fields(0), vb. Yes. NO) = vb. No THEN ad. Status = ad. STatus. Cancel END IF ADODC 1. Refresh

Will. Change. Record Validation Example IF ad. Status <> ad. Status. Cant. Deny THEN IF Msg. Box(“DO you really want to update? ”, vb. Yes. NO) = vb. No THEN ad. Status = ad. STatus. Cancel END IF ADODC 1. Refresh

Building ADO Control from Code Adodc 1. Connection. String = “provider=microsoft. jet. oledb. 4. 0; persist " & _ "security info=false; data source=c: program filesmicrosoft visual studiovb 98nwind. mdb" Adodc 1. Command. Type = ad. Cmd. Text Adodc 1. Record. Source = "select * from customers " Set Text 1. Data. Source = Adodc 1 ‘Note: Use Set because Data. Source is object Text 1. Data. Field = "Customer. ID“ Set Text 2. Data. Source = Adodc 1 Text 2. Data. Field = "Company. Name“

ADO Programming Object Model Connection Errors Error Command Parameters Parameter Record. Set Fields Field

Reference ADO Objects • Project/References – Microsoft Activex Data Objects 2. 5 • Use the ADODB to define ADO objects – DIM my. Conn AS ADODB. Connection – Dim my. Command AS ADODB. Command – Dim my. Recordset AS ADODB. Recordset • Use the Set and New to create objects: – Set my. Conn = New ADODB. Connection – Set my. Command = New ADODB. Command – Set my. Recordset = 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: Begin. Trans, Commit. Trans, roll. Back. Trans.

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. Connection. String = “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 filesmicrosoft visual studiovb 98nwind. mdb; ") – Connection string, User. ID, 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 • Data. Environment 1. Commands(“Command 1”). Command. Text = “Select * from Customers” • Data. Environment 1. Commands(0). Command. Text = “Select * from Customers” If Command 1 is the first command in the Data Environment. • Data. Environment 1. Commands(0). Execute – The recordset created by the command has name: rs+Command name, for example, rs. Command 1, and we can use recordset’s navigation methods, update methods.

Command Objects Properties & Methods • Command object properties: – Active. Connection: Current connection – Command. Type: ad. Cmd. Text, ad. Cmd. Table, ad. Cmd. Stored. Proc, etc. – Command. Text: Table name if command type is ad. Cmd. Table, SQL statement if command type is ad. Cmd. Text. • Command object methods: – Execute

Command Object’s Parameter Object • Parameter properties – Name • “Select * From Customers Where Customer. ID = ? ” • For the above parameter query, the parameter name is Customer. ID. – Type: Parameter data type • ad. Double, ad. Single, ad. Char, ad. Variant, 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 filesmicrosoft visual studiovb 98nwind. mdb; ") Set cm = New Command cm. Command. Type = ad. Cmd. Text cm. Command. Text = "select * from customers where Customer. ID = ? " Set pm = New Parameter pm. Name = "Customer. ID" pm. Type = ad. Var. Char pm. Value = "DRACD" pm. Size = 20 cm. Parameters. Append pm Set cm. Active. Connection = cn Set rs = cm. Execute Text 1. Text = rs. Fields(0) Text 2. 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", ad. Cmd. Text) Set Text 1. Data. Source = rs Text 1. Data. Field = "Customer. ID" Set Text 2. Data. Source = rs Text 2. Data. Field = "Company. Name“ Note: Text 1 and Text 2 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. Command. Type = ad. Cmd. Text cm. Command. Text = "select * from customers“ Set cm. Active. Connection = cn Set rs = cm. Execute Text 1. Text = rs. Fields(0) Text 2. Text = rs. Fields(1) End Sub Note: Text 1 and Text 2 are not bound.

Recordset Properties • • • BOF, EOF Book. Mark Cursor. Location Cursor. Type Lock. Type Marshal. Option Max. Records Record. Count Sort

Recordset Methods • Add. New, Update, Delete – After delete, Move. Next • • Clone: Create a duplicate recordset. Open, Close Find: Search a recordset. Move. First, Move. Last, Move. Next, Move. Previous Requery: Re-Execute the query. Get. Rows: Fetch records to an array. Get. String: Gets the recordset as a string.

Recordset Open Method • Recordset. Name. Open Source, Active. Connection, Cursor. Type, Lock. Type, Options • Source: Command object name, an SQL statement, a table name, a stored procedure. • Active. Connection: 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 Text 1. Text = rs. Fields!cid Text 2. 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" Text 1. Text = rs. Fields!cid Text 2. Text = rs. Fields!cname End Sub

Cursor Type • ado. Open. Dynamic: Dynamic cursor lets you view changes made by other users, and allows all types of movement through the recordset. • ado. Open. Keyset: Similar to Dynaset except it prevents you from seeing records that other users add. • ado. Open. Static: A static copy for genrating reports. Changes by other users will not be visible. • ado. Open. Forward. Only: Same as Dynaset but only let you scroll forward.

Lock. Type • ad. Lock. Read. Only (Default): Read only • ad. Lock. Pessimistic: 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. • ad. Lock. Optimistic: The data provider locks record only when you call the Update method. • ad. Lock. Batch. Optimistic: Batch updates.

Recordset Events • • Fetch. Progress, Fetch. Complete Will. Change. Field, Field. Change. Complete Will. Move, Move. Complete Will. Change. Record, Record. Change. Complete • Will. Change. Recordset, Recordset. Change. Complete • End. Of. Record. Set

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 Emp. File Set Salary = Salary*1. 05” – Command object’s Execute method sql. STr = " Update Emp. File Set Salary = Salary*1. 05” cm. Command. Type = ad. Cmd. Text cm. Command. Text = sql. STr cm. Active. Connection = 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" Text 1. Text = rs. Fields!cid Text 2. Text = rs. Fields!cname End Sub

Dim cn As ADODB. Connection Dim cm As ADODB. Command Dim rs As ADODB. Recordset Dim sql. Str As String Private Sub Command 1_Click() sql. Str = "update customer set rating = " & "'" & Text 1. Text & "'" sql. Str = sql. Str & "where Cname = " & "'" & List 1. test & "'" Debug. Print sql. Str cm. Command. Type = ad. Cmd. Text cm. Command. Text = sql. Str cm. Active. Connection = cn cm. Execute End Sub

Private Sub Form_Load() Set cn = Data. Environment 1. Connection 1 cn. Open Set rs = New ADODB. Recordset rs. Open "customer", cn List 1. Clear Do Until rs. EOF List 1. Add. Item rs. Fields("cname") rs. Move. Next Loop rs. Close End Sub Private Sub List 1_Click() sql. Str = "Select Rating from Customer where CName = " sql. Str = sql. Str & "'" & List 1. text & "'" rs. Open sql. Str, cn Text 1. Text = rs. Fields("Rating") rs. Close End Sub Note: This program demonstrate working with the data environment. You have to declare cn, rs, and sql. Str in the general section.