db_examples.bas

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

Attribute VB_Name = "Module1"
Public Sub ExecuteX()
   Dim strSQLChange As String
   Dim strSQLRestore As String
   Dim strCnn As String
   Dim cnn1 As ADODB.Connection
   Dim cmdChange As ADODB.Command
   Dim rstTitles As ADODB.Recordset
   Dim errLoop As ADODB.Error
   ' 定义两个 SQL 语句作为命令文本执行。
   strSQLChange = "UPDATE Titles SET Type = " & _
      "'self_help' WHERE Type = 'psychology'"
   strSQLRestore = "UPDATE Titles SET Type = " & _
      "'psychology' WHERE Type = 'self_help'"
   ' 打开连接。
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   Set cnn1 = New ADODB.Connection
   cnn1.Open strCnn
   ' 创建命令对象。
   Set cmdChange = New ADODB.Command
   Set cmdChange.ActiveConnection = cnn1
   cmdChange.CommandText = strSQLChange
   
   ' 打开标题表。
   Set rstTitles = New ADODB.Recordset
   rstTitles.Open "titles", cnn1, , , adCmdTable

   ' 打印原始数据报告。
   Debug.Print _
      "Data in Titles table before executing the query"
   PrintOutput rstTitles

   ' 清除 Errors 集合的外部错误。
   cnn1.Errors.Clear

   ' 调用 ExecuteCommand 子例程执行 cmdChange 命令。
   ExecuteCommand cmdChange, rstTitles
   
   ' 打印新数据报告。
   Debug.Print _
      "Data in Titles table after executing the query"
   PrintOutput rstTitles

   ' 使用 Connection 对象的 execute 方法执行 SQL 语句以恢复数据。
   ' 捕获错误，必要时检查 Errors 集合。
   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 AddNewX()
   Dim cnn1 As ADODB.Connection
   Dim rstEmployees As ADODB.Recordset
   Dim strCnn As String
   Dim strID As String
   Dim strFirstName As String
   Dim strLastName As String
   Dim booRecordAdded As Boolean
' 打开连接。
   Set cnn1 = New ADODB.Connection
   strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=;"
   cnn1.Open strCnn
   ' 打开 Employee 表。
   Set rstEmployees = New ADODB.Recordset
   rstEmployees.CursorType = adOpenKeyset
   rstEmployees.LockType = adLockOptimistic
   rstEmployees.Open "employee", cnn1, , , adCmdTable
   ' 从用户获取数据，雇员 ID 的格式应为：
   ' 名、中间名和姓的三个首字母，
   ' 五位数字，以及性别标识 M 或 F。
   ' 例如，Bill Sornsin 的雇员 ID 为：B-S55555M。
   strID = Trim(InputBox("Enter employee ID:"))
   strFirstName = Trim(InputBox("Enter first name:"))
   strLastName = Trim(InputBox("Enter last name:"))
   ' 只在用户输入姓和名之后进行。
   If (strID <> "") And (strFirstName <> "") _
      And (strLastName <> "") Then
      rstEmployees.AddNew
      rstEmployees!emp_id = strID
      rstEmployees!fname = strFirstName
      rstEmployees!lname = strLastName
      rstEmployees.Update
      booRecordAdded = True
      ' 显示新添加的数据。
      MsgBox "New record: " & rstEmployees!emp_id & " " & _
         rstEmployees!fname & " " & rstEmployees!lname
   Else
      MsgBox "Please enter an employee ID, " & _
         "first name, and last name."
   End If
   ' 删除新记录，因为这只是演示。
   cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strID & "'"
   rstEmployees.Close
   cnn1.Close
End Sub



Public Sub OpenX() '该范例使用已经打开的 Recordset 和 Connection 对象的 Open 和 Close 方法。
   Dim cnn1 As ADODB.Connection
   Dim rstEmployees As ADODB.Recordset
   Dim strCnn As String
   Dim varDate As Variant

   ' 打开连接。
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   Set cnn1 = New ADODB.Connection
   cnn1.Open strCnn
   
   ' 打开雇员表。
   Set rstEmployees = New ADODB.Recordset
   rstEmployees.CursorType = adOpenKeyset
   rstEmployees.LockType = adLockOptimistic
   rstEmployees.Open "employee", cnn1, , , adCmdTable

   ' 将第一个雇员记录的受雇日期赋值给变量，然后更改受雇日期。
   varDate = rstEmployees!hire_date
   Debug.Print "Original data"
   Debug.Print "  Name - Hire Date"
   Debug.Print "  " & rstEmployees!fname & " " & _
      rstEmployees!lname & " - " & rstEmployees!hire_date
   rstEmployees!hire_date = #1/1/1900#
   rstEmployees.Update
   Debug.Print "Changed data"
   Debug.Print "  Name - Hire Date"
   Debug.Print "  " & rstEmployees!fname & " " & _
      rstEmployees!lname & " - " & rstEmployees!hire_date

   ' 再查询 Recordset 并重置受雇日期。
   rstEmployees.Requery
   rstEmployees!hire_date = varDate
   rstEmployees.Update
   Debug.Print "Data after reset"
   Debug.Print "  Name - Hire Date"
   Debug.Print "  " & rstEmployees!fname & " " & _
      rstEmployees!lname & " - " & rstEmployees!hire_date

   rstEmployees.Close
   cnn1.Close

End Sub

'该范例演示运行来自 Command 对象和 Connection 对象的 Execute 方法。
'同时使用 Requery 方法检索记录集中的当前数据，并用 Clear 方法清除 Errors 集合的内容。
'运行该过程需要 ExecuteCommand 和 PrintOutput 过程。

Public Sub ExecuteX()
   Dim strSQLChange As String
   Dim strSQLRestore As String
   Dim strCnn As String
   Dim cnn1 As ADODB.Connection
   Dim cmdChange As ADODB.Command
   Dim rstTitles As ADODB.Recordset
   Dim errLoop As ADODB.Error

   ' 定义两个 SQL 语句作为命令文本执行。
   strSQLChange = "UPDATE Titles SET Type = " & _
      "'self_help' WHERE Type = 'psychology'"
   strSQLRestore = "UPDATE Titles SET Type = " & _
      "'psychology' WHERE Type = 'self_help'"

   ' 打开连接。
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   Set cnn1 = New ADODB.Connection
   cnn1.Open strCnn

   ' 创建命令对象。
   Set cmdChange = New ADODB.Command
   Set cmdChange.ActiveConnection = cnn1
   cmdChange.CommandText = strSQLChange
   
   ' 打开标题表。
   Set rstTitles = New ADODB.Recordset
   rstTitles.Open "titles", cnn1, , , adCmdTable

   ' 打印原始数据报告。
   Debug.Print _
      "Data in Titles table before executing the query"
   PrintOutput rstTitles

   ' 清除 Errors 集合的外部错误。
   cnn1.Errors.Clear

   ' 调用 ExecuteCommand 子例程执行 cmdChange 命令。
   ExecuteCommand cmdChange, rstTitles
   
   ' 打印新数据报告。
   Debug.Print _
      "Data in Titles table after executing the query"
   PrintOutput rstTitles

   ' 使用 Connection 对象的 execute 方法执行 SQL 语句以恢复数据。
   ' 捕获错误，必要时检查 Errors 集合。