accessing data sources with visual basic 6.0 william r. vaughn microsoft technical education (mste)...
TRANSCRIPT
![Page 1: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/1.jpg)
Accessing Data Sources Accessing Data Sources with Visual Basic 6.0with Visual Basic 6.0
William R. VaughnWilliam R. VaughnMicrosoft Technical Education (MSTE)Developer Trainer
12
![Page 2: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/2.jpg)
William R. VaughnWilliam R. VaughnMicrosoft Corporation
Author:Hitchhiker’s Guide to Visual Basic
and SQL Server Microsoft Press, 1996, 1997, 1998
![Page 3: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/3.jpg)
AgendaAgenda
Enterprise Development Strategies– SQL Server 7.0 – Microsoft Database Engine (MSDE)
Making best use of Visual Basic 6.0 Tools– DataEnvironment Designer – Data Object Wizard
Implementing ADO Solutions– Effective Queries– Processing Updates– Managing Stored Procedures
![Page 4: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/4.jpg)
Enterprise Development Strategies:Enterprise Development Strategies:SQL Server 7.0SQL Server 7.0
Larger (8000 byte) page size– Longer Char, VarChar– Less need to record BLOBs in the DB
Unicode (DBCS) support– Nchar, NVarChar, Ntext– Better international support
New Substring functions– CONTAINS, FREETEXT
Better BLOB handling– Fetch starting at “nth” byte
![Page 5: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/5.jpg)
Enterprise Development Strategies:Enterprise Development Strategies:SQL Server 7.0SQL Server 7.0
Top [n] [Percent [n] ]– Preferred over RowCount– Can be used in subqueries
Enhanced local cursor support New uniqueidentifier data type for
storing a globally unique identifier (GUID)– Instead of Identity datatype– Great for disjoint server updates
![Page 6: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/6.jpg)
Enterprise Development Strategies:Enterprise Development Strategies:SQL Server 7.0SQL Server 7.0
Automatically manages admin functions No need to:
– Update statistics– Tune procedure cache– Run DBCC maintenance routines– Preallocate disk space (no disk inits)– Stretch TempDB, DB or Log space
Automatically runs backups New GUI tools
![Page 7: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/7.jpg)
Enterprise Development Strategies:Enterprise Development Strategies:SQL Server 7.0SQL Server 7.0
Shares memory, disk space– Stretches or shrinks resources to match
demand
Does not create TempDB SPs– Not needed to leverage existing query plans
Shares ad-hoc and stored procedures– Executing queries now re-entrant– Automatically senses reusable queries
![Page 8: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/8.jpg)
SQL Server 6.5 SQL Server 6.5 Cache managementCache management
Proced
ure C
ache
1st. instance is loaded
2nd. instance loaded
3rd. instance re-uses first1st. instance finishes
SQL ServerStored Procedures
![Page 9: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/9.jpg)
SQL Server 7.0 SQL Server 7.0 Cache managementCache management
Man
aged M
emory
1st. instance is loaded
2nd. instance shares first
3rd. instance shares first1st. instance finishes
SQL ServerStored Procedures
Data Pages
![Page 10: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/10.jpg)
SQL Server 7.0 SQL Server 7.0 Ad hoc cachingAd hoc caching
If current SQL batch matches (exactly), SQL Server 7.0 uses cached plan.
Query #3 uses cached plan from #1, but #2 is recompiled…
Query1: Insert MyTable values(1,0)Query1: Insert MyTable values(1,0)Query2: Insert MyTable values(2,0)Query2: Insert MyTable values(2,0)Query3: Insert MyTable values(1,0)Query3: Insert MyTable values(1,0)
![Page 11: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/11.jpg)
Q1: Select * from employees where emp_id = 1000Q1: Select * from employees where emp_id = 1000Q2: Select * from employees where emp_id = 5 Q2: Select * from employees where emp_id = 5 Q3: Select * from employees where emp_id = 1000Q3: Select * from employees where emp_id = 1000
Auto parameterizationsAuto parameterizations
Engine guesses constants are parameters Similar parameters share same plan
All three use the same cached plan created with Q1.
![Page 12: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/12.jpg)
Intelligent CachingIntelligent Caching
Tips:– Know what the parameters are?
Mark them (?)Build parameters collection (in RDO/ADO)
– Continue to use SPsThey just run faster
– SPs are now re-entrantWhich makes them even faster
![Page 13: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/13.jpg)
Enterprise Development Strategies:Enterprise Development Strategies:SQL Server Desktop EditionSQL Server Desktop Edition
Microsoft SQL Server Desktop Edition Runs on Windows 95, 98, 2000,
NT 4.0 Workstation/Server Duplicates functionality in core
NT SQL Server 7.0 (no OLAP, EQ, FTS etc.) Performance “purposefully degraded” ~ 5
Users, Supports (only) sockets (TCP/IP) Supplied via
– SQL Server 7.0 NT versions
Includes SQL Enterprise Manager tools– Same as those that ship for NT version
![Page 14: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/14.jpg)
Enterprise Development Strategies:Enterprise Development Strategies:MSDEMSDE
Microsoft Database Engine (MSDE) Runs on Windows 95, 98, 2000, NT 4.0
WS/Svr Duplicates functionality in core
NT SQL Server 7.0 (no OLAP, EQ, FTS etc.)
Performance “purposefully degraded” Database limited to 2GB Supplied through
– Download/CD (now)
– Office 2000
![Page 15: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/15.jpg)
Enterprise Development Strategies:Enterprise Development Strategies:MSDEMSDE
Visual Basic 6.0 “Plus” Pack– Includes SQL Server Developer Edition
To setup databasesTo create permissionsPerform administrative functions
No “Setup.EXE” (requires batch operation)
No DB setup tools—SQLDMO samples– Get tools with SQL Server Desktop (on CD)– Download 45-Day “Trial” version of SQL Server
Desktop Ed.– Get CreateDatabase tool from www.betav.com
![Page 16: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/16.jpg)
Enterprise Development Strategies:Enterprise Development Strategies:MSDEMSDE
Supports (only) sockets (TCP/IP)—no named pipes
Freely distributable (with a Visual Studio or Office 2000 app)
Does not replace Microsoft Jet Database Engine (Jet)
Requires SQL Server 7.0 to run in “per-seat” licensing mode.
See – www.msdn.microsoft.com/vstudio/msde– www.betav.com
![Page 17: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/17.jpg)
Replicated DBReplicated DB
Enterprise Development Strategies:Enterprise Development Strategies:MSDEMSDE
New standard “portable” SS7 DBMS No need to use DAO and RDO/ADO Same features, limited capacity Same tools, interfaces, programming model Easily scale up to SQL Server 7.0 NT Write once--run “anywhere”
![Page 18: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/18.jpg)
Making best use of Making best use of Visual Basic 6.0 Tools: DEDVisual Basic 6.0 Tools: DED
Overview of DataEnvironment Technology
Introducing the Data View Window Introducing the DataEnvironment Object Solving problems with the DE
![Page 19: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/19.jpg)
Designers vs WizardsDesigners vs Wizards
Designers– RDO: UserConnection Designer– ADO: DataEnvironment Designer– Use dialogs to capture property settings– Create a binary .DSR file– Require a runtime– Construct data access COM objects at runtime
Wizards– Ask you a series of questions– Generate Visual Basic (editable) code
![Page 20: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/20.jpg)
DataEnvironment TechnologyDataEnvironment Technology
What is the “DE”?– Data Environment Designer created for VS 6.0– GUI tool for ADO development
What does it do?– DE Creates layer over ADO object interface
How do I use it?– Exposed as programmable “data source”– Bindable to data-aware
(ADO) controls
How can it help me?– Increases productivity– Builds team standard interface
![Page 21: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/21.jpg)
Introducing the Data View WindowIntroducing the Data View Window
What is the Data View Window?– Data Links persisted in Windows registry
Where is it exposed?– All Visual Studio tools
What is it used for?– Capture ADO Connection properties– Create DataEnvironment objects– View/create/modify:
schema, table(s), data, indexes, relationships, view(s), stored procedures, parameters…
Functionality based on provider
![Page 22: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/22.jpg)
Understanding the Understanding the Data View WindowData View Window
• Schema Diagrams
• Tables
• Views
• Stored Procedures
• (Project) DE Connections
New DE
New Data Link
![Page 23: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/23.jpg)
Creating a Data LinkCreating a Data Link
What’s a “Data Link”?– A persisted connection like a “DSN”
How do you create one?– Start Visual Basic 6.0– Click “Add New Data Link” icon– Fill in Connection properties
How do I prevent extra dialogs?– Do not simply point to DSN
for SQL Server or Oracle– Use ODBC or OLE DB provider
![Page 24: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/24.jpg)
Adding tables with a Adding tables with a Data LinkData Link
Create, Open DB Diagram
Right-click to – add table– add related tables– arrange tables
Fill in or copy/paste column properties
Drag “foreign” key to “primary” key
![Page 25: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/25.jpg)
Introducing the Introducing the DataEnvironment ObjectDataEnvironment Object
DataEnvironment persisted with VB project Saved as .DSR file
– Loadable by other team members
Masks ADO object model– DE contains one or more Connections– Connections contain one or more Commands– Commands have parameters, properties,
hierarchies
Managed via ADO properties, methods, events
![Page 26: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/26.jpg)
Understanding the Understanding the DataEnvironment ObjectDataEnvironment Object
Why should you use the DE?– To eliminate code–just like the UserConnection
Designer– To generate queries and stored procedures– To “correctly” generate parameters – To expose complex queries to the entire team
Where should you use the DE?– On the client–not really suitable for the middle tier– Where you want to increase developer productivity
Acts as “data source” control– Other controls can bind to DE– Position current row via rsxxx “Move” methods
![Page 27: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/27.jpg)
Creating a Creating a DataEnvironment ObjectDataEnvironment Object
Create new “Data” project Use Data Link icon
Next... Right-click Project menu Fill in Connection properties
![Page 28: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/28.jpg)
Setting DataEnvironment Setting DataEnvironment Connection PropertiesConnection Properties
Reference Properties - Connection window
Set properties not exposed in DE “wizard”– Command/
ConnectionTimeout– Cursor location (server/client)– Passwords (design/runtime)– Prompt behavior– Run/
DesignSaveAuthentication
![Page 29: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/29.jpg)
Using the Data EnvironmentUsing the Data Environment
Build Data Link with Data View Window Create DataEnvironment object Expand tables/views/stored procedures Drag to DE Set properties Tune query Drag to form Provide navigation controls, code Provide parameter controls, code
![Page 30: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/30.jpg)
Accessing ADO with the Accessing ADO with the DataEnvironment ObjectDataEnvironment Object
DE object internals Exposed as ADO objects
Drag tables/views/SPs from DE Connections lists
![Page 31: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/31.jpg)
DataEnvironment Object DataEnvironment Object exposes ADO objectsexposes ADO objects
DataEnvironment objectDataEnvironment object
Expenses “query”Expenses “query”
CostCost “field” “field”
ADO objectADO object
DataEnvironment1DataEnvironment1 Connection Connection
ExpensesExpenses method/Command method/Command
Recordset Field objectRecordset Field object
rsExpensesrsExpenses Recordset RecordsetBindable result setBindable result set
Expenses.Parameters(.)Expenses.Parameters(.)ParametersParameters
![Page 32: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/32.jpg)
Binding to Data Aware ControlsBinding to Data Aware Controls
Setting default controls by datatype– DataEnvironment
“Options /Field Mapping”
– Set control type for each datatype
Using the DOW to build UserControls
![Page 33: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/33.jpg)
Binding Options:Binding Options:DataGridDataGrid
![Page 34: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/34.jpg)
Binding Options:Binding Options:HierarchicalFlex GridHierarchicalFlex Grid
![Page 35: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/35.jpg)
Binding Options:Binding Options:Bound ControlsBound Controls
![Page 36: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/36.jpg)
DataEnvironment QueriesDataEnvironment QueriesTips and TechniquesTips and Techniques
Never use SELECT * Always use WHERE clause Choose the “right” (lightest weight)
cursor Limit rows to as few as possible
– Never over 200, usually < 100– Fetch More as needed
Update via stored procedure Use asynchronous options
![Page 37: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/37.jpg)
Hierarchical QueriesHierarchical Queries
In use everywhere– Customer-order-item
– System-component-subsystem-part
– State-Town-Street
DE uses ADO/OLE DB “Shape” provider
SHAPE {SELECT * FROM "dbo"."AuthorsView"}SHAPE {SELECT * FROM "dbo"."AuthorsView"}AS AuthorsView AS AuthorsView APPEND ({SELECT Title, Year_Published, ISBN, APPEND ({SELECT Title, Year_Published, ISBN, CoverFilename FROM Titles} AS titles RELATE ) CoverFilename FROM Titles} AS titles RELATE ) AS titlesAS titles
![Page 38: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/38.jpg)
Hierarchical QueriesHierarchical Queries
Observe “default” behavior with SQL Trace
SELECT * FROM "dbo"."Authors" SELECT * FROM "dbo"."Authors" SELECT * FROM "dbo"."Title_Author" SELECT * FROM "dbo"."Title_Author" SELECT * FROM "dbo"."Titles"SELECT * FROM "dbo"."Titles"
Be sure to limit focus of queries with views or focused queriesPassing in parameters is tough... But getting easier with ADO 2.1
![Page 39: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/39.jpg)
Stored ProceduresStored Procedures
Creating Debugging Installing
into the DE Passing
parameters Rebinding
![Page 40: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/40.jpg)
Creating Stored ProceduresCreating Stored Procedures
Use Data View window– Create Stored Procedure– Design (against existing SP)
Refresh DE after changes TSQL Debugger available to test Data Object Wizard (DOW) Use SQL Enterprise Manager … Use Visual InterDev Data Tools Code by hand
![Page 41: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/41.jpg)
Installing SPs into the DEInstalling SPs into the DE
Drag from Data View window (DE explorer)
Insert Command under Connection– Set Command type– Point to SP
Set properties, but not parameter Value
![Page 42: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/42.jpg)
Passing Parameters toPassing Parameters to Stored Procedures Stored Procedures
Don’t set in Data Environment Command properties...– Becomes a permanent part of query
Set while invoking Command as DE method Recordset passed as new rsXXXXX object
Set de = DataEnvironment1Set de = DataEnvironment1de.AuthorsByYearBorn Text1.Text, Text2.textde.AuthorsByYearBorn Text1.Text, Text2.textShowADOData rsAuthorsByYearBornShowADOData rsAuthorsByYearBorn
![Page 43: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/43.jpg)
Passing Parameters toPassing Parameters toStored ProceduresStored Procedures
Set in WillExecute event Parameter name depends on how query
is parsed. Note: this event called twice...
Private Sub Connection1_WillExecute...Private Sub Connection1_WillExecute... pCommand("@YearLow") = Form1.Text1pCommand("@YearLow") = Form1.Text1 pCommand("@YearHigh") = Form1.Text2pCommand("@YearHigh") = Form1.Text2End SubEnd Sub
![Page 44: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/44.jpg)
Passing Parameters toPassing Parameters to Stored Procedures (Subsequently) Stored Procedures (Subsequently)
No “Requery” available. Must Close and re-execute query … and rebind
With DataEnvironment1With DataEnvironment1 .rsAuthorsByYearBorn.Close.rsAuthorsByYearBorn.Close .AuthorsByYearBorn Text1, Text2.AuthorsByYearBorn Text1, Text2End WithEnd WithRebindControls Me, "TextBox,",RebindControls Me, "TextBox,", "AuthorsByYearBorn", "AuthorsByYearBorn", DataEnvironment1 DataEnvironment1
![Page 45: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/45.jpg)
Rebinding after Rebinding after Rebuilding RecordsetRebuilding Recordset
Sub RebindControls(FormName, ControlType As String, CommandName, DE)
Dim ctl As Control
For Each ctl In FormName.Controls
If InStr(ControlType & ",", TypeName(ctl) & ",") Then
If ctl.DataMember = CommandName Then
Set ctl.DataSource = DE
End If
End If
Next
End Sub
Recreating ADO Recordset requires rebinding to “simple” bound controls
“Complex” bound controls handled automatically
![Page 46: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/46.jpg)
Coding Move MethodsCoding Move Methods
Private Sub MoveNextButton_click()Private Sub MoveNextButton_click()On Error Resume NextOn Error Resume Next With DataEnvironment1.rsAuthorsByYearBornWith DataEnvironment1.rsAuthorsByYearBorn .MoveNext.MoveNext If .EOF ThenIf .EOF Then If Not .BOF Then .MoveLastIf Not .BOF Then .MoveLast End IfEnd If End WithEnd WithEnd SubEnd Sub
Default “simple” bound control binding does not include navigation controls
No drag-on control as in Visual InterDev
![Page 47: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/47.jpg)
Part IIPart II
Making best use of Visual Basic 6.0 Wizards…
![Page 48: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/48.jpg)
Data Object WizardData Object Wizard
Building procedure-based applications Constructing Procedures with Visual
Basic version 6.0 Building DOW-based Data classes Building DOW-generated UserControls
![Page 49: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/49.jpg)
Building procedure-based applicationsBuilding procedure-based applications
Why “procedure-based” designs?– Higher performance– Centralized management– Higher security
![Page 50: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/50.jpg)
Defining the problemDefining the problem
Data retrieval by key– Parameter-driven query– From any ADO data source
Resultsets displayed, manipulated by – Row – Grid – ComboBox
Updateable via procedure– Stored procedures– User-written ADO / DE Commands
![Page 51: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/51.jpg)
UserControl
Data Object Wizard Data FlowData Object Wizard Data Flow
Fetch Query
Update Query
Dat
a S
erve
r
Data Class
![Page 52: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/52.jpg)
Constructing the DOW proceduresConstructing the DOW procedures
Data retrieval...by key or “all” Update...by key Delete...by key Insert… by key or using Identity Can substitute Update with Delete /
Insert SQL Server? Remember your triggers Data lookup procedure
![Page 53: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/53.jpg)
Data Retrieval ProcedureData Retrieval Procedure
Create Procedure Publisher_Fetch Create Procedure Publisher_Fetch @StateWanted varchar(10)@StateWanted varchar(10)
AsAsif @StateWanted = 'all' if @StateWanted = 'all' Begin Begin Select * from publishers Select * from publishers where State is not nullwhere State is not nullEndEndElseElseBeginBegin Select * from Publishers Select * from Publishers where State = @StateWanted where State = @StateWanted and State is not nulland State is not nullEndEndReturnReturn
![Page 54: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/54.jpg)
Data Retrieval ProcedureData Retrieval Procedure
Returns rows based on primary key fetch (Requires) parameter query Limit scope of query to about 100 rows Pass parameter via
– UserControl property– FormLoad or Fetch procedure
Run automatically unless ManualInitialize=true
Do not use last field to fetch lookup value
![Page 55: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/55.jpg)
Data Update ProcedureData Update Procedure
Create Procedure Publisher_Update Create Procedure Publisher_Update @PubID Int,@Name Varchar(50),@Company_Name varchar(255), @PubID Int,@Name Varchar(50),@Company_Name varchar(255), @Address varchar(50), @City varchar(20), @Address varchar(50), @City varchar(20), @State varchar(15),@Zip Varchar(15), @Telephone @State varchar(15),@Zip Varchar(15), @Telephone varchar(15),@Fax Varchar(15)varchar(15),@Fax Varchar(15)AsAsUpdate PublishersUpdate Publishersset Name= @Name, Company_Name=@Company_Name, set Name= @Name, Company_Name=@Company_Name, Address=@Address, City=@City, State=@State, Address=@Address, City=@City, State=@State, Zip=@Zip, Telephone=@TelePhone, Zip=@Zip, Telephone=@TelePhone, Fax=@Fax Fax=@Fax where PubID = @PubIDwhere PubID = @PubIDreturnreturn
![Page 56: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/56.jpg)
Data Update ProcedureData Update Procedure
Updates row based on primary key match
Setup with parameter query All columns passed in--except Primary
Key Executed automatically when bound
data changes DOW Option to use Delete/Insert
instead
![Page 57: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/57.jpg)
Data Delete ProcedureData Delete Procedure
Create Procedure Publisher_Delete Create Procedure Publisher_Delete (@PubID Integer)(@PubID Integer)AsAsDelete Publishers where PubID = @PubIDDelete Publishers where PubID = @PubIDreturnreturn
![Page 58: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/58.jpg)
Data Delete ProcedureData Delete Procedure
Deletes row based on primary key match
Setup with parameter query Executed automatically when
– Bound data row is deleted– Or before insert when no update procedure
exists
![Page 59: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/59.jpg)
Data Insert ProcedureData Insert Procedure
Create Procedure Publisher_Insert Create Procedure Publisher_Insert
@Name varchar(50), @Company_Name varchar(255), @Name varchar(50), @Company_Name varchar(255),
@Address varchar(50), @City varchar(20), @Address varchar(50), @City varchar(20),
@State varchar(15), @Zip varchar(15),@State varchar(15), @Zip varchar(15),
@Telephone varchar(15), @Fax varchar(15)@Telephone varchar(15), @Fax varchar(15)
AsAs
INSERT INTO PublishersINSERT INTO Publishers
( Name, Company_Name, Address, City, ( Name, Company_Name, Address, City,
State, Zip, Telephone, Fax)State, Zip, Telephone, Fax)
VALUES ( @Name, @Company_Name, @Address, VALUES ( @Name, @Company_Name, @Address,
@City, @State, @Zip, @Telephone, @Fax)@City, @State, @Zip, @Telephone, @Fax)
return @@identityreturn @@identity
![Page 60: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/60.jpg)
Data Insert ProcedureData Insert Procedure
Inserts a new row via parameter query Procedure can return new @@Identity
rowID Executed automatically when
– Bound data row is added– Row is updated and no update procedure exists
![Page 61: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/61.jpg)
DemonstrationDemonstration
Creating Stored Procedures in Visual Basic version 6.0
![Page 62: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/62.jpg)
Connecting to the data sourceConnecting to the data source
Create New Data Environment (DE)
Delete existing Connection1
Drag Procedures to DE from Data View window
(or) Insert SPs No, don’t “Execute”
procedures
![Page 63: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/63.jpg)
Building Procedure-based Building Procedure-based Data ClassesData Classes
Start Visual Basic 6.0 with “Data” project Install DOW Addin Build Data Class Point to Procedures,
Queries
Tip: Use SQLTrace or SQL Profiler to see what ADO, DE, and DOW are doing...
![Page 64: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/64.jpg)
Building DOW-based Data ClassesBuilding DOW-based Data Classes
Tell DOW how to fetch individual rows– Point to fetch procedure (PublisherFetch)
![Page 65: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/65.jpg)
Building DOW-based Data ClassesBuilding DOW-based Data Classes
Tell DOW which field(s) contain the unique key– Set Primary Key on Select procedure
![Page 66: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/66.jpg)
Building DOW-based Data ClassesBuilding DOW-based Data Classes
Tell DOW how to insert, update, delete rows– Point to change procedures
![Page 67: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/67.jpg)
Building DOW-based Data ClassesBuilding DOW-based Data Classes
Tell DOW how to name your DataClass– Overlays existing class… permanently
![Page 68: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/68.jpg)
Building DOW-Generated UserControlsBuilding DOW-Generated UserControls
Point to DOW-generated Data class Choose Single Row, Data Grid, Combo
Boxes Choose VB control type for each field For Single Row and Combo Box add...
– Row navigation code– Add, delete buttons– Parameter capture code
![Page 69: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/69.jpg)
Install UserControlInstall UserControl
Provide parameter for fetch query– Set UserControl parameter property– Via Code and Init<Data class> method– Set ManualInitialize property as required
Uct<cls>DataGrid1.<parmname> = <parm source>Uct<cls>DataGrid1.<parmname> = <parm source>uct<cls>DataGrid1.Init<cls>uct<cls>DataGrid1.Init<cls>
uctPublishersDataGrid1.dbo_Publisher_FetchStateWanted = StateWanted.TextuctPublishersDataGrid1.dbo_Publisher_FetchStateWanted = StateWanted.TextuctPublishersDataGrid1.InitPublishersuctPublishersDataGrid1.InitPublishers
Set GridEditable = TrueRun it!
![Page 70: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/70.jpg)
DemonstrationDemonstration
Creating a simple DOW-based UserControl
![Page 71: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/71.jpg)
Adding a Lookup dropdownAdding a Lookup dropdown
Lookup valid entries for selected fields Substitute underlying coded entries
– In displayed UserControl columns– In Inserted, Updated procedures
Create Lookup DE Command– Add to project DE Commands– Might just be a Table (if it’s small)
![Page 72: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/72.jpg)
Adding a Lookup dropdownAdding a Lookup dropdown
Creating a Lookup DE Command– Choose Recordset source field (from fetch query)– Specify Lookup Command --> ValidStates table– Specify Display field from Lookup Command– Specify Lookup on Fields column from Lookup
Command
![Page 73: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/73.jpg)
Adding a Lookup dropdownAdding a Lookup dropdown
Map Source Command Fields to Lookup Command Fields– Source.State --> Lookup.StateCode
Set GridEditable property to True
![Page 74: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/74.jpg)
DemonstrationDemonstration
Creating a DOW-based UserControl with Lookup column
![Page 75: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/75.jpg)
Part IIIPart III
Implementing ADO Solutions
![Page 76: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/76.jpg)
Implementing ADO Solutions:Implementing ADO Solutions:Effective Queries Effective Queries
![Page 77: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/77.jpg)
Referencing the object ValueReferencing the object Value
Dim Rs as ADODB.Recordset ' ambiguity
...
Rs(0) ' by ordinal
Rs.Fields(0).Value ' explicitly
Rs.Fields(intF).Value ' by variable
Rs(intF) ' by variable
Rs!MyFieldName ' by name
Rs.Fields!Author.Value ' Bang field
Rs("MyFieldName") ' by name
Rs(A$) ' by variable
Speed
Speed
Slowest
Fastest
![Page 78: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/78.jpg)
Referencing Recordset FieldsReferencing Recordset Fields
![Page 79: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/79.jpg)
Referencing Recordset FieldsReferencing Recordset Fields
Use Comments
Use Enumerations
Rs(3) ' Part assembly number
Rs(eRsFld.PartAssy)
Enum eRsFldPartNamePartIDPartAssy
End Enum
![Page 80: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/80.jpg)
Implementing ADO Solutions:Implementing ADO Solutions:Processing Updates Processing Updates
Changing Recordset Data Handling collisions Update strategies Dealing with the errors
![Page 81: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/81.jpg)
Changing Recordset DataChanging Recordset Data
Use Updatable cursor to – Add rows: Use AddNew method
Creates empty row (at end of Recordset)– Change existing rows: Use Update method
No need to use Edit method as in DAO/RDODelete current row: Use Delete method
– Specify delete scope with AffectRecords argument
Use Update method to commit (local) change– Change permanent in “C/S” (non-batch) mode
Use UpdateBatch method to save to DB
![Page 82: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/82.jpg)
Changing Recordset DataChanging Recordset Data
Use SQL action queries to change Recordsets– INSERT statement to add rows – UPDATE statement to change rows– DELETE statement to drop rows
Create WHERE clause using primary key Don’t change primary key
– Use DELETE, INSERT instead
![Page 83: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/83.jpg)
Handling CollisionsHandling Collisions
What can go wrong?– Row changed by another user (or yourself?)– Row deleted– Row “moved”—Primary key changed– Delete fails due to any of the above– Insert fails due to primary key violations– Update or insert fails to field validations– … and many others
3 Copies of your field(s) now available– Was– Is (maybe gone)– Wanna be
![Page 84: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/84.jpg)
Update StrategiesUpdate Strategies
Get, Show Row(s)Get, Show Row(s)Get, Show Row(s)Get, Show Row(s)
Capture ChangesCapture ChangesCapture ChangesCapture Changes
Post to RSPost to RS(EditMode)(EditMode)
Post to RSPost to RS(EditMode)(EditMode)
Stored ProcStored ProcStored ProcStored Proc Client-ManagedClient-ManagedCollisionsCollisions
Client-ManagedClient-ManagedCollisionsCollisions
SQL SQL or Cursor?or Cursor?
SQL SQL or Cursor?or Cursor?
![Page 85: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/85.jpg)
Client-Managed CollisionsClient-Managed Collisions
EnableEnableOn Error GoToOn Error GoTo
EnableEnableOn Error GoToOn Error GoTo
RS.UpdateRS.UpdateRS.UpdateRS.Update
Case (Collision)Case (Collision)Case (Collision)Case (Collision)
Locate changedLocate changedFieldsFields
Locate changedLocate changedFieldsFields
??Force Force throughthrough
AcceptAcceptDBDB
RevertRevert
DoneDone
![Page 86: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/86.jpg)
Using “Unique” PropertiesUsing “Unique” Properties
Unique Table– Specifies base table
Unique Schema– Specifies table owner (or schema)
Unique Catalog– Specifies database
Helps provide specific-table updatability against complex (multi-table) queries
![Page 87: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/87.jpg)
Setting the “Update Criteria” PropertySetting the “Update Criteria” Property
Determines how ADO constructs UpdateWHERE clause– ID + all Columns– ID + Just “touched” columns (changed or not)– ID + Just TIMESTAMP– Just ID (Key)
Client-side cursors only Set before RS.Open in ADO 2.0 Set before or after RS.Open in ADO 2.1
![Page 88: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/88.jpg)
Checking for Update ErrorsChecking for Update Errors
How to tell that the update failed What to do about it Examine the rs.Status property Choosing from the 3 sets of data
– Original data from query (the way it was)– The revised data on your system (the way you
want it)– The revised data on the server (the way someone
else wants it)
![Page 89: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/89.jpg)
Finding what ChangedFinding what Changed
Set the “Update Resync” property (2.1 or later)– adResyncConflicts – retrieves latest values from
DB’s copy of row– Next Update tries to apply these values
OriginalValue = pre-changes from RS CurrentValue = current DB field value
![Page 90: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/90.jpg)
Dealing with the ErrorsDealing with the Errors
Force through your changes—adArrogant
Accept changes already made—adPassive
Revert back to original values—adUncertain
Be prepared for other changes User should not (ideally) be involved
![Page 91: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/91.jpg)
Data Access StrategiesData Access Strategies
Building Success into your apps–Use Stored Procedures–Fewer cursors, fewer rows::more scalability
–Use light-weight cursors–Use GetRows, GetClipString–Move logic out of the client into the middle tier or on to the server
–Avoid VBBLOBs–Avoid API approaches
![Page 92: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/92.jpg)
Visual Basic 6.0 far more “data aware” Makes ambitious strides toward higher
productivity New data tools make development easier
SummarySummary
![Page 93: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/93.jpg)
Questions?Questions?
![Page 94: Accessing Data Sources with Visual Basic 6.0 William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer 12](https://reader035.vdocuments.us/reader035/viewer/2022062716/56649dc35503460f94ab6373/html5/thumbnails/94.jpg)