Giáo trình Nhập môn hệ quản trị cơ sở dữ liệu - Đặng Thị Thu Hiền (Phần 2)

.Connection 
 con.Open cnstr 
 End If 
 If (IsNull(con)) Then 
 Set con = New ADODB.Connection 
 con.Open cnstr 
231 
 End If 
 If (IsEmpty(con)) Then 
 Set con = New ADODB.Connection 
 con.Open cnstr 
 End If 
 Dim rec As ADODB.Recordset 
 Set rec = New ADODB.Recordset 
 rec.Open sql, con, adOpenDynamic, adLockOptimistic 
 While (rec.EOF = False) 
 rec.Delete 
 rec.MoveNext 
 Wend 
 rec.Close: con.Close: Set con = Nothing 
 LoadDataToListBox 
End Sub 
Private Sub cmdEdit_Click() 
 'Thay lai Text trong Caption cua nut cho phu hop 
 'kiem tra nguoi dung chi duoc chon 1 item de sua 
 'tai item do vao cac dieu khien tren form 
 'nguoi dung sua cac gia tri 
 'cap nhat lai bang Course cac gia tri moi 
 'Hien thi ket qua sua item do 
 Dim cap, cn, Dur, Des As String 
 cn = "": Dur = "": Des = "" 
 cmdEdit.SetFocus: cap = cmdEdit.Caption 
 cap = Trim(cap): cap = LCase(cap) 
 If (cap = "edit") Then 
 cap = "Save Edit" 
 If (lbCourse.ItemsSelected.count = 0) Then 
 MsgBox "You must select only 1 item in the ListBox to edit!" 
 Exit Sub 
 End If 
 Dim count As Integer: count = lbCourse.ItemsSelected.count 
232 
 If (count > 1) Then 
 MsgBox "You must select only 1 item in the ListBox to edit!" 
 Exit Sub 
 End If 
 editingCID = lbCourse.Column(0, lbCourse.ItemsSelected.item(0)) 
 If (lbCourse.Column(1, lbCourse.ItemsSelected.item(0)) "") Then 
 cn = lbCourse.Column(1, lbCourse.ItemsSelected.item(0)) 
 End If 
 If (lbCourse.Column(2, lbCourse.ItemsSelected.item(0)) "") Then 
 Dur = lbCourse.Column(2, lbCourse.ItemsSelected.item(0)) 
 End If 
 If (lbCourse.Column(3, lbCourse.ItemsSelected.item(0)) "") Then 
 Des = lbCourse.Column(3, lbCourse.ItemsSelected.item(0)) 
 End If 
 txtCN.SetFocus: txtCN.Text = cn 
 txtDuration.SetFocus: txtDuration.Text = Dur 
 txtDes.SetFocus: txtDes.value = Des 
 Else 
 cap = "Edit" 
 txtCN.SetFocus: cn = txtCN.Text 
 txtDuration.SetFocus: Dur = txtDuration.Text 
 txtDes.SetFocus: Des = txtDes.value 
 If (cn = "") Then 
 MsgBox "The name of the course must be required!" 
 Exit Sub 
 End If 
 Dim sql As String: sql = "SELECT * FROM Course WHERE CID=" & 
CStr(editingCID) 
 If (con Is Nothing) Then 
 Set con = New ADODB.Connection 
 con.Open cnstr 
 End If 
 If (IsNull(con)) Then 
233 
 Set con = New ADODB.Connection 
 con.Open cnstr 
 End If 
 If (IsEmpty(con)) Then 
 Set con = New ADODB.Connection 
 con.Open cnstr 
 End If 
 Dim rec As ADODB.Recordset 
 Set rec = New ADODB.Recordset 
 rec.Open sql, con, adOpenDynamic, adLockOptimistic 
 rec.Fields("CName").value = cn 
 If (Dur "") Then 
 If (IsNumeric(Dur) = False) Then 
 MsgBox "Duration must be less than or equal 500!" 
 Exit Sub 
 End If 
 Dim pos1, pos2 As Integer 
 pos1 = -1 
 pos2 = -1 
 pos1 = InStr(1, Dur, ",", vbTextCompare) 
 pos2 = InStr(1, Dur, ".", vbTextCompare) 
 If (pos1 > 0) Or (pos2 > 0) Then 
 MsgBox "Duration must be less than or equal 500!" 
 Exit Sub 
 End If 
 Dim num As Integer 
 num = CInt(Dur) 
 If (num = Null) Or (num > 500) Then 
 MsgBox "Duration must be less than or equal 500!" 
 Exit Sub 
 End If 
 rec.Fields("DurationInHour").value = num 
