accessing data sources with visual basic 6 0 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Accessing Data Sources with Visual Basic 6.0 PowerPoint Presentation
Download Presentation
Accessing Data Sources with Visual Basic 6.0

Loading in 2 Seconds...

play fullscreen
1 / 94

Accessing Data Sources with Visual Basic 6.0 - PowerPoint PPT Presentation


  • 328 Views
  • Uploaded on

Accessing Data Sources with Visual Basic 6.0. 12. William R. Vaughn Microsoft Technical Education (MSTE) Developer Trainer. William R. Vaughn. Microsoft Corporation Author: Hitchhiker’s Guide to Visual Basic and SQL Server Microsoft Press, 1996, 1997, 1998 billva@microsoft.com.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Accessing Data Sources with Visual Basic 6.0' - ivory


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
accessing data sources with visual basic 6 0

Accessing Data Sources with Visual Basic 6.0

12

William R. Vaughn

Microsoft Technical Education (MSTE)

Developer Trainer

slide2

William R. Vaughn

Microsoft Corporation

Author:

Hitchhiker’s Guide to Visual Basic

and SQL Server

Microsoft Press, 1996, 1997, 1998

billva@microsoft.com

agenda
Agenda

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

enterprise development strategies sql server 7 0
Enterprise Development Strategies:SQL 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
enterprise development strategies sql server 7 01
Enterprise Development Strategies:SQL 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
enterprise development strategies sql server 7 02
Enterprise Development Strategies:SQL 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
enterprise development strategies sql server 7 03
Enterprise Development Strategies:SQL 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
sql server 6 5 cache management
SQL Server 6.5 Cache management

Stored Procedures

SQL Server

Procedure Cache

4

4

1st. instance is loaded

2nd. instance loaded

1st. instance finishes

3rd. instance re-uses first

sql server 7 0 cache management
SQL Server 7.0 Cache management

Stored Procedures

SQL Server

Managed Memory

4

4

1st. instance is loaded

2nd. instance shares first

1st. instance finishes

3rd. instance shares first

Data Pages

sql server 7 0 ad hoc caching
SQL Server 7.0 Ad 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)

Query2: Insert MyTable values(2,0)

Query3: Insert MyTable values(1,0)

auto parameterizations
Auto parameterizations
  • Engine guesses constants are parameters
  • Similar parameters share same plan
  • All three use the same cached plan created with Q1.

Q1: Select * from employees where emp_id = 1000

Q2: Select * from employees where emp_id = 5

Q3: Select * from employees where emp_id = 1000

intelligent caching
Intelligent Caching
  • Tips:
    • Know what the parameters are?
      • Mark them (?)
      • Build parameters collection (in RDO/ADO)
    • Continue to use SPs
      • They just run faster
    • SPs are now re-entrant
      • Which makes them even faster
enterprise development strategies sql server desktop edition
Enterprise Development Strategies:SQL Server Desktop Edition
  • Microsoft SQL Server Desktop Edition
  • Runs on Windows 95, 98, 2000, NT 4.0 Workstation/Server
  • Duplicates functionality in coreNT 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
enterprise development strategies msde
Enterprise Development Strategies:MSDE
  • Microsoft Database Engine (MSDE)
  • Runs on Windows 95, 98, 2000, NT 4.0 WS/Svr
  • Duplicates functionality in coreNT SQL Server 7.0 (no OLAP, EQ, FTS etc.)
  • Performance “purposefully degraded”
  • Database limited to 2GB
  • Supplied through
    • Download/CD (now)
    • Office 2000
enterprise development strategies msde1
Enterprise Development Strategies:MSDE
  • Visual Basic 6.0 “Plus” Pack
    • Includes SQL Server Developer Edition
      • To setup databases
      • To create permissions
      • Perform 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
enterprise development strategies msde2
Enterprise Development Strategies:MSDE
  • 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
enterprise development strategies msde3
Enterprise Development Strategies:MSDE

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”

Replicated DB

making best use of visual basic 6 0 tools ded
Making best use of Visual Basic 6.0 Tools: DED
  • Overview of DataEnvironment Technology
  • Introducing the Data View Window
  • Introducing the DataEnvironment Object
  • Solving problems with the DE
designers vs wizards
Designers 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
dataenvironment technology
DataEnvironment 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
introducing the data view window
Introducing 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
understanding the data view window
Understanding the Data View Window

