ADO????

1
ADO????
2
ADO????

• ADO(ActiveX Data Object)??????????????????????????
  ??????????,????????,???Oracle?MS SQL
  Server?Access??

3
Active Data Objects ????
4
Connection ??

• ??????????,????????????,??????????
• Connection????ADO???????????,?????????????????????
  ??

5
Recordset

• ?????????,????????,?????SQL???Command?????????????
  ?
• Recordset??????SQL????????.?????????????????,?????
  ??????????

6
Command ??

• ??????????????????
• Command?????????(query),????????????(parameter),??
  ???????????Recordset???

7
Parameter

• ??????(Query)?????(Stored Procedure)???????
• Parameters?????????????SQL Server??Stored
  Procedure????????,??????????

8
Field??

• ?????????????????????
• Fields????????????????

9
Error??

• ?????,???????,??????????,??Error??
• Errors????????????(run-time errors)?????,?????????
  ???????

10

• ?????Sub MakeAConnection()   
  '????Connection?Error??    Dim cnn As
  ADODB.Connection    Dim errAdo As
  ADODB.Error    Set cnn New ADODB.Connection  
    '??????    On Error GoTo errHandle   
  '?????????????    With cnn        .Provider
  "Microsoft.Jet.OLEDB.4.0"       
  .ConnectionString "Data SourceC\Program
  Files\Microsoft Visual Studio\VB98\nwind.mdb"    
      .Open    End With
•     '??????,???????? '??????????,??????????,?????
  ?????    Debug.Print "??????"    Exit
  SuberrHandle    '???????,???For
  Each????????????    Dim str As String    For
  Each errAdo In cnn.Errors        str str
  errAdo.NativeError " " errAdo.Description
  vbCrLf    Next    MsgBox strEnd Sub

11
????????

• ?????ADO??????????VB???????????????????(error-hand
  ling)????????????????VB??????,????ADO?Errors?????,
  ??????????????
• ????????,???????????,???????Error??????????Errors?
  ???????????VB??????????????????

12
Property

• ??Connection ???Command???Recordset???Field???????
  ??

13
ADO?????

• ????
• ????
• ????
• ????
• ????
• ????
• ????

14
??ADO??????????

• 1.????(reference)?ADO Object Library2.??Connectio
  n??3.??OLE DB??????(data provider)4.??????
• ??????????,??????????Open????????

15
?????ADO Object Library

• 1.??????Project,???References
• 2.??Microsoft ActiveX Data Object X.X
  Library???OK

16
ADO???????

• STEP1??????
• STEP2??????????

17
??????

• Dim objvar as Class
• Objvar???????
• Class????
• ?
• Dim cn as ADODB.connection

18
ADO ???????
?? ????
Connection ADODB.Connetion
Error ADODB.Error
Command Command
Parameter ADODB.Parameter
Recordset ADODB.Recordset
Field ADODB.Field
Property ADODB.Property
19
??????????

• Set objvarNew Class
• objvar???????
• Class????
• ????New?????,???ADO??????
• ?
• Dim cn As ADODB.Connection
• Set cnNew ADODB.Connection

20
??OLE DB??????(data provider)

• ??????connection??,?????OLE DB??????(Data
  Provider)?????Connection???Provider??????
• ???????Microsoft SQL Server??????
• cnn.Provider "SQLOLEDB

21
?????????????
OLE DB Provider???? ????????
SQLOLEDB  Microsoft SQL Server
MSDASQL  ODBC
Microsoft.Jet.OLEDB.4.0 Microsoft Jet
MSIDXS  Microsoft Index Server
ADSDSOObject  Microsoft Active Directory Service
MSDAORA  Oracle
22
??????

• ??????????????????????Connection???ConnectionStrin
  g???Connection??????????????????????????????????,A
  DO??????

23
??????

• ???????????Microsoft SQL Server??????
• With cnn    .Provider "SQLOLEDB"   
  .ConnectionString "User IDsaPassword"
  _                                    "Data
  SourceUi" _                                  
    "Initial CatalogPubs"
• End With
• ?????????????Password????????sa??????????

24
??????????

• ??????????????,????Connection???Close????SQL
  Server??,??????,???????????????????,??????????????
  ??????,?????????????
• ??????????
• cnn.CloseSet cnn Nothing

25
??Connection????????

• Connection????????,????????????????????????????,??
  SQL???????(Transaction)????