234 
 End If 
 If (Des "") Then 
 rec.Fields("Description").value = Des 
 End If 
 rec.Update 
 rec.Close: con.Close: Set con = Nothing 
 End If 
 cmdEdit.SetFocus 
 cmdEdit.Caption = cap 
 LoadDataToListBox 
End Sub 
Private Sub cmdRefresh_Click() 
 txtCN.SetFocus: txtCN.Text = "" 
 txtDes.SetFocus: txtDes.value = "" 
 txtDuration.SetFocus: txtDuration.Text = "" 
 editingCID = -1 
 cmdEdit.SetFocus 
 cmdEdit.Caption = "Edit" 
 LoadDataToListBox 
End Sub 
Private Sub cmdSearch_Click() 
 'Lay cac du lieu nguoi dung nhap tu form 
 'SELECT du lieu theo dieu kien tim kiem 
 'Hien thi ket qua ben duoi ListBox 
 cmdEdit.SetFocus: cmdEdit.Caption = "Edit": editingCID = -1 
 Dim cn As String: txtCN.SetFocus: cn = txtCN.Text 
 Dim Duration As String: txtDuration.SetFocus: Duration = 
txtDuration.Text 
 Dim Des As String: txtDes.SetFocus: Des = txtDes.value 
 Dim foundCN As Boolean: foundCN = False 
 Dim foundDur As Boolean: foundDur = False 
 Dim foundDes As Boolean: foundDes = False 
 If (cn "") Then 
 foundCN = True 
235 
 End If 
 If (Duration "") Then 
 foundDur = True 
 End If 
 If (Des "") Then 
 foundDes = True 
 End If 
 If (foundCN = False) And (foundDur = False) And (foundDes = False) Then 
 MsgBox "You must input either Name or Duration or Description of 
the course!" 
 Exit Sub 
 End If 
 Dim sql As String 
 sql = "SELECT * FROM Course WHERE" 
 If (foundCN) And (foundDur) And (foundDes) Then 
 sql = sql & " (CName Like '%" & cn & "%')" 
 sql = sql & " and (DurationInHour = " & Duration & ")" 
 sql = sql & " and (Description LIKE '%" & Des & "%')" 
 Else 
 If (foundCN) And (foundDur) And (Not foundDes) Then 
 sql = sql & " (CName Like '%" & cn & "%')" 
 sql = sql & " and (DurationInHour = " & Duration & ")" 
 Else 
 If (foundCN) And (Not foundDur) And (foundDes) Then 
 sql = sql & " (CName Like '%" & cn & "%')" 
 sql = sql & " and (Description LIKE '%" & Des & "%')" 
 Else 
 If (foundCN) And (Not foundDur) And (Not foundDes) Then 
 sql = sql & " (CName Like '%" & cn & "%')" 
 Else 
 If (Not foundCN) And (foundDur) And (foundDes) Then 
 sql = sql & " (DurationInHour = " & Duration & ")" 
 sql = sql & " and (Description LIKE '%" & Des & "%')" 
 Else 
236 
 If (Not foundCN) And (foundDur) And (Not foundDes) Then 
 sql = sql & " (DurationInHour = " & Duration & ")" 
 Else 
 If (Not foundCN) And (Not foundDur) And (foundDes) Then 
 sql = sql & "(Description LIKE '%" & Des & "%')" 
 End If 
 End If 
 End If 
 End If 
 End If 
 End If 
 End If 
 If (con Is Nothing) Then 
 Set con = New ADODB.Connection 
 con.Open cnstr 
 End If 
 If (IsNull(con)) Then 
 Set con = New ADODB.Connection 
 con.Open cnstr 
 End If 
 If (IsEmpty(con)) Then 
 Set con = New ADODB.Connection 
 con.Open cnstr 
 End If 
 Dim rec As ADODB.Recordset 
 Set rec = New ADODB.Recordset 
 rec.Open sql, con, adOpenDynamic 
 If (rec.RecordCount = 0) Then 
 lblSearch.Caption = "There is no item found!" 
 rec.Close: con.Close 
 Set con = Nothing 
 Exit Sub 
237 
 Else 
 rec.MoveLast 
 lblSearch.Caption = "There are " & CStr(rec.RecordCount) & " items 
found" 
 Dim i As Integer 
 While (lbCourse.ListCount > 0) 
 i = lbCourse.ListCount - 1 
 lbCourse.RemoveItem Index:=i 
 Wend 
 lbCourse.ColumnCount = 4 
 lbCourse.AddItem item:="CID;Course Name;Duration (in hour) ; 
