db_examples.bas

连接短信网关,嵌入式系统Boot Loader 技术内幕

   On Error GoTo Err_Execute
   cnn1.Execute strSQLRestore, , adExecuteNoRecords
   On Error GoTo 0

   ' 通过再查询记录集检索当前数据。
   rstTitles.Requery

   ' 打印已恢复数据的报告。
   Debug.Print "Data after executing the query " & "to restore the original information"
   PrintOutput rstTitles

   rstTitles.Close
   cnn1.Close
   
   Exit Sub
   
Err_Execute:   ' 将任何由执行查询引起的错误通知用户。
   If Errors.Count > 0 Then
      For Each errLoop In Errors
         MsgBox "Error number: " & errLoop.Number & vbCr & _
            errLoop.Description
      Next errLoop
   End If
   
   Resume Next

End Sub

Public Sub ExecuteCommand(cmdTemp As ADODB.Command, _
   rstTemp As ADODB.Recordset)

   Dim errLoop As Error
   
   ' 运行指定的 Command 对象。捕获错误，必要时检查 Errors 集合。
   On Error GoTo Err_Execute
   cmdTemp.Execute
   On Error GoTo 0

   '通过再查询记录集检索当前数据。
   rstTemp.Requery
   
   Exit Sub

Err_Execute:

   ' 将任何由执行查询引起的错误通知用户。
   If Errors.Count > 0 Then
      For Each errLoop In Errors
         MsgBox "Error number: " & errLoop.Number & vbCr & _
            errLoop.Description
      Next errLoop
   End If
   
   Resume Next

End Sub

Public Sub PrintOutput(rstTemp As ADODB.Recordset)

   ' 枚举 Recordset。
   Do While Not rstTemp.EOF
      Debug.Print "  " & rstTemp!Title & _
         ", " & rstTemp!Type
      rstTemp.MoveNext
   Loop

End Sub


Public Sub ResyncX()        '该范例说明如何使用 Resync 方法刷新静态记录集中的数据。


   Dim strCnn As String
   Dim rstTitles As ADODB.Recordset

   ' 打开连接。
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "

   ' 打开标题表的记录集。
   Set rstTitles = New ADODB.Recordset
   rstTitles.CursorType = adOpenStatic
   rstTitles.LockType = adLockBatchOptimistic
   rstTitles.Open "titles", strCnn, , , adCmdTable

   ' 更改记录集中第一个标题的类型。
   rstTitles!Type = "database"

   ' 显示更改结果。
   MsgBox "Before resync: " & vbCr & vbCr & _
      "Title - " & rstTitles!Title & vbCr & _
      "Type - " & rstTitles!Type

   ' 再次与数据库同步并重新显示结果。
   rstTitles.Resync
   MsgBox "After resync: " & vbCr & vbCr & _
      "Title - " & rstTitles!Title & vbCr & _
      "Type - " & rstTitles!Type

   rstTitles.CancelBatch
   rstTitles.Close

End Sub


Public Sub EditModeX()
'该范例说明如何在打开 Recordset 之前设置 CursorType 和 LockType 属性。
'同时还显示不同情况下 EditMode 的属性值。该过程运行时需要 EditModeOutput 函数。

   Dim cnn1 As ADODB.Connection
   Dim rstEmployees As ADODB.Recordset
   Dim strCnn As String

   ' 使用雇员表中的数据打开记录集。
   Set cnn1 = New ADODB.Connection
   strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   cnn1.Open strCnn
      
   Set rstEmployees = New ADODB.Recordset
   Set rstEmployees.ActiveConnection = cnn1
   rstEmployees.CursorType = adOpenKeyset
   rstEmployees.LockType = adLockBatchOptimistic
   rstEmployees.Open "employee", , , , adCmdTable

   ' 显示不同编辑状态下的 EditMode 属性。
   rstEmployees.AddNew
   rstEmployees!emp_id = "T-T55555M"
   rstEmployees!fname = "temp_fname"
   rstEmployees!lname = "temp_lname"
   EditModeOutput "After AddNew:", rstEmployees.EditMode
   rstEmployees.UpdateBatch
   EditModeOutput "After UpdateBatch:", rstEmployees.EditMode
   rstEmployees!fname = "test"
   EditModeOutput "After Edit:", rstEmployees.EditMode
   rstEmployees.Close
   
   ' 删除新记录，因为这只是演示。
   cnn1.Execute "DELETE FROM employee WHERE emp_id = 'T-T55555M'"

