?13? VB?????

1
?13? VB?????
????!!!
2
????VB??????????

• VB???????????
• VB????????????
• ??????????????

3
VB????????

• Access?????(Windows Office??,?.mdb??????)?
• ??????dBASE, Foxpro???????????,VB?????Excel??????
  ?????
• ODBC?????????????????(ODBC)????, ? SQL
  Server, Oracle, Sybase???????

4
??????

• VB????????????????
• DataGrid?????(???)
• ??ADO???????

5
????????????
VB ?? ??
???
????
????????????
6
VB????????

• ???????(VB?????????,?????,?????????)
• ???????????(????)
• ??????,DAO??(??)
• ActiveX??????,ADO??(???)

7
VB????????

• ????????,???????????????????????

8
??????????

• ????????????,????????????????
• ?????????????????????????
• ????????VB??????

9
13.1 ???????(VB??)

• ?????Access??????,??????,??VB???????,?????????
• ??????
• ?????
• ????????
• ??????????
• ????????
• ?????????????????????????????

10
13.1.1 ??????? ?????

• ??????????????????
• ??
• ???????,??????????
• ?????????
• ?????????,????????????,????????,?????????
• ??????????,???????
• ????,????????,??????

11
13.1.2 ??????? ?????

• ??????????,?????????,
• ???????????????????
• ?????????
• ?????????????
• ?????????????,????????????????
• ?????????,?????,??????,?????????,?????????????

12
13.1.3 ??????? ?????

• ??????,??????????????????????,????????????,???????
  ??????,???????????????????
• ???????(??????)
• ??Data????
• ???Data????
• ??DBGrid????(??)

13
13.1.4 ??????? ?????

• ???????,?????????????????
• ?????????
• ?????????(?????)
• ??Data????
• ???Data????
• ??DBGrid????(??)

14
13.1.5 ??????? ?????

• ??????,??????????
• ?SQL?????????Select??
• ?????????????

15
13.1.6 ??????? ????

• ????????????/??
• ?????(???????????????????,??????,????)
• ?????(?????????)

16
?????????
??
??????
??ADO??
??Recordset??
????VB????
17
13.2 ???????????

• ???????
• Data??(?)
• ADO????(?) ?
• ?????? ?

18
13.2.1 ADO????

• ADO????????????????? ??,??????????????,??????
  ??,??????
• ???????? ?????Microsoft ADO Data Control
  13.0

19
ADO??????????

• ??????????????
• ?????????(????????????????????,??????),?????SQL???
  ?
• ??????????????, ??????????????,?????(????????)
• ?????

20
ADO?????????(??)

• ConnectionString??
• ??????,????????
• ??????????????(?????????),????????,?????????
• ?????
• OLE DB??(.UDL)
• ODBC???(DSN)
• ??????????????
• ????????ADO???????????

21
ADO?????????(??)

• RecordSource??
• ???????,??????????????????
• ?????
• ?????????(CommandType??adCmdTable)
• ????Select??(CommandType??adCmdText)
• ????????ADO???????????

22
ADO?????????(??)

• CommandType??
• ???????
• ??
• adCmdTable
• adCmdText
• adCmdStoredProc
• adCmdUnknown
• ????????ADO???????????

23
ADO????????(??)

• Recordset??
• ?ADO??????????????????,??Recordset????????????????
  ?????????????
• Recordset??,???????,????????????,?????????,??????,
  ????,??????????
• ????????????(???,recordsource)??????
• Recordset?????????????,????????

24
?????
????? (???)
???? (????)
25
??

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

26
13.2.2 ?????????

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

27
???????????

• ??????
• ?CheckBox, ListBox , TextBox,Label,PictureBox,Imag
  e,ComboBox?,????DataSource?DataField????????????(?
  ??)
• ??????
• ?DBCombo, DBGrid, DBList, RichText, FlexGrid?
• ???OLEDB???????(?)
• ?DataCombo, DataList, DataGrid?

28
?????????????

• ???????
• DataSource??, ????????????,?ADO???????
• ??????????????????
• ??????????ADO????,?????????
• DataField??, ???????????(???????)
• ??????????????????

29
13.2.3 ??ADO???????????

