Gi?i thi?u v? Visual Basic v

1
Gi?i thi?u v? Visual Basic và ADO

• Nguy?n Van Khi?t

2
Noäi dung

• Giôùi thieäu veà Visual Basic
• Moâi tröôøng laøm vieäc
• Caùc kieåu döõ lieäu cô sôû
• Cuù phaùp caùc leänh
• Giôùi thieäu veà ADO
• Moâ hình ñoái töôïng
• Caùc ví duï söû duïng.

3
Giôùi thieäu veà Visual Basic

• Maøn hình thieát keá form

4
Giôùi thieäu veà Visual Basic

• Xöû lyù caùc söï kieän treân form

5
VB Caùc kieåu döõ lieäu cô baûn

• Byte
• Integer
• Long
• Currency
• Date
• String
• Boolean
• Single
• Double

6
Cuù phaùp caùc leänh

• Khai báo bi?n
• Dim TênBi?n As Ki?uD?Li?u
• VD Dim i as Integer
• Dim str as String
• Dim b as Boolean
• Dim cn as ADODB.Connection
• Dim lb as ListBox

7
Cuù phaùp caùc leänh

• Leänh gaùn
• Cuù phaùp
• Gaùn caùc kieåu döõ lieäu cô sôû
• Bieán giaùtrò
• Gaùn caùc ñoái töôïng
• Set bieán ñoái töôïng
• Ví duï
• a 100
• Set cn new ADODB.Recordset

8
Cuù phaùp caùc leänh

• Leänh if
• Cuù phaùp
• If di?u_ki?n Then L?nh
• If di?u_ki?n Then
• End if
• If di?u_ki?n Then
• Else
• End if

9
Cuù phaùp caùc leänh

• Leänh Select Case
• Cuù phaùp
• Select Case Bi?n
• Case giá_tr?_1
• (Các) l?nh
• Case giá_tr?_2
• (Các) l?nh
• Case Else
• (Các) l?nh
• End Select

10
Cuù phaùp caùc leänh

• Leänh For
• Cuù phaùp
• For bienBiên_du?i To Biên_trên step k
• (Các) l?nh
• Next

11
Cuù phaùp caùc leänh

• Leänh While
• Cuù phaùp
• While di?u_ki?n_l?p
• (Các) l?nh
• Wend
• Do While di?u_ki?n_l?p
• (Các) l?nh
• Loop

12
ADO
Browser / Application
ADO
OLE DB
ODBC
Relational Data
Non-Relational Data
13
ADO (ActiveX Data Object)

• Ñeå söû duïng ADO trong Visual Basic, tham chieáu
  ñeán thö vieän
• Trong Visual Basic, choïn menu Project,
  References
• Choïn Microsoft ActiveX Data Objects 2.6 Library .

14
ADO Object Model

• Connection
• Recordset
• Command
• Parameter
• Field

15
ADO Object Model

• Ñoái töôïng Connection
• Thieát laäp keát noái ñeán CSDL.
• Xaùc ñònh OLE DB provider
• Thöïc hieän 1 caâu query
• Quaûn lyù transactions treân keát noái ñang môû.
  

16
Connection Object

• Ví duï Môû moät connection
• (k?t n?i t?i MS Access hs.mdb)
• Dim cn As New ADODB.Connection
• cn.ConnectionString "Provider
  Microsoft.Jet.OLEDB.4.0 Data Source hs.mdb"
• cn.Open

17
Connection Object

• Ví duï Môû moät connection
• (k?t n?i t?i MS SQLServer pubs)
• Dim cn As New ADODB.Connection
• cn.ConnectionString Provider SQLOLEDB.1Data
  SourceSELAB106User IDsaInitial Catalogpubs"
• cn.Open

18
Connection Object

• Ví duï Thöïc hieän caâu leänh SQL
• insert statement
• Dim strSQL as String
• strSQL "insert into employee values ('ARD25437A
  ','John','P','Baxter',7, 100, 0877,
  '1989-11-11T000000 ')"
• cn. Execute strSQL

19
Connection Object

• Ví duï Thöïc hieän caâu leänh SQL
• update statement
• Dim strSQL as String
• strSQL UPDATE employee SET job_id11 WHERE
  fname'Pedro "
• cn. Execute strSQL

20
Connection Object

