1 / 46

Giới thiệu về Visual Basic và ADO

Giới thiệu về Visual Basic và ADO. Nguyễn Văn Khiết. 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. Giôùi thieäu veà Visual Basic.

benjamin-buck
Download Presentation

Giới thiệu về Visual Basic và ADO

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Giới thiệu về Visual Basic và ADO Nguyễn Văn 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 điều_kiện Then Lệnh • If điều_kiện Then … End if • If điề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 bien=Biên_dướ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 điều_kiện_lặp (Các) lệnh Wend Do While điề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ïnMicrosoft 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.1;Data Source=SELAB106;User ID=sa;Initial Catalog=pubs" 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-11T00:00:00 ')" 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_id=11 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.RecordCount>0 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,NoiSinh,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 > 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 End If Set rs = Nothing End Sub

  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 > 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 Next End If Set rs = Nothing End Sub

  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

More Related