• ?????ADO????
• ???????ADO?????????,???????????????(??????)
• ????????, ??????????????, ?????????????ADO????????
  
• ?????ADO????????????????????(?????recordset??????)
  
• ??????????????????

30
ADO????????

• ??????????,??????
• ?????Stu.mdb?????Student??
• (???Student??Course??SC???)
• ?????Examp1?

31
?

• ?????????????????,??????????????
• ??????ADO?????RecordSource????Select???

32
ADO????????

• ?????????????????
• ??ADO?????RecordSource?
• Select
• Student.stuID,StuName,stuSex,stuDept,couName,st
  uScore
• from Student,Course, SC
• where Student.stuIDSC.stuID And
• SC.couIDCourse.couID
• ??CommandType???adCmdText
• ?????????DataSource?DataField??
• ???Examp2?

33
??,????ADO?????????,?????????????,????????????????
?????,????
?

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

34
13.2.4 Recordset??(???)???

• Recordset ?????
• EOF?BOF??
• ???Fliter??
• RecordCount??
• AbsolutePosition??
• Bookmark??

35
??????(??)

• RecordCount????????????
• AbsolutePosition????????????????(????????),?1????

36
??????(??)

• BOF???????????????
• EOF???????????????
• ???????
• SumAge 0
• adodc1.Recordset.MoveFirst
• Do While not adoc1.Recordset.EOF
• SumAge SumAge adodc1.Recordset(age)
• adodc1.Recordset.MoveNext
• Loop
• AverageAge SumAge/adodc1.Recordset.RecordCount

37
??????(??)

• Bookmark???????????,????,????????????????,???????
  ????
• Dim myBookMark as Variant
• myBookMark adodc1.Recordset.BookMark

????????????????????, ???????????????
38
??????(??)

• ??Recordset??????
• ??Recordset???Filter??,???Recordset???????????????
  ?,???????Recordset?????,??????????????????????????
  ,????????Recordset????????????
• ??
• ??????,??????
• ?????????

39
?????

• ??
• adodc1.Recordset.Filter ?????
• ????????
• ??? ????? ??(?????)

40
Filter?????

• ?????????,??????????
• ?????
• ?????(gt, lt, lt, gt, ltgt, , LIKE)
• ???,???(), ??(),LIKE(,)
• ??AND,OR????(??)
• ?
• adodc1.Recordset.Filter ProductID gt 30 and
  ProductID lt50
• adodc1. Recordset .Filter ProductName LIKE
  computer

41
?????

• ??
• Adodc1.Recordset.Filter adFilterNone(??????)

42
??????(??)

• Fields??????????
• ??????????????,??????
• ????,????????????Field??
• Text1.text adodc1.Recordset.Fields(0).Value
• Text2.text adodc1.Recordset.Fields(Orderid).V
  alue
• Text1.text adodc1.Recordset(Orderid)
• ?????????????,???????????
• ?????????????
• (0)??????????
• ()??????????

43
Field??

• ???????,Recordset????????
• ????
• Name?????
• Value?????????
• OrdinalPosition???Field??????
• Type?Size???????????
• SourceField, SourceTable??????????????????,??????
  ???????????????

44
13.3.2 Recordset??(???)???

• Recordset ?????
• Move??
• MoveFirst?MoveLast?MoveNext?MovePrevious
• ??????
• AddNew?Update?Delete?CancelUpdate?
• Find??

45
??Move???

• ???ADO?????????,???????
• MoveFirst?????????
• MoveLast?????????
• MoveNext??????????????
• MovePrevious??????????????
• ??
• MoveNext?MovePrevious??????????????
• ?????(BOF, EOF),?????????,????
• ?????????

46
???

• ??????,????????
• ??????????
• ????????????????

