CIS 338 Using ADO Active X Data Objects
![CIS 338: Using ADO (Active. X Data Objects) [largely replaced by adonet. vb] Dr.](https://slidetodoc.com/presentation_image_h2/941d265c9e35e2aab592adaedbea7d86/image-1.jpg "CIS 338: Using ADO (Active. X Data Objects) [largely replaced by adonet. vb] Dr.")
CIS 338: Using ADO (Active. X Data Objects) [largely replaced by adonet. vb] Dr. Ralph D. Westfall April, 2003
Universal Data Access ADO. NET is part of Microsoft's universal data access strategy n Universal Data Access FAQs ODBC: open database connectivity n API standard for using relational databases w API = application programming interface w includes "drivers" for Access, d. Base, DB 2, Excel, text, etc. n Microsoft is leading ODBC backer
Universal Data Access - 2 OLEDB: DB object linking & embedding n next step beyond ODBC accesses relational databases with ODBC provides access to other data also w e-mail, video, voice, images, web pages, etc. w data is accessed "in place" (no need to first move it into a database)
OLEDB Concepts data store = "persistent" collection of data provider = gets data from data store n n OLEDB "driver": database, email data, etc. OLEDB "has nothing to do with Object Linking and Embedding" (Word, Excel) data consumer = application that uses data n ADO (Active. X Data Object) interfaces to data store through OLEDB
ADO. NET replace. S DAO, RDO and previous ADO can use ADO. NET with VB in code via object interface w more powerful than DAO and previous ADO w numerous objects with useful properties and methods
OLEDB with ADO. NET dataset object n n copy of data in memory, rather than connected to the database can have 1 to many Data. Table objects ADO. NET OLEDB data providers n n SQLClient driver for SQL Server (Microsoft) Ole. DBClient driver for other databases
Data. Set Object Data Tables "collections" of table(s) information n n rows, columns of table relationships with other tables constraints to manage additions/deletions from tables that are related to other tables data "views" that allow data to be seen in different ways than it is stored in table(s)
Data Provider Objects connection (to file or server) command object runs SQL or stored procedures in the database data reader (if need high performance) n read only, forward only data adaptor (more capabilities) n add, change, delete, relate tables
ADO. NET: XML Data Transfer industry standard for transferring data like HTML, but can create other tags n n HTML: <title>[some text]</title> XML: <price>[value for price]</price> like HTML, can have tags within others n n HTML: <html><body></html> XML: <name><first></name>
XML Schema separate file that describes data in an XML file n n type of data: numeric, text, etc. occurrences: 0 or 1, or either to many relationships e. g. , name includes 1 st and last names other characteristics e. g. , primary key
Exercise create your own tags for something you are familiar with n food, pets, motor vehicles, video games, ways to communicate, college courses, etc. make sure that tags are "nested" n open and close inside other "boxes" then fill in with some sample data
Database System Options SQL Server n n high performance not well-suited for using on one computer Microsoft Data Engine (MSDE) n n n chopped down version of SQL Server can work with SQL Server files but also can work with Microsoft Access
Creating an MSDE Database note: may have problems on network start Access File>New, New>Project (New Data) Access creates a MSDE database (. adp) n n data storage is different, but you can work with it like an Access database (. mdb) can edit data or upload files as with. mdb
Connecting ADO to Access start a new. NET Windows application View>Server Explorer right click Data Connections>Add Connection click Provider tab>Microsoft Jet 4. 0 OLE DB Provider>Next button browse … for Access database>OK
Using Data Connection viewing data in a table in Server Explorer, expand Data Connections, Access, Tables icons right click on a Table>Retrieve Data from Table close this preview window
Create a Data. Adapter in Server Explorer, click and drag a Table onto the form n note 2 new components in tray below right click Data. Adaptor>Generate Dataset
Data. Grid with Data. Adapter add a Data. Grid set Data. Source property to Data. Set in Form_Load, use. Fill method of Data. Adapter with Data. Set as argument n Click Start to view Data. Grid
List/Combo. Box with Data. Set drag/drop List. Box onto form set Data. Source to Data. Set (drop down) set Display. Member to a field in table add. Fill method of Data. Adapter n see previous slide if didn't do this already note that data is in same order as in database (not sorted)
Data. View List/Combo. Box Data. View makes it possible to sort and do other things with database data drag/drop Data. View object to form/tray set Data. View Table property to a table from the Data. Set (drop down) type in a field name for Sort property n see field names in Server Explorer
Data. View List/Combo. Box - 2 drag/drop Combo. Box onto form set Data. Source to Data. Set (drop down) set Display. Member to a field in table add. Fill method of Data. Adapter n see previous slide if didn't do this already can set. List. Index property to -1 so that no item is selected at start (List. Box too)
SQL as Source of Data expand Data. Adaptor Select Command property to see Command. Text code can modify this SQL by changing the line, or by clicking … to see Query Builder n n n can (un)select fields in Output column can add a WHERE condition value ? in the Criteria column(s) prompts user
Binding Other Controls to Data draw Text. Box or Label on form expand (Data. Bindings) property click Text, and select table/field from drop down add code to load Data. Set with just the item input or selected in another control
Binding Other Controls - Code
![Setting Up ADO with a DSN Windows (not VB): Start>Settings>Control Panel[>Administrative Tools>Data Sources] n](http://slidetodoc.com/presentation_image_h2/941d265c9e35e2aab592adaedbea7d86/image-24.jpg "Setting Up ADO with a DSN Windows (not VB): Start>Settings>Control Panel[>Administrative Tools>Data Sources] n")
Setting Up ADO with a DSN Windows (not VB): Start>Settings>Control Panel[>Administrative Tools>Data Sources] n n n click Data Sources (32 bit) [User DSN tab] select MS Access Database, click Add select Microsoft Access Driver, click Finish click select, choose database (e. g. , BIBLIO) type in Data Source Name (DSN) and description, then click OK w put in what you want to call DSN, your description
Using ADO Data Control right click toolbox, select Components click Microsoft ADO Data Control 6. 0, OK click Adodc control on toolbar, draw on form right click on control to see Properties page n n first set up Source of Connection (General tab) select Use ODBC Data Source Name and select the DSN you just created (or Use Connection String>Build etc. with latest Microsoft Jet [=Access] Provider)
Using ADO Data Control - 2 select Record. Source tab select 1 of following Command Types: n n n ad. Cmd. Text – need to type in SQL query ad. Cmd. Table – need to select the table name ad. Cmd. Stored. Procedure – need to select the procedure name enter SQL, or select Table or stored procedure (e. g. , Publishers), click OK
Using ADO Data Control - 3 add controls (e. g. , 2 textboxes) to form bind controls to data fields (like with DAO; e. g. , Name, City) n n n set Data. Source properties = name of data control from dropdown list set Data. Field properties to field names from dropdown list run project, scroll with ADO data control
Using Code & ADODC Control 'command button event code (need a 3 rd 'textbox to run this) Dim s. State as String, s. SQL as String s. State = Text 3. Text '3 rd Text. Box s. SQL = "select * from Publishers" If s. State <> "" Then s. SQL = s. SQL & _ " WHERE State = '" & s. State & "'" 'space Adodc 1. Command. Type = ad. Cmd. Text Adodc 1. Record. Source = s. SQL Adodc 1. Refresh
Data Grid Control displays database data on a form like in a spreadsheet can use this layout to edit data in the database
Using Data. Grid Control right click toolbox, select Components n n click Microsoft ADO Data Control 6. 0 click Microsoft Data Grid Control 6. 0, OK click Adodc control on toolbar, draw on form right click on Adodc control to see Properties n n 1 st set Source of Connection (General tab) select Use ODBC Data Source Name and select DSN (BIBLIO), or use connect string select Record. Source tab select Command Type (ad. Cmd. Table), select Table name (Publishers)
Using Data. Grid Control - 2 click toolbar's Data. Grid, draw on form using regular Properties window, set Data. Source to connection being used then right click on Data. Grid, select Retrieve fields
Using Data. Grid Control - 3 Data. Grid properties n use regular Properties window to set some properties w e. g. , Caption n n right click on grid for Properties Pages for others General tab w Column. Headers – on or off w Enabled – allows user to scroll, select, modify w Allow. Add. New, Allow. Delete, or Allow. Update – user can add, delete, or change database contents
Using Data. Grid Control - 4 Properties Pages (continued) Keyboard tab n n allow use of arrow keys set Tab. Action tab key behavior (reference) other tabs n Color, Font, Format (number, date/time, + etc. like in Excel)
Editing a Data. Grid right click grid, select Edit, right click again n use commands to change grid w cut, paste, delete, etc. n can split a grid to get a new user window w use Property Pages Layout tab to uncheck Visible for individual columns in different splits
Using Active. X Data Objects Project>References> and then check: n Microsoft Active. X Data Objects 2. x Library w e. g. , x = 2. 7 if you have it n provides ADOR (recordset) objects w Recordset, Field and Property objects n provides ADODB objects w above objects plus Connection, Command, Parameter and Error objects
Set Up a Data Source in Code can use a connection object to access a database with a DSN Dim cn As ADODB. Connection Set cn = New ADODB. Connection cn. Connection. String="DSN=[name]" cn. Open
Setting Up a Data Source - 2 connecting without a DSN (p. 629) Dim cn As ADODB. Connection '(General) Set cn = New ADODB. Connection cn. Connection. String = _ "Driver=Microsoft Access Driver (*. mdb); DBQ=C: [path][file]. MDB; " 'need just one space before (*. mdb) 'NO spaces!! in Driver=Microsoft. . . cn. Open
![Using Recordsets in Code Dim cn As ADODB. Connection Dim rs[name] As ADODB. Recordset](http://slidetodoc.com/presentation_image_h2/941d265c9e35e2aab592adaedbea7d86/image-38.jpg "Using Recordsets in Code Dim cn As ADODB. Connection Dim rs[name] As ADODB. Recordset")
Using Recordsets in Code Dim cn As ADODB. Connection Dim rs[name] As ADODB. Recordset 'add to (General Declarations) Set cn = New ADODB. Connection cn. Connection. String = _ "Driver=Microsoft Access Driver (*. mdb); DBQ=C: [path][file]. MDB; " cn. Open
![Using Recordsets in Code - 2 Const s. SQL = "SELECT * FROM [table]"](http://slidetodoc.com/presentation_image_h2/941d265c9e35e2aab592adaedbea7d86/image-39.jpg "Using Recordsets in Code - 2 Const s. SQL = \"SELECT * FROM [table]\"")
Using Recordsets in Code - 2 Const s. SQL = "SELECT * FROM [table]" Set rs[name] = New ADODB. Recordset rs[name]. Active. Connection = cn rs[name]. Source = s. SQL rs[name]. Open rs[name]. Move. First Print "[ ]= " & _ rs[name]. Fields("[field]")
![Multiple Ways to Use Objects Set rs[name] = New ADODB. Recordset rs[name]. Active. Connection](http://slidetodoc.com/presentation_image_h2/941d265c9e35e2aab592adaedbea7d86/image-40.jpg "Multiple Ways to Use Objects Set rs[name] = New ADODB. Recordset rs[name]. Active. Connection")
Multiple Ways to Use Objects Set rs[name] = New ADODB. Recordset rs[name]. Active. Connection = cn rs[name]. Source = s. SQL rs[name]. Open OR Set rs[name] = cn. Execute(s. SQL)
![Recordset Lock Types rs[name]. Lock. Type = … n ad. Lock. Read. Only –](http://slidetodoc.com/presentation_image_h2/941d265c9e35e2aab592adaedbea7d86/image-41.jpg "Recordset Lock Types rs[name]. Lock. Type = … n ad. Lock. Read. Only –")
Recordset Lock Types rs[name]. Lock. Type = … n ad. Lock. Read. Only – can't add, change, or delete n ad. Lock. Pessimistic – record locked while working on it n ad. Lock. Optimistic – record locked when submitted to database (but rejected if another user already has a lock on it) n ad. Lock. Batch. Optimistic – multiple records submitted, locked individually while being updated
![Recordset Cursor Types rs[name]. Cursor. Type = … n n ad. Open. Forward. Only](http://slidetodoc.com/presentation_image_h2/941d265c9e35e2aab592adaedbea7d86/image-42.jpg "Recordset Cursor Types rs[name]. Cursor. Type = … n n ad. Open. Forward. Only")
Recordset Cursor Types rs[name]. Cursor. Type = … n n ad. Open. Forward. Only – fast, but one-way ad. Open. Key. Set – user can see changes by other users, but not new records or deletions ad. Open. Dynamic – slowest, but user can see all modifications by other users ad. Open. Static – can't see any modifications
![Updating Databases with Recordsets in ADO Set rs[name] = New ADODB. Recordset rs[name]. Cursor.](http://slidetodoc.com/presentation_image_h2/941d265c9e35e2aab592adaedbea7d86/image-43.jpg "Updating Databases with Recordsets in ADO Set rs[name] = New ADODB. Recordset rs[name]. Cursor.")
Updating Databases with Recordsets in ADO Set rs[name] = New ADODB. Recordset rs[name]. Cursor. Type = [ ] rs[name]. Lock. Type = [ ] rs[name]. Source = [table, SQL] rs[name]. Active. Connection = cn rs[name]. Open
![Updating Databases with Recordsets in ADO - 2 'changing field values rs[name]. Fields("[field]") =](http://slidetodoc.com/presentation_image_h2/941d265c9e35e2aab592adaedbea7d86/image-44.jpg "Updating Databases with Recordsets in ADO - 2 'changing field values rs[name]. Fields(\"[field]\") =")
Updating Databases with Recordsets in ADO - 2 'changing field values rs[name]. Fields("[field]") = [ ] rs[name]. Update '1 command 'need 2 commands to change in DAO 'adding new records (2 commands) rs[name]. Add. New '1 st command rs[name]. Fields("[field]") = [ ] rs[name]. Update '2 nd command
Disposing of Objects to conserve resources, get rid of objects when finished with them rs[name]. Close 'disconnects from database Set rs[name] = Nothing 'removes from memory cn. Close Set cn = Nothing
Data Source Name (DSN) DSNs allow you to set up "virtual addresses" on a computer n DSN "points to" the actual physical path w can change physical path for a DSN (e. g. , when move application to another computer) but use same DSN in code n n DSNs can make applications more portable but DO NOT use DSNs in your projects for CIS 338 (they are not on my computer!)
- Slides: 46