26
??Connection????????
Connection?????????? ??
AbortTransaction  ?RollbackTrans????????
BeginTransaction  ?BeginTrans????????
CommitTransaction  ?CommitTrans????????
ConnectComplete  ????????????,??????????????
Disconnect  ??????????
ExecuteComplete  ?Execute????????
InfoMessage  ?OLE DB??????????????
WillConnect  ?Open?????,???????????
WillExecute  ?Execute?????,??????????????
27
??ADO??

• ????ADO???,???VB?WithEvents?????VB????????????????
  ???????WithEvents????,????VB???????????,??????????
  ????????

28
??ADO??
29

• ?????
• Private Sub cnnUi_ConnectComplete(ByVal pError
  As ADODB.Error, adStatus As ADODB.EventStatusEnum,
  ByVal pConnection As ADODB.Connection)    If
  adStatus adStatusOK Then        MsgBox
  "??????"    End IfEnd Sub

30
????????

• ??Command??
• ADO?Commmand??????????????,??????????????????????S
  tored Procedure
• ??Command????????????Connection??,?????????Conneci
  ton???????Command?????,???????????Connection??,???
  ???Connection??????????Command?Recordset???????

31
Command????
???? ??
ActiveConnection ???????????????Connection??
CommandText ????SQL??,stored procedure??,?????????
CommandType ????CommandText????SQL??,stored procedure??,?????????
Prepared ??????SQL???????????Stored Procedure
State ????commmand???????????????
32
Command????
???? ??
Cancel ?????????
CreateParameter ??parameter??(???Stored Procedure???)
Execute ??SQL??
33
???????Connection???Command

• Dim cnn As New ADODB.ConnectionWith cnn   
  ??Connection??????????.Provider
  "Microsoft.Jet.OLEDB.4.0"       
  .ConnectionString "Data SourceC\Program
  Files\Microsoft Visual Studio\VB98\nwind.mdb,Initi
  al CatalogpubsUser IDsaPassword"   
  .OpenEnd With
• Dim cmd As New ADODB.CommandWith cmd   
  '????????Connection??    .ActiveConnection
  cnn    '??????SQL??    .CommandText "UPDATE
  Titles SET PricePrice1.1"    '??Command   
  .ExecuteEnd With
• Set cmd Nothingcnn.CloseSet cnn Nothing

34
?????Command??

• Dim cmd As New ADODB.CommandWith cmd   
  '?????SQL ServerUi01???    .ActiveConnection
  "ProviderSQLOLEDBData
• Sourceui01Initial CatalogpubsUser
• DsaPassword"    .CommandText
  "Update Titles Set PricePrice1.1"   
  .ExecuteEnd With

35
??Recordset??

• Recordset?????????????????SQL?????????????????????
  ???????????,????SQL Server??Stored
  Procedure???recordset??,???????????,??????????????
  ??

36
(No Transcript)
37
??Recordset??

• ?????VB??????????Recordset??,?????????????????????
  ?????????????????????????????VB???????????????????
  ??????????,???????????????MS SQL
  Server?Oracle?Sybase????????Stored
  Procedure,??SQL (Structure Query
  Language)???????,??????????????????

38
Recordset????
Recordset???? ??
ActiveCommand ????Recordset???
ActiveConnection ???????????????Connection??
CursorLocation ?????????(cursor)???????,???adUseServer
CursorType ????????????,???adOpenForward
LockType ?????????(locking)???,???adLockReadOnly
MaxRecords ?????????
PersistFormat ???Recordset???Save???,???????(??ADO 2.0????adPersistADTG???,????????????)
RecordCount ????recordset??????????
State ????recordset?????
39
Recordset????
Recordset???? ??
Open ????SQL??,???????
Close ??Recordset??(??????????,???????????????Nothing)
Requery ????SQL?????Recordset??
Resync ??Recordset????????
Save ????Recordset??????????????,????????
40
??Connection???Command?????Recordset??

• ?????????,???????????????ADO?????????????????Recor
  dset,??connection???Execute????????

41