???????Examp3?
47
??????(??)
??Find?? ?????????????????? ?adodc1.Recordset.F
ind(Orderid1101) adodc1.Recordset.Find(
Name Like ? )
48
??????(??)
??AddNew??(?????????) ???????,?????,????,?? ?????
??????,?????????? adodc1.Recordset.AddNew adodc
1.Recordset(Orderid) 100 adodc1.Recordset(Or
derdate) 2001/06/30 adodc1.Recordset(ShipAd
dress) Shanghai
?????????????????????
49
??????(??)
??Update?? ??????????????????????????????????????
?????,??????????? adodc1.Recordset.AddNew adodc1
.Recordset(Orderid) 100 adodc1.Recordset(Ord
erdate) 2001/06/30 adodc1.Recordset(ShipAdd
ress) Shanghai adoc1.Recordset.Update
??????ADO??????????????,??????????
50
??????(??)
??Delete?? ??????,????
adodc1.Recordset.Delete
51
??????(??)

• ??CancelUpdate??
• ????????Update????????????????????
• adodc1.Recordset.CancelUpdate

52
???????

• ??????????
• ??????????
• ????????
• adodc1.Recordset.MoveNext
• adodc1.Recordset(Orderid) 101
• adodc1.Recordset(Orderdate) 2001/06/30
• adodc1.Recordset(ShipAddress) Chongqing
• adodc1.Recordset.Update
• ??????????????????

53
???

• ??????
• ???AddNew????
• ???Delete????
• ???Update????
• ???CancelUpdate????
• ???Find????,???????????????????,????????????????
  ??
• ??Filter???????????????

54
13.4 DataGrid?????
????

• ???????????????????????,??????????????????,???????
  ???,??????

55
??

• ??DataGrid?????????????
• ??DataGrid???????????
• ???????????,????? ?????Microsoft DataGrid
  Control 13.0(OLEDB)?

56
DataGrid?????

• ??????????,???????? ?,??????????
• ????
• DataSource?????,??????? (??????)

57
DataGrid?????

• ????????,?????????????
• ?????????????Properties?Columns????
• ??????????????????????Edit,??????????,????
• ???????????? ?Splits????
• ????????????????????????

58
DataGrid???????

• ??DataGrid????????????,??????DataGrid???????
• ??DataGrid??????,???????
• AllowAddNew??
• AllowDelete??
• AllowUpdate??
• ColumnHeaders????????????
• Enabled??????DataGrid??,??????????

59
ADO????????

• ????????????
• ????
• ???????AdoDC1??
• ??DataGrid??????,??????
• ??DataGrid???????
• ?????

60
13.5 Data????

• Data??
• ??????????????
• ?????????,?????SQL????
• ??????????????, ??????????????,?????
• ?????

61
??Data?????

• Connect???????
• DatabaseName??????????
• RecordSource???(???????)
• Options?????????????
• Exclusive????????????
• ReadOnly?????????
• EditMode?????????

62
1?Data?? DatabaseName??

• ?????,????????????
• ?????
• d\student\score.mdb
• ??????????
• \\server\work\score.mdb , ?????
• g\work\score.mdb, ?????????
• ?????????????,????????

63
2?Data?? Connect??

• ?????,??????????, ?????ODBC???????????
• Access Access(????)
• dBASE III dBASE III
• dBASE 5 dBASE 5.0
• Paradox 5.x Paradox 5.x
• FoxPro 2.6 FoxPro 2.6
• Excel 3.0 Excel 3.0

64
3?Data?? RecordSource??

• ???????????
• ???
• SQL???????(???????)
• ??
• ????
• SQL????

65
??Data?????

• Move??
• MoveFirst?MoveLast?MoveNext?MovePrevious
• ??????
• AddNew?Update?Delete?CancelUpdate?
• Find??
• FindFirst?FindLast?FindNext?FindPrevious

66
??Data?????

• Reposition
• ???????????
• Validate
• ?????????????????????, ??, ??, ??, ??
• Error
• Data????????????????????, ??????????????,
  ?????????????

67
13.6 ADO??????
ADO??
??
68
ADO???
?

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

69
????????????
????
VB ?? ??
???
????
????????? (ADO)????
70
ADO???
?

• ADO???????,?????????????????ADO???????????????????
  ??
• ??ADO??????????,????????(?????)?
• ???????
• ?????????,?????
• ???????????

71
ADO???
?

• ADO?????????????????????????????????????
• ADO??????????????????
• ADO??????????????????????Visual C?Visual
  J?Visual InterDev????ADO???

72
????ADO????
Connection
Errors
Error
Command
Parameters
Parameter
Recordset
Fields
Field
73
ADO????

