9 1 2 ADO Recordset Connection Errors Error

9 -1 -2 ADO物件結構 Recordset Connection Errors Error Fields Field Properties Property Record Command Fields Parameters Properties Property Field Stream 主從式資料庫系統 - CH 9 8

以連線物件連結範例(續) 2. 檢查連線物件狀態 1. Public Sub check. Con(i As Integer) 2. On Error Go. To chkcon_err: 3. If i = 1 Then 4. If Not ad. Con. state = ad. State. Open Then Call open. Con() 5. Else 6. Call close. Con 7. End If 8. Exit Sub 9. chkcon_err: 10. Msg. Box Err. Description 11. End Sub 主從式資料庫系統 - CH 9 13

以連線物件連結範例(續) 3. 開啟連線公用程序 1. Public Sub open. Con() 2. On Error Go. To opn. Con_err_end: 3. Dim cn_str As String 4. cn_str = DRIVER = {My. SQL ODBC 3. 51 Driver}; " & _ "SERVER = localhost; " & _ " DATABASE = mysal; " & _ "User = 帳號; Password = 密碼; OPTION=3" 主從式資料庫系統 - CH 9 14

3. 開啟連線公用程序(續) 5. If Not ad. Con. state = ad. State. Open Then 6. ad. Con. Cursor. Location = ad. Use. Client 7. ad. Connection. String = cn_str 8. ad. Con. Open 9. End If 10. Exit Sub 11. opn. Con_err_end: 12. Msg. Box Err. Description 13. End Sub 主從式資料庫系統 - CH 9 15

4. 關閉連線公用程序 1. Public Sub close. Con() 2. If ad. Con. state = ad. State. Open Then 3. ad. Con. Close 4. Set ad. Con = Nothing 5. End If 6. End Sub 主從式資料庫系統 - CH 9 16

2. 開啟紀錄集公用程序 1. Public Function open. Rs(rstr As String) AS ADODB. Recordset 2. On Error Goto open. Rs_err 3. If ad. Rs. state = ad. State. Open Then ad. RS. Close 4. ad. Rs. Open rstr, ad. Con, ad. Open. Dynamic, ad. Lock. Batch. Optimistic 5. ad. Rs. Marshal. Options = ad. Marshal. Modified. Only 6. Set ad. Rs. Active. Connection = Nothing 7. Set open. Rs=ad. RS 8. Exit Function 9. open. RS_er: 10. Msgbox Err. Description 11. End Function 主從式資料庫系統 - CH 9 20

3. 關閉紀錄集 1. Public Sub close. Rs() 2. If ad. Rs. state = ad. State. Open Then 3. ad. Rs. Close 4. Set ad. Rs = Nothing 5. End If 6. End Sub 主從式資料庫系統 - CH 9 21

1. 連線及開啟資料表 n 搭配On Open 事件程序 1. Private Sub Form_Open(Cancel As Integer) 2. Dim str As String 3. Call check. Con(1) 4. str = ”Select * From cuinfo” 5. Call open. Rs(str) 6. End Sub 主從式資料庫系統 - CH 9 23

更新資料程序 n 關閉表單時，搭配On Close事件程序將資料寫回伺服 端 1. Private Sub Form_Close() 2. ad. Rs. Filter = "" 3. Set ad. Rs. Active. Connection = ad. Con 4. ad. Rs. Filter = ad. Filter. Pending. Records 5. ad. Rs. Update. Batch 6. ad. Rs. Close 7. Set ad. Rs = Nothing 8. End Sub 主從式資料庫系統 - CH 9 24

修改/儲存紀錄程序範例 1. Private Sub儲存_Click() 2. ad. Rs. Filter = "“ 3. ad. Rs. Filter = "CU_No ='" & Me!CU_No & "'“ 4. If Not ad. Rs. EOF Then 5. With ad. Rs 6. !CU_No = Me!CU_No 7. ………………. 8. . Update 9. End With 10 End If 11 End Sub 主從式資料庫系統 - CH 9 32

更新記錄程序範例 1. Private Sub更新_Click() 2. ad. Rs. Filter = "“ 3. Set ad. Rs. Active. Connection = ad. Con 4. ad. Rs. Filter = ad. Filter. Pending. Records 5. ad. Rs. Update. Batch 6. Set ad. Rs. Active. Connection =Nothing 7. End Sub 主從式資料庫系統 - CH 9 34

處理指令(續) n設定指令物件為預儲參數 ad. Cmd. Prepared = True n建立參數物件，取名為cuno, 資料型態設為 ad. Varchar，寬度設為 255 Set qry. Par =ad. Cmd. Create. Parameter(“cuno”, ad. Varchar, ad. Parm. Input, 255) n將參數物件加入指令物件 ad. Cmd. Parameters. Append qry. Par 主從式資料庫系統 - CH 9 39

Data. Grid物件應用範例(續) 9. 點開【查閱資料表】事件屬性之On Click程序 編輯視窗，加入下列指令： Private Sub tbl. Cmd_Click() Dim str As String If Is. Null(tbl. Da) Then Exit Sub str = "Select * From " & Me!tbl. Da & "; " Call open. Rs(str) Set dtl. Data. Source = ad. Rs End Sub 主從式資料庫系統 - CH 9 49

Data. Grid物件應用範例(續) 10. 點開【查閱查詢表】事件屬性之On Click程序編輯視窗， 加入下列指令： Private Sub qry. Cmd_Click() Dim str As String, rstr as String Dim rsq As Recordset Dim dbs As Database If Is. Null(qry. Da) Then Exit Sub rstr="Select * from qry. Table Where qry. ID=qry. Da ; Set dbs = Current. Db Set rsq = dbs. Open. Recordset(rst) str = rsq!qry. SQL Call open. Rs(str) Set dtl. Data. Source = ad. Rs End Sub 主從式資料庫系統 - CH 9 50