Description;", Index:=0 
 Dim item As String: item = "" 
 rec.MoveFirst 
 While (rec.EOF = False) 
 item = CStr(rec.Fields("CID").value) 
 If rec.Fields("CName") "" Then 
 item = item & ";" & rec.Fields("CName").value 
 Else 
 item = item & "; " 
 End If 
 If IsNull(rec.Fields("DurationInHour")) = False Then 
 item = item & ";" & 
CStr(rec.Fields("DurationInHour").value) 
 Else 
 item = item & "; " 
 End If 
 If rec.Fields("Description") "" Then 
 item = item & ";" & rec.Fields("Description").value 
 Else 
 item = item & "; " 
 End If 
 lbCourse.AddItem item:=item 
 rec.MoveNext 
238 
 Wend 
 rec.Close: con.Close 
 Set con = Nothing 
 End If 
End Sub 
Private Sub Form_Load() 
 cmdEdit.SetFocus: cmdEdit.Caption = "Edit": editingCID = -1 
 LoadDataToListBox 
End Sub 
Private Sub LoadDataToListBox() 
'Ham nay se tai du lieu tu bang Coourse vao ListBox lbCourse 
 'lbCourse.RowSource = "" 
 Dim rec As ADODB.Recordset 
 If (con Is Nothing) Then 
 Set con = New ADODB.Connection 
 con.Open cnstr 
 End If 
 If (IsNull(con)) Then 
 Set con = New ADODB.Connection 
 con.Open cnstr 
 End If 
 If (IsEmpty(con)) Then 
 Set con = New ADODB.Connection 
 con.Open cnstr 
 End If 
 Set rec = New ADODB.Recordset 
 rec.Open "SELECT * FROM Course", con, adOpenDynamic 
 If (rec.RecordCount = 0) Then 
 lblSearch.Caption = "There is no item found!" 
 rec.Close: con.Close: Set con = Nothing 
 Exit Sub 
 Else 
 rec.MoveLast 
239 
 lblSearch.Caption = "There are " & CStr(rec.RecordCount) & " items 
found" 
 rec.MoveFirst 
 Dim i As Integer 
 While (lbCourse.ListCount > 0) 
 i = lbCourse.ListCount - 1 
 lbCourse.RemoveItem Index:=i 
 Wend 
 lbCourse.AddItem item:="CID;Course Name;Duration (in hour) ; 
Description;", Index:=0 
 Dim item As String: item = "" 
 While (rec.EOF = False) 
 item = CStr(rec.Fields("CID").value) 
 If rec.Fields("CName") "" Then 
 item = item & ";" & rec.Fields("CName").value 
 Else 
 item = item & "; " 
 End If 
 If IsNull(rec.Fields("DurationInHour")) = False Then 
 item = item & ";" & 
CStr(rec.Fields("DurationInHour").value) 
 Else 
 item = item & "; " 
 End If 
 If rec.Fields("Description") "" Then 
 item = item & ";" & rec.Fields("Description").value 
 Else 
 item = item & "; " 
 End If 
 lbCourse.AddItem item:=item 
 rec.MoveNext 
 Wend 
 rec.Close 
 con.Close 
240 
 Set con = Nothing 
 End If 
End Sub 
241 
BÀI TẬP CHƢƠNG 7 
1. Hãy cài đặt lại chƣơng trình trong ví dụ về một chƣơng trình hoàn chỉnh sử 
dụng các đối tƣợng khác Recordset trong các kiến trúc DAO và ADO 
2. Hãy cài đặt thêm chức năng phân trang cho ListBox của bài tập trên. Số bản 
ghi trên 1 trang (PageSize) sẽ do ngƣời dùng nhập vào, tại mỗi thời điểm 
ListBox chỉ hiển thị PageSize bản ghi và các nút 1, 2, 3, 4,  để chuyển 
trang. Khi ngƣời dùng chọn 1 trang bất kỳ thì ListBox chỉ hiển thị các bản 
ghi thuộc trang đó và nút hiển thị trang đó sẽ nổi bật lên. 
242 
TÀI LIỆU THAM KHẢO 
[1] Access 2013, Microsoft, 2013 
[2] Đặng Thị Thu Hiền và Đỗ Thanh Thủy, Bài giảng Nhập môn hệ quản trị cơ sở dữ 
liệu Access, Trƣờng ĐH Giao Thông Vận Tải, 2007. 
[3] MSDN Library – October 2012 
[4] Teresa Hennig, Ben Clothier, George Hepworth and Dagi (Doug) Yudovich, 
Professional Access 2013 Programming, ISBN: 978-1-118-53083-2, Wrok, August 
2013. 
[5]  
[6]