• Connection ??????????
• Command ??????????????
• Recordset ???????????
• Field ???????????
• Error ????????????????
• Parameter ?????????
• ??????????

?? ????Microsoft ActiveX Data Object 2.0
Library
74
?????????

• 1??????Stu.mdb
• Student,Course,SC?
• 2???????????(DSNvbtest)
• ????ODBC?????
• ???????????,?????? ?????????

75
????????

• ???????????????,???????
• ???????????????????????
• ??????,????????????????
• ??,??????(????????)?
• ??
• Dim adoRecordset As ADODB.Recordset
• ??
• ???????????????????
• Dim adoRecordset As New ADODB.Recordset
• ?????New???
• Dim adoRecordset As ADODB.Recordset
• Set adoRecordset New ADODB.Recordset

76
??ADO???????????

• ??Connection??????????
• ??Command??,???????????????Connection??,??????????
  ?????????(?Select, Insert, Update?)
• ??Command???Execute??????,???????,????????Recordse
  t??
• ??Recordset??????

77
??ADO???????

• Dim adoConnection As New ADODB.Connection
• Dim adoCommand As New ADODB.Command
• Dim adoRecordset As New ADODB.Recordset
• adoConnection.ConnectionString
  "dsnvbtestuidsapwd"
• adoConnection.Open
• adoCommand.ActiveConnection adoConnection
• adoCommand.CommandType adCmdTable
• adoCommand.CommandText Student
• adoRecordset adoCommand.Execute

78
??ADO???????

• ??Connection??????????
• ??Recordset??,???????????????
• ??Recordset???Open??,?????????

????????????,???????SQL??,????????????????????????
,???????
79
??ADO???????

• Dim adoConnection As New ADODB.Connection
• Dim adoRecordset As New ADODB.Recordset
• ????
• adoConnection.ConnectionString
  "dsnvbtestuidsapwd"
• adoConnection.Open
• ?????
• adoRecordset.ActiveConnection adoConnection
• adoRecordset.CursorLocation adUseClient
• adoRecordset.CursorType adOpenDynamic
• adoRecordset.Source "select from Student"
• adoRecordset.Open , , , , adCmdText

80
13.13.1 Connection??

• Connection????????????????
• ??Connection????????????
• ?????
• ??????
• ??Error??????????????

81
??Connection?????

• ???????Connection????,??????
• ConnectionString ?????,???????????????
• ????????????????,????????,?????
• ConnectionTimeOut ?????????,???0,???????????????
• State ??????????????????????????

82
??Connection?????

• Open ????,???????????
• ?
• Dim adoConnection As ADODB.Connection
• Set adoConnection New ADODB.Connection
• adoConnection.ConnectionString
  "dsnvbtestuidsapwd"
• adoConnection.Open
• ??????Connection???????,????????????????

83
13.13.2 Recordset??

• ?ADO???,????Recordset??????????,?????????SQL??????
  ?
• ???Recordset??(???)?????(?)???(?)???

84
????? (Recordset??)
????? (???)
???? (????)
85
ADO???Recordset?????

• ?????????????,???????/??????????,???????????????
• ???????,???????????????,??????????
• ?????????

86
??ADO?Recordset?????

• ?????Command???Execute??????Recordset(???????????
  )
• Set adoRst adoCom.Execute
• ???????Recordset?????Open??(???????????)
• adoRst.Open

87
??Recordset?????
88
1?CursorType??

• ???????? (???????????)
• Static(adOpenStatic)
• ????????????,??????????
• ????????????????
• Dynamic(adOpenDynamic)
• ?????????????????,?????
• ???????
• ForwardOnly(adOpenForwardOnly)(???)
• ??????????,???????????
• Keyset(adOpenKeyset)
• ???????,???????,??Static?Dynamic??

89
2?CursorLocation??

• ?????????????
• AdUseClient
• ???????????????
• ???????????????,??????,
• ????????????
• AdUseServer
• ????????????????,??????
• ?????,?????,?????????,?
• ????Bookmark?AbsolutePosition???(???)

90
3?LockType??