• Ví duï Thöïc hieän caâu leänh SQL
• delete statement
• Dim strSQL as String
• strSQL DELETE from employee WHERE
  fname'Pedro "
• cn. Execute strSQL

21
Recordset Object

• Recordset object provides methods for
  manipulating result sets .
• Recordset object allows you to add, update,
  delete, and scroll through rows in the recordset.

22
Recordset Object

• Caùc caùch ñeå laáy ñöôïc moät Recordset
• Execute connection
• Execute command
• Open Recordset.

23
Recordset Object

• Open recordset
• Open strSQL, connection, opentype, locktype

24
Recordset Object

• Open Type
• adOpenForwardOnly
• adOpenStatic
• adOpenKeyset
• adOpenDynamic
• Lock type
• adLockReadOnly
• adLockOptimistic
• adLockPessimistic
• adLockBatchOptimistic

25
Recordset Object

• VD
• Dim rs as ADODB.Recordset
• Set rs New ADODB.Recordset
• rs.Open Select from employee, cn,
  adOpenStatic, adLockOptimistic
• Set rs Nothing

26
Recordset Object

• Di chuyeån treân Recordset
• MoveFirst
• MoveNext
• MovePrevious
• MoveLast
• Move

27
Recordset Object

• Duyeät qua Recordset
• Dim rs as ADODB.Recordset
• Set rs New ADODB.Recordset
• rs.Open Select from employee, cn,
  adOpenStatic, adLockOptimistic
• If rs.RecordCountgt0 then
• rs.MoveFirst
• While not rs.EOF
• rs.MoveNext
• WEnd
• End if
• Set rs Nothing

28
VB ADO Code example

• Database Microsoft Access 2000
• Data Source hs.mdb
• Table HOCSINH

29
Ví duï 1

• Nhaäp thoâng tin hoïc sinh.
• Duøng phöông thöùc AddNew cuûa Recordset

30
Ví duï 1

• Xöû lyù nuùt Ghi
• Dim cn As New ADODB.Connection
• cn.ConnectionString "Provider
  Microsoft.Jet.OLEDB.4.0 Data Source "
  App.Path "\hs.mdb"
• cn.Open
• Dim rs As New ADODB.Recordset
• rs.Open "Select from HOCSINH", cn,
  adOpenStatic, adLockOptimistic
• rs.AddNew
• rs!TenHS txtTenHS.Text
• rs!NgaySinh txtNgaySinh.Text
• rs("Noisinh") txtNoiSinh.Text
• rs("DiaChi") txtDiaChi.Text
• rs.Fields("GhiChu") txtGhiChu.Text
• rs.Update
• Set rs Nothing
• Set cn Nothing

31
Ví duï 2

• Nhaäp thoâng tin hoïc sinh.
• Duøng phöông thöùc Execute cuûa Connection.

32
Ví duï 2

• Xöû lyù nuùt Ghi
• Dim cn As New ADODB.Connection
• cn.ConnectionString "Provider
  Microsoft.Jet.OLEDB.4.0 Data Source "
  App.Path "\hs.mdb"