New Data Link

  • Schema Diagrams

New DE

  • Tables
  • Views
  • Stored Procedures
  • (Project) DE Connections
creating a data link
Creating 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
adding tables with a data link
Adding tables with a Data 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
introducing the dataenvironment object
Introducing the DataEnvironment 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
understanding the dataenvironment object
Understanding the DataEnvironment 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
creating a dataenvironment object
Creating a DataEnvironment Object
  • Create new “Data” project
  • Use Data Link icon

Next...

  • Right-click Project menu
  • Fill in Connection properties
setting dataenvironment connection properties
Setting DataEnvironment Connection 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
using the data environment
Using 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
accessing ado with the dataenvironment object
Accessing ADO with the DataEnvironment Object

DE object internals Exposed as ADO objects

Drag tables/views/SPs from DE Connections lists

dataenvironment object exposes ado objects

DataEnvironment object

ADO object

DataEnvironment1

Connection

Expenses “query”

Expenses method/Command

Recordset Field object

Cost “field”

Bindable result set

rsExpenses Recordset

Parameters

Expenses.Parameters(.)

DataEnvironment Object exposes ADO objects
binding to data aware controls
Binding 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
dataenvironment queries tips and techniques
DataEnvironment QueriesTips 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
hierarchical queries
Hierarchical 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"}AS AuthorsView APPEND ({SELECT Title, Year_Published, ISBN, CoverFilename FROM Titles} AS titles RELATE ) AS titles

hierarchical queries1
Hierarchical Queries
  • Observe “default” behavior with SQL Trace

SELECT * FROM "dbo"."Authors" SELECT * FROM "dbo"."Title_Author" SELECT * FROM "dbo"."Titles"

Be sure to limit focus of queries with

views or focused queries

Passing in parameters is tough... But getting easier with ADO 2.1

stored procedures
Stored Procedures
  • Creating
  • Debugging
  • Installing into the DE
  • Passing parameters
  • Rebinding
creating stored procedures
Creating 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
installing sps into the de
Installing 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
passing parameters to stored procedures
Passing Parameters to 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 = DataEnvironment1

de.AuthorsByYearBorn Text1.Text, Text2.text

ShowADOData rsAuthorsByYearBorn

passing parameters to stored procedures1
Passing Parameters toStored Procedures
  • Set in WillExecute event
  • Parameter name depends on how query is parsed.
  • Note: this event called twice...

Private Sub Connection1_WillExecute...

pCommand("@YearLow") = Form1.Text1

pCommand("@YearHigh") = Form1.Text2

End Sub

passing parameters to stored procedures subsequently
Passing Parameters to Stored Procedures (Subsequently)
  • No “Requery” available.
  • Must Close and re-execute query
  • … and rebind

With DataEnvironment1

.rsAuthorsByYearBorn.Close

.AuthorsByYearBorn Text1, Text2

End With

RebindControls Me, "TextBox,", "AuthorsByYearBorn", DataEnvironment1

rebinding after rebuilding recordset
Rebinding after Rebuilding Recordset
  • Recreating ADO Recordset requires rebinding to “simple” bound controls
  • “Complex” bound controls handled automatically

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

coding move methods
Coding Move Methods
  • Default “simple” bound control binding does not include navigation controls
  • No drag-on control as in Visual InterDev

Private Sub MoveNextButton_click()

On Error Resume Next

With DataEnvironment1.rsAuthorsByYearBorn

.MoveNext

If .EOF Then

If Not .BOF Then .MoveLast

End If

End With

End Sub

part ii

Part II

Making best use of Visual Basic 6.0 Wizards…

data object wizard
Data Object Wizard
  • Building procedure-based applications
  • Constructing Procedures with Visual Basic version 6.0
  • Building DOW-based Data classes
  • Building DOW-generated UserControls
building procedure based applications
Building procedure-based applications
  • Why “procedure-based” designs?
    • Higher performance
    • Centralized management
    • Higher security
defining the problem
Defining 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
data object wizard data flow

Fetch Query

Data Class

Data Server

UserControl

Update Query

Data Object Wizard Data Flow
constructing the dow procedures
Constructing 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
data retrieval procedure
Data Retrieval Procedure

Create Procedure Publisher_Fetch

@StateWanted varchar(10)

As

if @StateWanted = 'all'

Begin