• ?????????????????,?????????????
• AdLockReadOnly(???)
• ??????
• AdLockPessimistic
• ????????????,????????
• AdLockOptimistic
• ?????Update???,?????
• AdLockBatchOptimistic
• ?????????,???????????

91
4?ActiveConnection??

• ?????????Recordset?????Connection??

92
5?Source??

• ??????Recordset???????
• 6?MaxRecord??
• ????????Recordset?????????????,???0,??????????

93
??Recordset?????

• Open??
• ????Recordset???

94
??Recordset????

• Dim adoRst3 as ADODB.Recordset
• Set adoRst3 New ADODB.Recordset
• adoRst3.ActiveConnection adoConnection
• adoRst3.CursorType adOpenForwardOnly
• adoRst3.CursorLocation adUseClient
• adoRst3.Source Student
• (? adoRst3.SourceSelect From Student)
• adoRst3.Open

95
??????
96
1?????

• ????
• MoveFirst ???Recordset??????
• MoveLast???Recordset???????
• MoveNext?????????????
• MovePrevious?????????????
• BOF???????
• EOF???????

?????????Forward-Only,?????MovePrevious?MoveFirst?
??
97
2???????

• Recordset???Move????????
• ??Recordset.Move NumRecords, Start
• Numrecords ????????,????,????
• Start ?????????,??????????
• AdBookmarkCurrent????????
• AdBookmarkFirst?????????
• AdBookmarkLast??????????
• ?????BOF?EOF??

98
??????

• adoRst.MoveFirst
• Do While not adoRst.EOF
• debug.print adoRst(stuName)
• adoRst.MoveNext
• Loop

99
?????????

• ?????,????Recordset???????
• ???????????(??????????????????)
• Textbox????????,?????????,????????
• ??Examp6?

100
3?????

• Recordset.AddNew Fields, Values(????)
• Fields Recordset????,?????????????????
• Values Recordset???,???Fields?????
• ????????,??
• adoRst.addNew
• adoRst(couID)c0020
• adoRst(couName)???
• adoRst(couTeacher)Wang
• adoRst(couCredit) 4

101
4?????

• Recordset.Delete Affectrecords
• Affectrecords?????????????,??,???????
• AdAffectCurrent ??????(???)
• AdAffectGroup ??????Filter?????????

102
5?????

• ???????,?????????????
• ??(??????)
• Recordset.Update Fields, Values(????)
• ???????,??
• ??AddNew???
• ????????
• ???????,?????????,???
• ???????,??????CancelUpdate??

103
?????????

• ?????,????Recordset???????
• ???????????(??????????????????)
• Textbox????????,?????????,????????
• ????????????,?????????
• ???????????,????????
• ?????Examp7?

104
6?Recordset???????

• ????
• ?????????????,??Update????
• ??????????,??CancelUpdate????
• ???
• ???????????????????,???
• ???????UpdateBatch??,??CancelBatch
• ?????
• ??LockType?????AdLockBatchOptimistic

105
7??Recordset???????

• ????????????????,?????Recordset??????????????????
  ?????????Variant???
• BookMark?????????????
• ??????????????adUseClient(CursorLocation??)

106
8???Recordset??????

• ??Recordset???Filter??,???Recordset???????????????
  ?,???????Recordset??????????,????????Recordset???
• ??recordset.Filter?????
• ??????????? ????? ??
• ??
• ??????,??????
• ?????????

107
Filter?????

• ?????????,??????????
• ?????
• ?????(gt, lt, lt, gt, ltgt, , LIKE)
• ???,???(), ??(),LIKE(,)
• ??AND,OR????(??)
• ?
• adoRst.Filter ProductID gt 30 and ProductID
  lt50
• adoRst.Filter ProductName LIKE computer

108
?????????

• ?Stu?Student????
• ??DataGrid??????
• ????????,??????
• Textbox????????

109
9???Recordset????

• Find Criteria, SkipRows,SearchDirection,Start
  
• Criteria ??????(?Filter??)
• SkipRows ?????????????????
• SearchDirection ??????(??/??)
• Start ?????????
• ??BOF, EOF???True
• ??????????????,?????BOF?
• EOF?True

110
ADO???????????
111
13.7 ?????????

• ??????SQL Server??????
• ??Excel??
• ??Access???