ADO căn bản

Nội dung text: ADO căn bản

1. ADO căn bản MONDAY, 28. JANUARY 2008, 06:05:26 ADO Hãy đọc trước khi các bạn bước vào thế giới của ADO ADO là gì? Tôi xin miễn bàn cụ thể, các bạn có thể tham khảo trên trang www.vovisoft.com hay những trang khác. Tôi xin nói ngắn gọn: ADO là viết tắt của chữ ActiveX Data Object. Đây là một đối tượng giúp bạn làm việc với nhiều kiểu dữ liệu khác nhau. Giống như ODBC (Open DataBase Connectivity), kỹ thuật lập trình với OLE DB đòi hỏi phải được thực hiện bằng các lệnh phức tạp cấp thấp API. Chính vì lý do này, Microsoft cũng đã đưa ra một lớp giao tiếp lập trình cấp cao ADO được xây dựng dựa trên kỹ thuật OLE DB cho phép lập trình nhanh các ứng dụng liên kết CSDL (Cơ sở dữ liệu). CÁC ĐỐI TƯỢNG LẬP TRÌNH ADO CƠ SƠ Lớp giao tiếp lập trình dữ liệu cấp cao ADO cung cấp một tập hợp những đối tượng giúp chúng ta dễ dàng làm việc với các nguồn dữ liệu khác nhau. Trong các đối tượng này có những đối tượng chính cần tìm hiểu, đó là: Connection, Recordset, Command, Field (ngoài ra còn có: Parameter, Property, Error). Connection Đây là một đối tượng được sử dụng để tạo liên kết giữa chương trình với CSDL. Sau khi đã tạo được liên kết xong chúng ta mới có thể thực hiện các thao tác xử lý dữ liệu trên CSDL này.
2. Để tạo lập một liên kết Connection chúng ta cần xác định rõ các thông tin cần thiết bao gồm: • Provider: Tên của chương trình giao tiếp dữ liệu (OLE DB Provider). • Data source: Tên đầy đủ của tập tin CSDL hay tên nguồn dữ liệu ODBC cần liên kết. • Username: Đây là một thông tin tùy chọn quy định tên của người dùng được tổ chức bên trong CSDL này. Nó phụ thuộc vào các lọai CSDL, với các CSDL Access chúng ta có thể bỏ qua thông tin này. • Password: Chuỗi mật khẩu tương ứng với User name nhằm thâm nhập CSDL Command Đối tượng Command sẽ cho phép chương trình có thể gọi thực hiện một câu lệnh truy vấn SQL trên CSDL hay gọi thực hiện một thủ tục xử lý đã được cài đặt trong CSDL (Stored procedure). Nếu các thủ tục xử lý trong CSDL này có nhận và trả về giá trị, thì khi đó chúng ta sẽ dùng đối tượng Parameter Với CSDL Access, các truy vấn đã được xây dựng sẵn trong tập tin MDB (bao gồm các truy vấn chọn, thêm, xóa, sửa, ) có thể được xem như là các stored procedure. Recordset Là đối tượng lưu trữ một tập họp các mẫu tin được chọn từ các bảng có trong CSDL. Thông qua các đối tượng Recordset, chúng ta có thể thực hiện các xử lý như hiển thị dữ liệu, cập nhật, tìm kiếm, với CSDL đã nối kết. Thông thường các Command có câu lệnh truy vấn thực hiện dạng
3. SELECT đều trả về một bộ mẫu tin (Recordset). Trong quá trình lập trình CSDL với Visual Basic 6.0 chúng ta có thể tạo ra Recordset bằng nhiều cách tại những thời điểm khác nhau như: dùng ADO control, DataEnvirement hay dùng bằng lệnh. Đối tượng Field Là đối tượng con cho phép chúng ta truy xuất và làm việc với một trường (cột) dữ liệu có trong một bộ mẫu tin Recordset. Với đối tượng field có hai thuộc tính cần quan tâm, đó là: Name (tên trường dữ liệu) và Type (kiểu dữ liệu của trường) Bây giờ chúng ta sẽ đi vào từng thuộc tính và phương thức chính của các đối tượng trên. CONNECTION Sau khi tạo xong liên kết dữ liệu, chúng ta mới có thể tạo ra các đối tượng khác dựa trên liên kết này. 1. Thuộc tính liên kết dữ liệu:
4. Trước khi thực hiện tạo một liên kết dữ liệu, chúng ta cần khai báo các tham số cần thiết cho đối tượng Connection như tên server, tên cơ sở dữ liệu, tên người dùng và mật khẩu. Sau đây là các thuộc tính quan trọng cần khai báo khi tạo một liên kết dữ liệu. _Thuộc tính ConnectionString: là chuỗi ký tự chứa các thông tin cần thiết cho việc nối kết dữ liệu. Chuỗi ký tự này gồm các thành phần thông tin như: _Provider: tên trình liên kết dữ liệu OLE DB _Server: tên máy chủ chứa CSDL cần kết nối _Use ID: mã số người dùng _Password: mật khẩu người dùng _Data Source: nguồn dữ liệu kết nối Ví dụ khi kết nối với một cơ sở dữ liệu SQL Server có tên QLTV chúng ta sẽ có chỗi sConnection như sau: sConnection="Provider=SQLOLEDB;" & "Server=ServerNT; User ID=MyID; " & "Password=MyPWD; Data Source=QLTV" Một số connection string tham khảo từ trang web Erlandsen Data Consulting: Kết nối vào Access database dùng standard security: cn.Open "driver={Microsoft Access Driver (*.mdb)};" & _ "dbq=c:\foldername\databasename.mdb;uid=admin;pwd="
5. Kết nối vào một Access database dùng một workgroup system database: cn.Open "driver={Microsoft Access Driver (*.mdb)};" & _ "dbq=c:\foldername\databasename.mdb;" & _ "systemdb=c:\foldername\databasename.mdw;", _ "userid", "userpassword" Kết nối vào một Access database exclusively: cn.Open "driver={Microsoft Access Driver (*.mdb)};" & _ "dbq=c:\foldername\databasename.mdb;" & _ "exclusive=1;uid=admin;pwd=" Kết nối vào một SQL server dùng standard sequrity: cn.Open "driver={SQL Server};" & _ "server=servername;database=databasename;" & _ "uid=userid;pwd=userpassword" Kết nối vào SQL server dùng trusted connection security: cn.Open "driver={SQL Server};" & _ "server=servername;database=databasename;" & _ "uid=;pwd=" Kết nối vào một SQL server từ name và password:
6. cn.Properties("Prompt") = adPromptAlways cn.Open "driver={SQL Server};" & _ "server=servername;database=databasename" Connecting to a Sybase database using the Sybase System 12 driver: cn.Open "driver={SYBASE ASE ODBC Driver};" & _ "srvr=servername;" & _ "uid=userid;pwd=userpassword" Connecting to a Sybase database using the Sybase System 11 driver: cn.Open "driver={SYBASE SYSTEM 11};" & _ "srvr=servername;" & _ "uid=userid;pwd=userpassword" Connecting to an Oracle database using the current Microsoft ODBC driver: cn.Open "driver={Microsoft ODBC for Oracle};" & _ "server=servername.world;" & _ "uid=userid;pwd=userpassword" Connecting to an Oracle database using the Oracle ODBC driver: cn.Open "driver={Oracle ODBC Driver};" & _ "dbq=databasename;" & _ "uid=userid;pwd=userpassword"
7. Bạn phải định nghĩa dbq databasename trong file tnsnames.ora. Kết nối vào một Excel workbook: cn.Open "driver={Microsoft Excel Driver (*.xls)};" & _ "driverid=790;dbq=c:\foldername\workbookname.xls;" & _ "defaultdir=c:\foldername" Kết nối vào một dBase database: cn.Open "driver={Microsoft dBase Driver (*.dbf)};" & _ "driverid=277;dbq=c:\foldername" ' specify the filename when you open the recordset rs.Open "select * from tablename.dbf", cn, , , adCmdText Bạn phải cần Borland Database Engine (BDE) để cập nhật file dBase. Kết nối vào một Paradox database: cn.Open "driver={Microsoft Paradox Driver (*.db )};" & _ "driverid=538;fil=Paradox 5.X;" & _ "defaultdir=c:\foldername\;" & _ "dbq=c:\foldername\;collatingsequence=ASCII"
8. The extra space character after *.db in the driver name is necessary. Bạn phải cần Borland Database Engine (BDE) để cập nhật file ISAM. Kết nối vào một file text: cn.Open "driver={Microsoft Text Driver (*.txt; *.csv)};" & _ "dbq=c:\foldername\;" & _ "extensions=asc,csv,tab,txt,log,*." rs.Open "select * from filename.csv", cn, adOpenStatic, adLockReadOnly, adCmdText Định dạng của file text được định nghĩa bởi file schema. File thông tin schema luôn luôn được đặt tên là Schema.ini và luôn luôn được đặt trong cùng thư mục với file nguồn. File này cung cấp thông tin về định dạng tổng quát của file, tên cột và kiểu dữ liệu. File Schema.ini luôn luôn được yêu cầu để lấy dữ liệu từ fixed-width data. Một số ví dụ về file Schema.ini Ví dụ file Schema.ini cho file filename.txt chứa tên các cột ở dùng đầu tiên: [filename.txt] Format=TabDelimited ColNameHeader=True MaxScanRows=0
9. CharacterSet=ANSI Ví dụ file Schema.ini cho filefilename.txt chứa dấu ";" phân cách giữa các trường dữ liệu, và hàng đầu tiên chứa các tên của các cột dữ liệu: [filename.txt] Format=Delimited(;) ColNameHeader=True MaxScanRows=0 CharacterSet=ANSI Ví dụ về file Schema.ini cho filefilename.txt chứa fixed width data không có tên các cột dữ liệu ở dòng đầu: [filename.txt] Format=FixedLength ColNameHeader=False Col1=FieldName1 Char Width 30 Col2=FieldName2 Date Width 15 Col3=FieldName3 Integer Width 15 Col4=FieldName4 Float Width 20 CharacterSet=ANSI _Thuộc tính CursorLocation: đây là thuộc tính quy định con trỏ liên kết dữ liệu sẽ được tạo tại máy chủ hay máy con. Các hằng số dùng cho thuộc tính này gồm: • adUseServer: con trỏ liên kết dữ liệu được tạo ra tại máy
10. chủ. Mọi sửa đổi của các người dùng khác trên mạng được thể hiện tức thời trên dữ liệu của máy trạm. • adUseClient: con trỏ liên kết dữ liệu được tạo tại máy trạm. Mọi sửa đổi của người dùng khác trên mạng không được thể hiện trên dữ liệu của máy trạm cho đến khi được truy xuất lại. Với kiểu con trỏ này, dữ liệu trên máy trạm có những thuận lợi mà kiểu adUseServer không có. Thuộc tính này chung cho Connection lẫn Recordset, nghĩa là tất cả các Recordset tạo ra từ một Connection sẽ có cùng kiểu con trỏ nếu không được qui định tường minh. 2.Tạo đối tượng Connection dùng lệnh Tại thời điểm ứng dụng đang thực thi (runtime), chúng ta có thể tạo Connection với các bước sau: 2.1 Bước1: Khai báo tham chiếu đến thư viện ADO Để sử dụng kỹ thuật ADO, ta phải khai báo tham chiếu đến thư viện ADO trong ứng dụng như sau:
11. • Chọn Project | References • Chọn Microsoft ActiveX Data Objects X.X Library, với X.X là phiên bản có trên máy trạm tùy theo hệ thống, sau đó chọn OK. 2.2 Bước 2: Khai báo và khởi tạo một đối tượng Connection Trước tiên, ta khai báo một biến đối tượng kiểu Connection của ADO như sau: Dim cnn as New ADODB.Connection Trong trường hợp ứng dụng có tham chiếu đến nhiều thư viện lập trình ADO, RDO (Remote Data Object), nếu chúng ta khai báo một biến Connection nhưng không chỉ cụ thể Connection thuộc thư viện nào thì đối tượng Connection được tạo ra sẽ thuộc thư viện tham chiếu ưu tiên. Thư viện tham chiếu ưu tiên là thư viện được liên kết trước các thư viện khác trong danh sách của bảng References. 2.3 Bước 3: Khai báo thuộc tính liên kết dữ liệu Gán thuộc tính ConnectionString cho đối tượng Connection cnn theo cú pháp sau: cnn.ConnectionString ="Provider= " cnn.CursorLocation = adUseClient hay adUseServer Các chuổi ConnectionString xin tham khảo phần trên đã giới thiệu. 2.4 Bước 4: Kết nối
12. Dùng phương thức Open của Connection để thực hiện kết nối. Cú pháp sử dụng có dạng: cnn.Open Ví dụ: để thực hiện kết nối với cơ sở dữ liệu Biblio.mdb, ta viết đọan mã như sau: DBName= ActiveWorkbook.Path & "\Biblio.mdb" cnn.ConnectionString="Provider=" & "Microsoft.Jet.OLEDB.4.0; Data Source=" & DBname cnn.CursorLocation=adUseClient cnn.Open 3. Thuộc tính State Trong môi trường nhiều người dùng, để tránh có quá nhiều Connection kết nối vào cơ sở dữ liệu, đôi lúc sau khi tạo liên kết và lấy dữ liệu về máy đơn xong chương trình sẽ đóng kết nối đi. Khi cần thiết sẽ liên kết lại. Trong trường hợp này, chúng ta sẽ dựa vào thuộc tính State của Connection để kiểm tra trạng thái liên kết của Connection đang đóng hay đang mở. Sau khi kết nối, chúng ta dùng thuộc tính State để kiểm tra kết nối có thành công hay không. Hằng |Trị |Ý nghĩa adStateClosed |0 |Không kết nối adStateOpen |1 |Đang kết nối Ví dụ như đọan mã sau: If cnn.State=1 Then Msgbox "Kết nối thành công." Else Msgbox "Kết nối không thành công."
13. End If 4. Phương thức Excecute Sau khi kết nối thành công, ta có thể dùng phương thức Execute của connection để thực hiện truy vấn hay cập nhật dữ liệu. Cú pháp như sau: Set = .Execute ( [ ][, ]) cho truy xuất Hoặc .Execute ( [, ][, ]) cho cập nhật Nếu kiểu chuỗi là: Thì kiểu số Có trị là Tên bảng adCmdTable 2 Câu truy vấn Select adCmdText 1 Tên Query trên Access hoặc Stored Procedure trong SQL Server adCmdStoreProc 4 Chú ý: • Trong trường hợp rút trích đối số không cần chỉ ra. • Đối số có thể không chỉ ra, nhưng nếu chỉ ra thì phải phù hợp với , nếu không sẽ gây lỗi. • Recordset được tạo ra theo cách này có thuộc tính chỉ đọc (ReadOnly)
14. Ví dụ: Dim rstdmnv as New ADOB.Recordset Set rstdmnv=cnn.Execute ("dmnv", , adCmdTable) 5. Đóng liên kết dữ liệu Một liên kết dữ liệu (Connection) sẽ tự động được đóng khi đóng ứng dụng. Tuy nhiên trong chương trình khi cần đóng liên kết dữ liệu lại, chúng ta có thể dùng phương thức Close theo cú pháp sau: cnn.Close COMMAND Sau khi thực hiện kết nối với nguồn dữ liệu qua Connection, chúng ta có thể dùng Command để thực hiện một lệnh truy vấn SQL trên nguồn dữ liệu đó hay gọi thực hiện một thủ tục nội trong cơ sở dữ liệu này. Command đặc biệt hữu dụng khi phải thực hiện nhiều lần cùng một lệnh hay các truy vấn có tham số. Khởi tạo và làm việc với Command Các thuộc tính chính • CommandText: đây là thuộc tính quan trọng nhất của Command. Nội dung của thuộc tính này là chuỗi lệnh SQL, tên truy vấn, tên bảng hoặc tên thủ tục nội muốn thực hiện trên nguồn dữ liệu. • CommandType: là thuộc tính giúp ADO nhanh chóng xác định kiểu lệnh có trong CommandText. Nếu trị của CommandType dùng không đúng kiểu của CommandText thì chương trình sẽ báo lỗi. Chính vì vậy, chúng ta cần phải biết
15. và sử dụng các hằng thường dùng cho CommandType sau: - adCmdText: nếu CommandText là câu truy vấn SQL. - adCmdTable: nếu CommandText là tên bảng của cơ sở dữ liệu. - adCmdStoreProc: nếu CommandText là tên thủ tục nội. - adCmdUnknown: khi không biết kiểu của CommandText. Đây là trị mặc định, trong trường hợp này, trình cung cấp OLEDB sẽ xác định kiểu. Tuy nhiên đây là điều không nên và làm tốn thời gian. - adCmdExecutteNoRecords: nếu CommandText là lệnh hay thủ tục nội không trả về các mẫu tin (như khi cập nhật, xóa, ) và được dùng chung với adCmdStoreProc. - adCmdTabldirect: nếu mở trực tiếp bảng của cơ sở dữ liệu. • ActiveConnection: thuộc tính này xác định liên kết dữ liệu (Connection) mà Command sẽ thực hiện trên đó. Vì thế, nếu bỏ trống thuộc tính này sẽ gây ra lỗi khi thực hiện lệnh. Chúng ta có thể dùng chuỗi ConnectionString của Connection hoặc bản thân Connection đang kết nối để gán trị cho thuộc tính này. Thông thường, khi cần làm việc với nhiều Command, chúng ta nên gán chính đối tượng Connection cho thuộc tính ActiveConnection của các Command vì nếu dùng ConnectionString để gán sẽ tạo ra nhiều liên kết riêng biệt. Làm việc với Command Để làm việc với đối tượng Command, chúng ta sẽ thực hiện các bước sau:
16. Bước 1: Khai báo và cấp phát vùng nhớ biến Command Có hai cách khai báo Command: - Khai báo và cấp phát vùng nhớ cùng lúc Dim cmd As New ADODB.Command - Khai báo trước, khi dùng sẽ cấp phát vùng nhớ sau. Dim cmd As ADODB.Command Set cmd=New ADODB.Command Bước 2: Đặt các thuộc tính cần thiết Các thuộc tính cần thiết cho đối tượng Command gồm: • Thuộc tính CommandText • Thuộc tính CommandType • Thuộc tính ActiveConnection Bước 3: Gọi thực hiện Command Để gọi thực hiện một Command, chúng ta dùng phương thức Execute theo cú pháp đơn giản như sau: • Đối với Command dạng rút trích (Tức là Select ): Set = .Execute() • Đối với Command cập nhật (Insert, Delete, Update, ) .Execute()
17. Với là biến kiểu Recordset của ADO, là biến đối tượng command muốn thực hiện. Ví dụ: Đọan chương trình sau sẽ minh họa việc khởi tạo và gọi thực hiện Command trong Visual Basic (cũng như trong VBA) Giả sử tập tin quản lý nhân sự nhansu.mdb được lưu trữ dưới đây: Const DBPATH="C:\QLNS\Nhansu.mdb" 'Tạo đối tượng command thứ nhất Dim cmd1 As New ADODB.Command Dim rst1 As New ADODB.Recordset cmd1.ActiveConnection="Provider=Microsoft.Jet.OLEDB .4.0;" & "Data Source=" & DBPATH cmd.CommandText="SELECT mato, tento" & "FROM dmto" Set rst1=cmd.Execute() 'Tức là chọn mã tổ, tên tổ từ bảng dmto (danh mục tổ) 'Tạo đối tượng command thứ hai Dim cmd2 As New ADODB.Command Dim rst2 As New ADODB.Recordset Set cmd2.ActiveConnection=cmd.ActiveConnection cmd2.CommandText="SELECT * FROM dmnv" Set rst2=cmd2.Execute()
18. Command có tham số Muốn thực hiện nhiều lần một Command với các điều kiện khác nhau, ta có thể sử dụng Command có tham số. Phần nội dung tiếp theo đây sẽ giới thiệu với các bạn cách tạo và gọi thực hiện Command có tham số tùy theo lọai của đối tượng Command. 1. Với CommandText là chuỗi lệnh SQL Trong trường hợp nội dung của CommandText là chuỗi lệnh SQL, chúng ta có thể dùng dấu ? thay cho các giá trị cụ thể trong phần điều kiện của câu lệnh SQL. Ví dụ để lấy danh sách nhân viên thuộc các tổ khác nhau, chúng ta có thể dùng: cmd.CommandText="Select tento From " & "dmto Where mato=?" Trong ví dụ trên chúng ta thấy ký tự ? đã được thay thế một giá trị cụ thể trong biểu thức điều kiện. Để gọi thực hiện Command có tham số này, chúng ta sẽ sử dụng cú pháp đầy đủ của phương thức Excecute như sau: . Excute(Affected, Parameters, Options) Cả ba tham số trong phương thức Execute đều là tham số tùy chọn. Ý nghĩa của ba tham số này như sau: • Affected: là một biến kiểu Long để nhận về số mẫu tin bị ảnh hưởng của Command. Với các Command rút trích, biến này không nói lên điều gì nhưng với những Command cập nhật chúng ta có thể biết được số mẫu tin được thay đổi hay xóa bởi Command. • Parameters: các giá trị sẽ truyền cho tham số của
19. Command nếu có. Trường hợp Command có nhiều hơn một tham số thì các giá trị được truyền cho tham số của Command sẽ được lưu trong một biến mảng kiểu Variant. Như vậy để gọi thực hiện command trên với Mato cần lọc là chuỗi strMato chúng ta sẽ dùng cú pháp như sau: Set rst=cmd.Execute(, strmato) 2. Với CommandText là tên thủ tục nội Trong trường hợp cần gọi thực hiện một thủ tục nội trong cơ sở dữ liệu, chúng ta cũng tiến hành tương tự chỉ có khác là thuộc tính CommandText sẽ chứa tên thủ tục và CommandType là adCmdStoreProc. Đọan lệnh dưới đây sẽ gọi thực hiện một truy vấn rút trích có tên Q_para để lấy về danh sách các nhân viên có tuổi từ 20 đến 25 (Q_para có chức năng rút trích thông tin các nhân viên có độ tuổi từ t1 đến t2 với t1, t2 là hai tham số của truy vấn). cmd.CommandText = "Q_para" cmd.CommandType = adCmdStoreProc Set rst = cmd.Execute(,Array(20,25)) Chú ý: Cũng như Recordset được tạo từ phương thức Execute của Connection, Recordset được tạo ra theo cách này có thuộc tính chỉ đọc. RECORDSET - BỘ MẪU TIN Recordset còn được gọi là bộ mẫu tin. Đây là đối tượng chính của mô hình ADo, đối tượng được dùng để hiển thị và cập nhật dữ liệu trong lập trình ứng dụng CSDL.
20. Mỗi dòng dữ liệu có trong bộ mẫu tin được gọi là mẫu tin (record). Tuy bộ mẫu tin có thể chứa nhiều mẫu tin nhưng tại một thời điểm, chúng ta chỉ có thể làm việc với đúng một mẫu tin gọi là mẫu tin hiện hành của bộ mẫu tin mà thôi. Mỗi cột dữ liệu có trong bộ mẫu tin được gọi là trường dữ liệu hay Field. Phân lọai Trong quá trình lập trình cơ sở dữ liệu, mỗi khi tạo ra bộ mẫu tin, chúng ta cần xác định trước lọai của bộ mẫu tin cần tạo. Có bốn lọai chính là: Static, Dynamic, Keyset và Forwardonly. Mỗi lọai trên đây sẽ được dùng tùy theo vị trí và ý nghĩa sử dụng như sau: • adOpenStatic: bộ mẫu tin sẽ được tạo tại máy con, mọi thay đổi trên dữ liệu nguồn của Recordset do những người dùng khác thực hiện sẽ không tự động cập nhật. • adOpenDynamic: bộ mẫu tin sẽ được tạo tại máy chủ và mọi thay đổi trên dữ liệu nguồn của Recordset sẽ tự động cập nhật hiển thị cho tất cả những người dùng đang làm việc trên cùng bộ mẫu tin. • adOpenKeyset: tương tự như lọai Dynamic, nhưng không tự cập nhật các mẫu tin bị xóa hay thêm mới bởi các người dùng đang sử dụng chung Recordset, không truy cập được những mẫu tin bị xóa do người dùng khác và các thay đổi do người dùng khác vẫn được hiển thị. • adOpenForwardOnly: bộ mẫu tin được tạo với lọai này sẽ nằm tại máy con như adOpenStatic và chỉ cho phép di chuyển mẫu tin đi tời bằng phương thức MoveNext. Khai báo và khởi tạo bộ mẫu tin 1. Recordset với ADO Data Control Trong quá trình làm việc với điều khiển ADO Data Control
21. trước đây, chúng ta đã từng làm việc với bộ mẫu tin trong điều khiển ADO Data Control thông qua cú pháp: .Recordset Hoặc khai báo biến kiểu ADODB.Recordset, sau đó gán biến Recordset này với bộ mẫu tin có trong điều khiển ADO Data Control Dim rst As New ADODB.Recordset Set rst= .Recordset 2. Tạo Recordset bằng lệnh Làm việc với bộ mẫu tin trong điều khiển ADO Data Control có rất nhiều hạn chế, trong đó nổi bật nhất là phải đặt điều khiển ADo trên các form và các bộ mẫu tin phải được tạo trước tại thời điểm thiết kế. Môi trường lập trình chuyên nghiệp đòi hỏi mỗi người chúng ta phải có thể tạo và xử lý các đối tượng trong mô hình ADO, đặc biệt là đối tượng Recordset, tại thời điểm chạy chương trình. Phần nội dung trình bày dưới đây sẽ trình bày cách khai báo và khởi tạo đối tượng Recordset tại thời điểm chạy chương trình. Để làm việc với đối tượng bộ mẫu tin Recordset, chúng ta cần thực hiện các bước sau: Bước 1: Khai báo biến bộ mẫu tin Dim rst As ADODB.Recordset Bước 2: Cấp phát vùng nhớ trước khi làm việc với rst Set rst=New ADODB.Recordset
22. Bước 3: Khởi tạo các giá trị cho biến bộ mẫu tin rst Sử dụng phương thức Open theo cú pháp dưới đây để khởi tạo cho biến Recordset, rst như sau: rst.Open [Source], [Connection], [Type], [Lock], [Option] Trong đó: • Source: chuỗi xác định nguồn dữ liệu của bộ mẫu tin là tên một bảng hay chuỗi câu lệnh SQL. • Connection: mối kết nối dữ liệu sẽ được dùng. • Type: các hằng số chỉ ra lọai của Recordset. Thông thường, khi viết các ứng dụng chạy trên máy đơn, chúng ta thường dùng lọai adOpenStatic. • Lock: thuộc tính xác định trạng thái cho phép khóa dữ liệu khi cập nhật trên bộ mẫu tin. Các hằng trị của thuộc tính Lock gồm: Trị hằng Ý nghĩa adLockBatchOptimistic |4 |Với kiểu khóa này có thể cập nhật nhiều mẫu tin cùng lúc và các mẫu tin được cập nhật chỉ bị khóa khi phát lệnh cập nhật. adLockOptimistic |3 |Cập nhật từng mẫu tin và mẫu tin được cập nhật chỉ bị khóa khi phát lệnh cập nhật. adLockPessimistic |2 |Với kiểu này, mẫu tin sẽ bị khóa ngay từ khi chuyển sang trạng thái sửa đổi hay thêm mới. Chỉ có thể sử dụng kiểu khóa này với thuộc tính CursorLocation của recordset là adUseServer adLockReadOnly |1 |Chỉ cho phép đọc
23. Việc quy định thuộc tính Lock của các Recordset cũng rất quan trọng. Nếu chúng ta cần tạo ra một bộ mẫu tin chỉ để tính tóan, thì có thể dùng adLockReadOnly. Trong khi đó, nếu muốn tạo một bộ mẫu tin để hiển thị và cập nhật dữ liệu, chúng ta nên dùng giá trị adLockOptimistic, adLockBatchOptimistic hay adLockPessimistic. • Option: là thuộc tính xác định kiểu nguồn dữ liệu Source, có thể có các trị sau: Hằng trị |Ý nghĩa |Trị adCmdTable |Khi Source là tên bảng, tên bảng Query trên Access |2 adCmdText |Khi Source là truy vấn SQL |1 adCmdStoreProc|Khi Source là Stored Procedure trong SQL SERVER |4 Trong một số trường hợp, chúng ta có thể ghép bước 1 và bước 2 thành một bước duy nhất bằng cách vừa khai báo vừa cấp phát vùng nhớ bằng cú pháp như sau: Dim rst As New ADODB.Recordset Các xử lý với bộ mẫu tin Thuộc tính State Tương tự như thuộc tính State đã trình bày trong phần Connection, thuộc tính State của bộ mẫu tin Recordset sẽ chỉ ra trạng thái của bộ mẫu tin là đang mở hay đóng. Thuộc tính này có các hằng trị sau: • adStateClosed (0) trạng thái đóng • adStateOpen (1) trạng thái mở Đóng và mở bộ mẫu tin Để đóng liên kết dữ liệu giữa một đối tượng Recordset với dữ liệu thật có trong cơ sở dữ liệu, chúng ta có thể dùng phương thức Close theo cú pháp
24. Rst.Close Sau đó, khi cần, chúng ta sẽ có thể tạo lại liên kết như dòng lệnh dưới đây: If rst.State=adStateClosed then rst.Open Truy xuất các trường dữ liệu Khi cần truy xuất giá trị của một trường nào đó của Recordset, chúng ta có thể dùng một trong các cách sau: (" ").Value hoặc ! .Value hoặc (index).Value Với Index là số thứ tự trường muốn truy xuất. Giá trị của Index được tính từ 0 đến số trường -1. Các thuộc tính mẫu tin thường dùng • Thuộc tính Recordcount (trị Long, chỉ đọc): thuộc tính này chứa số mẫu tin hiện có trong bộ mẫu tin. Thuộc tính này thường chỉ được cập nhật khi có sự di chuyển mẫu tin hiện hành. Recordcount sẽ có trị là -1 khi không xác định được số mẫu tin hay khi kiểu con trỏ là adOpenForwardOnly. • Thuộc tính BOF và EOF _Thuộc tính BOF: nếu thuộc tính này có giá trị là True thì mẫu tin hiện hành đang đứng trước mẫu tin đầu tiên trong bộ mẫu tin Recordset. Lúc này, mọi thao tác như di chuyển mẫu tin về trước, xóa mẫu tin hiện hành, truy xuất giá trị các field đều tạo ra lỗi. Ở các vị trí khác, thuộc tính BOF có giá trị là False.
25. _Thuộc tính EOF: tương tự như thuộc tính BOF, thuộc tính EOF sẽ chỉ ra mẫu tin hiện hành có đang đứng sau mẫu tin cuối cùng hay không. Nếu đúng, EOF có trị là True, ngược lại sẽ có trị là False. Cũng như BOF, nếu EOF là True, mọi thao tác như di chuyển mẫu tin về sau, xóa mẫu tin hiện hành, truy xuất giá trị các field đều tạo lỗi. • AbsolutePostion (trị Long): thuộc tính chứa số thứ tự của mẫu tin hiện hành trong bộ mẫu tin Recordset, có các hằng đặc biệt sau: _adPosUnknown: (-1) Recordset rỗng, vị trí hiện hành không xác định. _adPosBOF: (-2) vị trí mẫu tin hiện hành ở BOF, nghĩa là thuộc tính BOF là True. _adPosEOF: (-3) vị trí mẫu tin hiện hành ở EOF, nghĩa là thuộc tính EOF là True. • BookMark: (Trị Variant) thuộc tính lưu trữ thông tin vị trí của mẫu tin hiện hành trên Recordset. Chúng ta thường dùng thuộc tính này để lưu trữ và phục hồi vị trí mẫu tin hiện hành của bộ mẫu tin trong các xử lý cập nhật dữ liệu. Mỗi mẫu tin có trị Bookmark khác nhau. Chú ý: Sự khác biệt giữa AbsolutePosition và BookMark: tuy cùng xác định vị trí của mẫu tin hiện hành, nhưng sau khi huỷ một mẫu tin nằm trước vị trí hiện hành, giá trị AbsolutePosition sẽ bị thay đổi còn BookMark thì không. • EditMode: (chỉ đọc) chỉ tình trạng của mẫu tin hiện hành, có các trị sau: Hằng Trị Ý nghĩa adEditNone 0 Tình trạng bình thường adEditInProgress 1 Tình trạng đang sửa đổi chưa cập
26. nhật adEditAdd 2 Tình trạng đang thêm mới chưa cập nhật adEditDelete 4 Tình trạng được đánh dấu hủy chưa cập nhật Kiểm tra Recordset rỗng Thông thường các xử lý trên bộ mẫu tin Recordset chỉ có tác dụng khi bộ mẫu tin đó đang chứa số liệu. Có những xử lý như xóa, sửa, lọc dữ liệu sẽ không họat động được khi bộ mẫu tin đang rỗng. Chính vì lý do này, trong một số xử lý, chúng ta sẽ kiểm tra nếu bộ mẫu tin Recordset sẽ xử lý theo một cách nào đó dựa vào cú pháp sau: If rst.BOF and rst.EOF then Else End If Hoặc If rst.BOF and rst.EOF Then End If <> <xử lý khi recordset rst có dữ liệu Duyệt các mẫu tin của một Recordset Để duyệt qua các mẫu tin của một Recordset, chúng ta có thể dùng một trong hai cấu trúc lệnh sau đây: rst.MoveFirst
27. Do While Not rst.EOF rst.MoveNext Loop Hay là For i=1 to rst.RecordCount rst.AbsolutePostion=i Next Thêm một mẫu tin Cú pháp được dùng là: .AddNew Đọan chương trình dưới đây sẽ minh họa cách thêm một mẫu tin vào Recordset On Error Goto Err_Msg rst.AddNew Exit Sub Err_Msg: Msgbox "Lỗi thêm dữ liệu"
28. Xóa mẫu tin Cú pháp để xóa mẫu tin trong một Recordset là: .Delete 'Xóa mẫu tin hiện hành Tuy nhiên cần lưu ý là sau khi xóa mẫu tin thì mẫu tin hiện hành sẽ không có giá trị. Vì vậy, sau khi xóa, chúng ta cần di chuyển mẫu tin hiện hành sang mẫu tin sau để cập nhật lại mẫu tin hiện hành của Recordset. Đọan chương trình dưới đây sẽ minh họa xử lý xóa mẫu tin hiện hành của một Recordset. Private Sub cmdDelete_Click()'Xử lý nút xóa On Error Goto Err_Msg rst.Delete rst.MoveNext If rst.EOF And Not rst.BOF Then rst.MoveLast End If Exit Sub Err_Msg: Msgbox "Lỗi xóa mẫu tin" End Sub Cập nhật dữ liệu
29. Để ghi lại những thay đổi của mẫu tin hiện hành trong một Recordset chúng ta dùng lệnh theo cú pháp như sau: .CancelUpdate Làm tươi dữ liệu trong Recordset Để làm tươi dữ liệu của bộ mẫu tin Recordset chúng ta có thể sử dụng cú pháp sau: .Sort = " [DESC], [DESC] Ví dụ dòng lệnh dưới đây sẽ sắp xếp dữ liệu của bộ mẫu tin rst theo thứ tự cột TENNV (tên nhân viên) tăng dần và sau đó là NGAYSINH giảm dần. Rst.Sort = "TENNV, NGAYSINH DESC
30. Trường hợp không cần sắp xếp dữ liệu nữa, chúng ta gán chuỗi rỗng cho thuộc tính Sort của Recordset Rst.Sort="" Nếu muốn lọc dữ liệu của bộ mẫu tin theo một điều kiện nào đó, chúng ta có thể dùng thuộc tính Filter theo cú pháp sau: .Filter = " " Khi không muốn lọc nữa, chúng ta gán cho thuộc tính này trị hằng adFilterNone hay chuỗi rỗng. Chú ý: Sau khi gán thuộc tính Filter, chỉ những mẫu tin thỏa mãn biểu thức lọc mới được hiển thị, vị trí hiện hành được di chuyển đến mẫu tin đầu tiên của nhóm hiển thị. Sau khi bỏ lọc, vị trí cũng được di chuyển về vị trí đầu trong danh sách các mẫu tin mới. Có thể lọc theo nhiều điều kiện và các điều kiện nối kết với nhau bằng AND, OR có thể sử dụng các ký tự đại diện (*,?) Tìm kiếm Để tìm kiếm mẫu tin thỏa một điều kiện nào đó trong bộ mẫu tin Recordset chúng ta có thể sử dụng phương thức Find theo cú pháp sau: .Find = sCondition, [nRecordSkip], [nDirection], [startPos] • sCondition: là chuỗi chứa điều kiện cần tìm. Chuỗi này là một điều kiện đơn có dạng: • Các tóan tử thường được dùng là { =,>, =, và
31. Like}. Giá trị trong điều kiện tìm có thể là một chuỗi ký tự được đặt trong cặp dấu nháy đơn '', một con số hay một giá trị ngày nằm giữa hai ký tự # #. • nRecordSkip: là một giá trị tùy chọn chỉ ra số mẫu tin sẽ được bỏ qua trước khi bắt đầu việc tìm kiếm. Tham số này thường được dùng để tìm tiếp theo điều kiện cũ sau khi đã tìm thấy một mẫu tin thỏa điều kiện. • nDirection: là tham số chỉ ra hướng tìm. Mặc nhiên, tham số này sẽ có trị là 1 hay adSearchForWard (tìm theo hướng tới). Khi cần tìm theo hướng ngược lại, chúng ta có thể dùng giá trị -1 hay adSearchBackWard. • startPos: là tham số chỉ ra vị trí bắt đầu tìm. Mặc nhiên lệnh Find sẽ tìm từ vị trí hiện hành (adBookmarkCurrent). Khi cần tìm từ đầu hay tìm ngược từ cuối, chúng ta phải dùng các hằng sau: adBookmarkFirst, adBookmarkLast. Nếu tìm thấy, mẫu tin đầu tiên thỏa mãn điều kiện tìm sẽ trở thành mẫu tin hiện hành. Ngược lại, mẫu tin hiện hành là ở vị trí EOF nếu tìm tới hoặc ở vị trí BOF nếu đang tìm theo chiều ngược. Đọan chương trình dưới đây sẽ tìm mẫu tin có trường NGAYLV trước ngày 1/1/1977 trong bộ mẫu tin rst: sCondition = "NGAYLV<#1/1/1977# vBookmark = rst.Bookmark rst.Find sCondition,,,adBookmarkFirst If rst.EOF Then Msgbox "Không tìm thấy" rst.Bookmark = vBookmark Else
32. Msgbox "Đã tìm thấy" End If Đối tượng Field của Recordset Mỗi cột dữ liệu trong bộ mẫu tin Recordset được gọi là trường hay Field. Chúng ta thường sử dụng các thuộc tính sau của đối tượng Field: • DefinedSize: kích thước khai báo của Field trên nguồn dữ liệu. • Name: tên của Field. • OriginalValue: chỉ ra giá trị của Field, trước khi xảy ra sự thay đổi và chưa được cập nhật. • Type: kiểu dữ liệu của Field được khai báo trên nguồn dữ liệu. • Value: giá trị của Field, đây là thuộc tính mặc nhiên của Field. Tập hợp Fields của Recordset Khi muốn tham chiếu đến tập hợp các trường dữ liệu của recordset, chúng ta sử dụng tập họp Fields. Đọan chương trình sau đây sẽ duyệt qua các trường có trong bộ mẫu tin rst, lấy tên và kiểu dữ liệu của chúng đưa vào Combobox có tên là cmbTruong và cmbKieu: Dim fld As ADODB.Field For Each fld In rst.Fields cmbTruong.AddItem fld.Name
33. cmbKieu.AddItem fld.Type Next fld cmbTruong.ListIndex=0 'Chọn mục đầu tiên Khai báo với từ khóa WithEvents Trong quá trình làm việc, bên cạnh nhu cầu khởi tạo một biến kiểu bộ mẫu tin ADODB.Recordset động trong chương trình, đôi lúc chúng ta còn cần phải sử dụng các xử lý biến cố liên quan đến biến bộ mẫu tin này (ví dụ như biến cố MoveComplete, ). Muốn như vậy chúng ta phải khai báo biến với từ khóa WithEvents theo cú pháp sau đây: Dim | Public] WithEvents rst As ADODB.Recordset Ví dụ sau đây sẽ lấy dữ liệu từ Access database tên budget.mdb. Database này có chứa một bảng tên Budget, có bảy trường dữ liệu: Division, Department, Category, Item, Month, Budget, Actual. Ví dụ này chỉ lấy các dữ liệu có trường Division = 'N. America' và Item = 'Lease'. Sub ADO_Demo() Dim DBFullName As String Dim Cnct As String, Src As String Dim Connection As ADODB.Connection Dim Recordset As ADODB.Recordset Dim Col As Integer Cells.Clear 'Thông tin đường dẫn của dữ liệu DBFullName= ThisWorkbook.Path & "budget.mdb"
34. 'Mở connection Set Connection = New ADODB.Connection Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; " Cnct = Cnct & "Data Source=" & DBFullName & ";" Connection.Open ConnectionString:=Cnct 'Tạo Recordset Set Recordset = New ADODB.Recordset With Recordset 'Lọc Src = "SELECT * FROM Budget WHERE Item = 'Lease' " Src = Src & "and Division = 'N. America'" .Open Source:=Src, ActiveConnection:=Connection 'Điền tên trường vào For Col=0 to Recordset.Fields.Count-1 Range("A1").Offset(0,Col).Value=Recordset.Fie lds(Col).Name Next 'Đưa recordset ra Range("A1").Offset(1,0).CopyFromRecordset Recordset End With 'Giải phóng biến và đóng Connection Set Recordset = Nothing Connection.Close Set Connection = Nothing End Sub