• cn.Open
• Dim strSQL As String
• strSQL "Insert into HOCSINH(TenHS,NgaySinh,N
  oiSinh,DiaChi,GhiChu) values ('" txtTenHS.Text
  "','" txtNgaySinh.Text "','"
  txtNoiSinh.Text "','" txtDiaChi.Text "','"
  txtGhiChu.Text "')"
• cn.Execute strSQL
• Set cn Nothing

33
Ví duï 3

• Laáy danh saùch hoïc sinh.
• Duøng MSHFlexGrid ñeå hieån thò

34
Ví duï 3

• Choïn söû duïng Microsoft Hierrachical FlexGrid
  Control.
• Choïn Project Components

35
Ví duï 3

• Khôûi taïo Connection
• Public Sub KhoiTaoKetNoi()
• cn.ConnectionString "Provider
  Microsoft.Jet.OLEDB.4.0 Data Source "
  App.Path "\hs.mdb"
• cn.Open
• End Sub

36
Ví duï 3

• Hieån thò tieâu ñeà
• Public Sub HienThiTieuDe()
• gridHS.Cols 7
• gridHS.Rows 2
• gridHS.TextMatrix(0, 0) "STT"
• gridHS.TextMatrix(0, 1) "MaHS"
• gridHS.TextMatrix(0, 2) "Teân HS"
• gridHS.TextMatrix(0, 3) "Ngaøy sinh"
• gridHS.TextMatrix(0, 4) "Noi sinh"
• gridHS.TextMatrix(0, 5) "Ñòa chæ"
• gridHS.TextMatrix(0, 6) "Ghi chuù"
• gridHS.AllowUserResizing flexResizeBoth
• End Sub

37
Ví duï 3

• Laáy döõ lieäu
• Public Sub LayDuLieu()
• Dim rs As New ADODB.Recordset
• rs.Open "Select from HOCSINH", cn,
  adOpenStatic, adLockOptimistic
• If rs.RecordCount gt 0 Then
• gridHS.Rows rs.RecordCount 1
• rs.MoveFirst
• Dim i As Integer
• For i 1 To rs.RecordCount
• gridHS.TextMatrix(i, 0) i
• gridHS.TextMatrix(i, 1) rs!MaHS
• gridHS.TextMatrix(i, 2) rs!TenHS
• gridHS.TextMatrix(i, 3) rs!NgaySinh
• gridHS.TextMatrix(i, 4) rs!NoiSinh
• gridHS.TextMatrix(i, 5) rs!DiaChi
• gridHS.TextMatrix(i, 6) rs!GhiChu
• rs.MoveNext
• Next

38
Ví duï 3

• Goïi 3 haøm treân
• Private Sub Form_Load()
• KhoiTaoKetNoi
• HienThiTieuDe
• LayDuLieu
• End Sub

39
Ví duï 4

• Laáy danh saùch hoïc sinh.
• Duøng ListView ñeå hieån thò

40
Ví duï 4

• Choïn söû duïng Microsoft Windows Common Control
  6.0.
• Choïn Project Components

41
Ví duï 4

• Khôûi taïo Connection
• Public Sub KhoiTaoKetNoi()
• cn.ConnectionString "Provider
  Microsoft.Jet.OLEDB.4.0 Data Source "
  App.Path "\hs.mdb"
• cn.Open
• End Sub

42
Ví duï 4

• Hieån thò tieâu ñeà
• Public Sub HienThiTieuDe()
• lvHS.View lvwReport
• lvHS.ColumnHeaders.Add , , "STT"
• lvHS.ColumnHeaders.Add , , "MaHS"
• lvHS.ColumnHeaders.Add , , "Teân HS"
• lvHS.ColumnHeaders.Add , , "Ngaøy sinh"
• lvHS.ColumnHeaders.Add , , "Nôi sinh"
• lvHS.ColumnHeaders.Add , , "Ñòa chæ"
• lvHS.ColumnHeaders.Add , , "Ghi chuù"
• End Sub

43
Ví duï 4

• Laáy döõ lieäu
• Public Sub LayDuLieu()
• Dim rs As New ADODB.Recordset
• rs.Open "Select from HOCSINH", cn,
  adOpenStatic, adLockOptimistic
• If rs.RecordCount gt 0 Then
• rs.MoveFirst
• Dim i As Integer
• Dim li As ListItem
• For i 1 To rs.RecordCount
• Set li lvHS.ListItems.Add(, , i)
• li.SubItems(1) rs!MaHS
• li.SubItems(2) rs!TenHS
• li.SubItems(3) rs!NgaySinh
• li.SubItems(4) rs("NoiSinh")
• li.SubItems(5) rs("DiaChi")
• li.SubItems(6) rs.Fields("Ghichu")
• rs.MoveNext

44
Ví duï 4

• Goïi 3 haøm treân
• Private Sub Form_Load()
• KhoiTaoKetNoi
• HienThiTieuDe
• LayDuLieu
• End Sub

45
Ví duï 5

• Xoùa moät hoïc sinh töø danh saùch

46
Ví duï 5

• Xöû lyù nuùt Xoùa
• Private Sub cmdXoa_Click()
• Dim li As ListItem
• Set li lvHS.SelectedItem
• If Not IsNull(li) Then
• Dim sql As String
• Dim mahs As Integer
• mahs li.SubItems(1)
• sql "Delete from HOCSINH where MAHS"
  mahs
• cn.Execute sql
• lvHS.ListItems.Remove li.index
• End If
• End Sub