End Sub

Public Function EditModeOutput(strTemp As String, intEditMode As Integer)

   ' 打印基于 EditMode 属性值的报表。
   Debug.Print strTemp
   Debug.Print "  EditMode = ";

   Select Case intEditMode
      Case adEditNone
         Debug.Print "adEditNone"
      Case adEditInProgress
         Debug.Print "adEditInProgress"
      Case adEditAdd
         Debug.Print "adEditAdd"
   End Select

End Function


Public Sub BOFX()
'该范例使用 BOF 和 EOF 属性，在用户试图移过 Recordset 的第一个和最后一个记录时显示一条信息。
'它通过 Bookmark 属性使用户对 Recordset 中的记录进行标记，稍后再返回给它。

   Dim rstPublishers As ADODB.Recordset
   Dim strCnn As String
   Dim strMessage As String
   Dim intCommand As Integer
   Dim varBookmark As Variant

   ' 使用出版商表格数据打开记录集。
   strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   Set rstPublishers = New ADODB.Recordset
   rstPublishers.CursorType = adOpenStatic
   ' 使用客户游标激活 AbsolutePosition 属性。
   rstPublishers.CursorLocation = adUseClient
   rstPublishers.Open "SELECT pub_id, pub_name FROM publishers " & _
      "ORDER BY pub_name", strCnn, , , adCmdText

   rstPublishers.MoveFirst

   Do While True
      ' 显示关于当前记录的信息并让用户输入。
      strMessage = "Publisher: " & rstPublishers!pub_name & _
         vbCr & "(record " & rstPublishers.AbsolutePosition & _
         " of " & rstPublishers.RecordCount & ")" & vbCr & vbCr & _
         "Enter command:" & vbCr & _
         "[1 - next / 2 - previous /" & vbCr & _
         "3 - set bookmark / 4 - go to bookmark]"
      intCommand = Val(InputBox(strMessage))

      Select Case intCommand
         ' 向前或向后移动，捕获 BOF 或 EOF。
         Case 1
            rstPublishers.MoveNext
            If rstPublishers.EOF Then
               MsgBox "Moving past the last record." & _
                  vbCr & "Try again."
               rstPublishers.MoveLast
            End If
         Case 2
            rstPublishers.MovePrevious
            If rstPublishers.BOF Then
               MsgBox "Moving past the first record." & _
                  vbCr & "Try again."
               rstPublishers.MoveFirst
            End If

         ' 将当前记录的书签保存。
         Case 3
            varBookmark = rstPublishers.Bookmark

         ' 转向由存储的书签所指示的记录。
         Case 4
            If IsEmpty(varBookmark) Then
               MsgBox "No Bookmark set!"
            Else
               rstPublishers.Bookmark = varBookmark
            End If

         Case Else
            Exit Do
      End Select

   Loop

   rstPublishers.Close

End Sub



Public Sub BOFX2() '该范例使用 Bookmark 和 Filter 属性创建记录集的限定视图，将只允许访问书签数组所引用的记录。

Dim rs As New ADODB.Recordset
Dim bmk(10)

rs.CursorLocation = adUseClient
rs.ActiveConnection = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=;"

rs.Open "select * from authors", , adOpenStatic, adLockBatchOptimistic
Debug.Print "Number of records before filtering: ", rs.RecordCount

ii = 0
While rs.EOF <> True And ii < 11
    bmk(ii) = rs.Bookmark
    ii = ii + 1
    rs.Move 2
Wend
rs.Filter = bmk
Debug.Print "Number of records after filtering: ", rs.RecordCount

rs.MoveFirst
While rs.EOF <> True
    Debug.Print rs.AbsolutePosition, rs("au_lname")
    rs.MoveNext
Wend
    
End Sub