• Dim cmd As New ADODB.CommandDim cnn As New
  ADODB.ConnectionDim rec As New
  ADODB.RecordsetWith cnn    '?????SQL
  ServerUi01???    . .Provider
  "Microsoft.Jet.OLEDB.4.0"       
  .ConnectionString "Data SourceC\Program
  Files\Microsoft Visual Studio\VB98\nwind.mdb"   
  .OpenEnd WithWith cmd    '??Command?SQL??   
  .ActiveConnection cnn    .CommandText
  "SELECT FROM titles"    '??Command???Execute???
  SQL Server???????Recordset??    Set rec
  .ExecuteEnd WithDim i As IntegerWith rec   
  While Not .EOF        For i 0 To .Fields.Count
  - 1            '?????            Debug.Print
  .Fields(i) " "        Next i       
  Debug.Print        .MoveNext    WendEnd With

42
??Recordset?????Open??

• Dim cnn As New ADODB.ConnectionDim rec As New
  ADODB.RecordsetWith cnn    '?????SQL
  ServerUi01???    .Provider "Microsoft.Jet.OLED
  B.4.0"        .ConnectionString "Data
  SourceC\Program Files\Microsoft Visual
  Studio\VB98\nwind.mdb"    .OpenEnd WithWith
  rec
•     '???????
•     .CursorLocation adUseClient    .CursorType
  adOpenKeyset    .LockType adLockOptimistic 
     .Open "SELECT FROM titles", cnnEnd With

43
??Connection???Execute????Recordset??