Select * from publishers

where State is not null

End

Else

Begin

Select * from Publishers

where State = @StateWanted

and State is not null

End

Return

data retrieval procedure1
Data 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
data update procedure
Data Update Procedure

Create Procedure Publisher_Update

@PubID Int,@Name Varchar(50),@Company_Name varchar(255), @Address varchar(50), @City varchar(20),

@State varchar(15),@Zip Varchar(15), @Telephone varchar(15),@Fax Varchar(15)

As

Update Publishers

set Name= @Name, Company_Name=@Company_Name,

Address=@Address, City=@City, State=@State,

Zip=@Zip, Telephone=@TelePhone,

Fax=@Fax

where PubID = @PubID

return

data update procedure1
Data 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
data delete procedure
Data Delete Procedure

Create Procedure Publisher_Delete

(@PubID Integer)

As

Delete Publishers where PubID = @PubID

return

data delete procedure1
Data 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
data insert procedure
Data Insert Procedure

Create Procedure Publisher_Insert

@Name varchar(50), @Company_Name varchar(255),

@Address varchar(50), @City varchar(20),

@State varchar(15), @Zip varchar(15),

@Telephone varchar(15), @Fax varchar(15)

As

INSERT INTO Publishers

( Name, Company_Name, Address, City,

State, Zip, Telephone, Fax)

VALUES ( @Name, @Company_Name, @Address,

@City, @State, @Zip, @Telephone, @Fax)

return @@identity

data insert procedure1
Data 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
demonstration
Demonstration
  • Creating Stored Procedures in Visual Basic version 6.0
connecting to the data source
Connecting 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
building procedure based data classes
Building Procedure-based Data 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...

building dow based data classes
Building DOW-based Data Classes
  • Tell DOW how to fetch individual rows
    • Point to fetch procedure (PublisherFetch)
building dow based data classes1
Building DOW-based Data Classes
  • Tell DOW which field(s) contain the unique key
    • Set Primary Key on Select procedure
building dow based data classes2
Building DOW-based Data Classes
  • Tell DOW how to insert, update, delete rows
    • Point to change procedures
building dow based data classes3
Building DOW-based Data Classes
  • Tell DOW how to name your DataClass
    • Overlays existing class… permanently
building dow generated usercontrols
Building 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
install usercontrol
Install 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.Init<cls>

uctPublishersDataGrid1.dbo_Publisher_FetchStateWanted = StateWanted.Text

uctPublishersDataGrid1.InitPublishers

Set GridEditable = True

Run it!

demonstration1
Demonstration
  • Creating a simple DOW-based UserControl
adding a lookup dropdown
Adding 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)
adding a lookup dropdown1
Adding 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
adding a lookup dropdown2
Adding a Lookup dropdown
  • Map Source Command Fields to Lookup Command Fields
    • Source.State --> Lookup.StateCode
  • Set GridEditable property to True
demonstration2
Demonstration
  • Creating a DOW-based UserControl with Lookup column
part iii

Part III

Implementing ADO Solutions

referencing the object value
Referencing 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

Fastest

Speed

Slowest

referencing recordset fields1
Referencing Recordset Fields
  • Use Comments
  • Use Enumerations

Rs(3) ' Part assembly number

Rs(eRsFld.PartAssy)

Enum eRsFld

PartName

PartID

PartAssy

End Enum

implementing ado solutions processing updates
Implementing ADO Solutions:Processing Updates
  • Changing Recordset Data
  • Handling collisions
  • Update strategies
  • Dealing with the errors
changing recordset data
Changing 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/RDO
  • Delete 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
changing recordset data1
Changing 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
handling collisions
Handling 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
update strategies
Update Strategies

Get, Show Row(s)

Capture Changes

Post to RS(EditMode)

SQL or Cursor?

Stored Proc

Client-ManagedCollisions

client managed collisions
Client-Managed Collisions

EnableOn Error GoTo

Case (Collision)

RS.Update

Locate changedFields

?

AcceptDB

Force through

Done

Revert

using unique properties
Using “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
setting the update criteria property
Setting 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
checking for update errors
Checking 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)
finding what changed
Finding 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
dealing with the errors
Dealing 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
data access strategies
Data 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
summary
Summary
  • Visual Basic 6.0 far more “data aware”
  • Makes ambitious strides toward higher productivity
  • New data tools make development easier