• ???????
• Dim cnn As New ADODB.ConnectionDim rec As New
  ADODB.RecordsetWith cnn    '?????SQL
  ServerUi01???    . .Provider
  "Microsoft.Jet.OLEDB.4.0"       
  .ConnectionString "Data SourceC\Program
  Files\Microsoft Visual Studio\VB98\nwind.mdb"   
  .Open    Set rec .Execute("SELECT FROM
  titles")End With

44
?????????

• ???????????Recordset?????????????,?????????????(co
  ntrol)????Text box?Text??,???????????????DataSourc
  e?DataField????????Recordset????(Data Binding)?

45
??Recordset????????

• ???????
• Dim rec As New ADODB.RecordsetWith rec   
  .Open "SELECT FROM titles", "ProviderSQLOLEDBD
  ata Sourceui01Initial CatalogpubsUser        
  IDsaPassword", adOpenKeyset,
  adLockOptimisticEnd WithWith rec    While Not
  .EOF        '?????        Debug.Print rec!Title
  " " rec.Fields("price")       
  .MoveNext    WendEnd With
• ????VB??????TextBox?????
• Text1.Text rec!TitleText2.Text rec!price

46
??????Recordset??

• Visual Basic?????????????????(??????????data-aware
  control)??????ADO?Recordset???????????????ActiveX
  Data Control?????????????Recordset?????????,?????
  ???????????????
• ??????
• Set Text1.DataSource recText1.DataField
  "title"Set Text2.DataSource
  recText2.DataField "price"

47
??Recordset??????

• ?ADO?????????,??Recordset?????????????????????????
  ???????????,????????????

48
Recordset???????????
???? ??
AbsolutePage ??????????????(absolute)????(page)
AbsolutePosition ??????????????(absolute)??(?????????????
BOF ?????????????????????
Bookmark ?????????????,?????????????????????
EOF ??????????????????????
49
Recordset???????????
???? ??
Move ???????????????????
MoveFirst ????????????
MoveLast ?????????????
MoveNext ????????????
MovePrevious ????????????
??????????(CursorType )?adForwardOnly,????MoveNex
t??
50
???????

• Dim rec As New ADODB.Recordset
• Private Sub cmdFirst_Click()    '???????   
  rec.MoveFirstEnd SubPrivate Sub
  cmdLast_Click()    '????????   
  rec.MoveLastEnd SubPrivate Sub cmdNext_Click()
•     '????????    With rec       
  .MoveNext        '?????????????,??????????????? 
         If .EOF Then            .MoveLast       
  End If    End WithEnd Sub

51

• Private Sub cmdPrevious_Click()    '????????   
  With rec        .MovePrevious       
  '????????????,??????????????        If .BOF
  Then            .MoveFirst        End If   
  End WithEnd SubPrivate Sub Command1_Click()   
  '??????    With rec        .Open "SELECT
  FROM titles", "ProviderSQLOLEDBData
  Sourceui01Initial CatalogpubsUser
  IDsaPassword", adOpenKeyset,
  adLockOptimistic    End With    Set
  Text1.DataSource rec    Text1.DataField
  "title"    Set Text2.DataSource rec   
  Text2.DataField "price"End Sub

52
???????

• ??Recordset???Sort??,??????????Recordset??????????
  ???????????????,??????????????????????????????????
  ???
• ??????Microsoft SQL Server??Recordset???Sort??,??
  ??????????(.CursorLocation adUseClient)?????????
  ??????????????,?SQL Server?????
• ??Sort???,??????ASC?DESC?????????????????Sort?????
  ,????SQL???Order???

53

• ???????title????????
• rec.Sort "title ASC"
• ???????
• ????????????Sort??????????("")??
• ???????
• rec.Sort ""

54
????

• ??Filter??,????????Recordset?????????recordset????
  ?????????,?????????????????????Filter?????,????SQL
  ???Where???
• ????????,???????,???????c???????
• rec.Filter "title Like 'c'"
• ??????
• ???????????Recordset???????,???Filter????adFilterN
  one????
• ???????
• rec.Filter adFilterNone

55
????

• ??Filter??????????????????,??????????????,???Find?
  ?,?????????????
• Find???????????? Recordset????????,? Recordset
  ???????????????,???????? Recordset ????
• ??
• Find (criteria, SkipRows , searchDirection ,
  start)
• ???????,??????????????last_name?'White'????
• rec.Find "au_lname'White'"
• ?????????,?????(')???,????????,?????()????

56
????Find????????
???? ??
Criteria ????????????????,?????????????? String
SkipRows ?????? Long ?,?????,?????????? start ??????????
searchDirection ????? SearchDirectionEnum ?,??????????????,????????????????????????? adSearchForward ? adSearchBackward????????? Recordset ??????,?? searchDirection ????
Start ?????? Variant ??,???????????
57

• ???????????FieldName?????Operator,lt,gt,likeValue??
  ??????????

?????? ?????
FieldName ?????
Operator ,lt,gt,like
Value ????????????
?????Find???Sort?Filter??????,??????????
58
????

• ?Execute??
• ?????????????,?????SQL????Recordset??(????????????
  ?)??????????????,??SQL?????????
• ?????Connection?Command?Execute?????SQL??,????SQL?
  ?????????,???Connection????????????SQL?????????,??
  ???Comand???

59
??????

• ???????
• Dim cnn As ADODB.ConnectionSet cnn New
  ADODB.ConnectionWith cnn    .ConnectionString
  "ProviderSQLOLEDBData Sourceui01Initial
  CatalogpubsUser IdsaPassword"    .Open
•     '??SQL Insert??    .Execute "INSERT INTO
  authors(au_id,au_lname,au_fname,contract) VALUES
  ('111-11-1111','Green','Blue',0)"End With

60
????

• ????????????????????????,?????SQL?Update??
• ???????
• .Execute "UPDATE authors SET au_lname'black'
  WHERE au_id'111-11-1111'"

61
????

• ????????????????????,????SQL?Delete??
• ???????
• .Execute "DELETE FROM authors WHERE
  au_id'111-11-1111'"

62
??Recordset??

• ??Recordset??????????????????????????????????????C
  onnection????SQL?????????????????????
• ??????
• ??Recordset??????????????????AddNew???????????,???
  ????Update??????????????
• ??Recordset???CursorType?LockType?????????,??????
  ??AddNew?Update??Delete???

63
???????

• With rec    .CursorLocation adUseClient   
  .CursorType adOpenKeyset    .LockType
  adLockOptimistic    .Open "SELECT
  au_id,au_lname,au_fname,contract FROM authors",
  "ProviderSQLOLEDBData Sourceui01Initial
  CatalogpubsUser IdsaPassword"   
  .AddNew    .Fields(0) "111-11-1111"   
  .Fields(1) "Black"    .Fields(2) "Blue"   
  .Fields(3) 0    .UpdateEnd With
• ???AddNew??????????CancelUpdate??,???Update???????
  ?????

64
??????

• ???????????Recordset???Delete??,????????????????,?
  ??Update????
• ???????
• rec.Delete
• ????????,??????????????????,??????????,???????????
  ?EOF????

65
???????

• With rec        .MoveNext       
  '?????????????,???????????????        If .EOF
  Then            .MoveLast        End If    End
  With

66
??????

• ????????????????????????????????????????,????????,
  ??Update?????????
• ???DAO?RDO????,ADO?Recordset??????????Edit??

67
???????

• With rec    .Fields(0) "222-11-1111"   
  .UpdateEnd With