HOWTO: 用 ADO 来检索、更新 SQL Server 的文本字段

文章翻译 文章翻译
文章编号: 180368 - 查看本文应用于的产品
本文的发布号曾为 CHS180368
本文已归档。它按“原样”提供,并且不再更新。
展开全部 | 关闭全部

本文内容

概要

本文向您展示了如何用 ActiveX Data Object(ActiveX 数据对象,ADO)来访问、更新大的文本字段(二进制大对象/BLOBS)。 这一过程是通过 ADODB 记录集字段对象的 GetChunk 及 AppendChunk 方法来实现的。

更多信息

示例工程(步骤分解)



  1. 打开一个新工程。 Form1 是默认创建的窗体。 在“工程”菜单,选择“引用”,把一个引用设置到 Microsoft ActiveX Data Objects Library。
  2. 在工程中添加一个新的标准模块,并贴入以下代码:
       Global cn As ADODB.Connection
       Global cmd1 As ADODB.Command
       Global rsset As ADODB.Recordset
    
       Const BLOCKSIZE As Long = 4096
    
       Public Sub ColumnToFile(Col As ADODB.Field, DiskFile As String)
        'Retrieves data from the database and puts it into a temp file on
        'the hard drive.
        'The size of the chunk is in the variable BLOCKSIZE (4096).
    
       Dim NumBlocks As Long  'Holds the number of chunks.
       Dim LeftOver As Long   '# of chars left over after last whole chunk.
       Dim strData As String
       Dim DestFileNum As Long
       Dim I As Long
       Dim ColSize As Long
    
       'Make sure that you aren't in an empty recordset.
        If Not rsset.EOF And Not rsset.BOF Then
        ColSize = Col.ActualSize
    
       'If filelength > 0, then it is soiled:
       ' throw away contents.
        If Len(Dir$(DiskFile)) > 0 Then
          Kill DiskFile
        End If
    
        DestFileNum = FreeFile
        Open DiskFile For Binary As DestFileNum
        NumBlocks = ColSize \ BLOCKSIZE
        LeftOver = ColSize Mod BLOCKSIZE
    
        'Now Write data to the file in chunks.
         For I = 1 To NumBlocks
         strData = String(BLOCKSIZE, 0)
         strData = Col.GetChunk(BLOCKSIZE)
         Put DestFileNum, , strData
         Next I
         strData = String(LeftOver, 0)
         strData = Col.GetChunk(LeftOver)
         Put DestFileNum, , strData
    
         Close DestFileNum
         End If
          End Sub
    
         Sub FileToColumn(Col As ADODB.Field, DiskFile As String)
         'Takes data from the temp file and saves it to the database.
    
           Dim strData As String
           Dim NumBlocks As Long
           Dim FileLength As Long
           Dim LeftOver As Long
           Dim SourceFile As Long
           Dim I As Long
    
           SourceFile = FreeFile
           Open DiskFile For Binary Access Read As SourceFile
           FileLength = LOF(SourceFile)
           If FileLength = 0 Then
            Close SourceFile
            MsgBox DiskFile & " Empty or Not Found."
           Else
            NumBlocks = FileLength \ BLOCKSIZE
            LeftOver = FileLength Mod BLOCKSIZE
            Col.AppendChunk Null
            strData = String(BLOCKSIZE, 0)
            For I = 1 To NumBlocks
             Get SourceFile, , strData
             Col.AppendChunk strData
            Next I
            strData = String(LeftOver, 0)
            Get SourceFile, , strData
            Col.AppendChunk strData
            rsset.Update
            Close SourceFile
           End If
          End Sub
    
          Public Sub FileToForm(DiskFile As String, SomeControl As Control)
           'Retrieves data from the temp file and puts it onto the control.
    
           Dim SourceFile As Long
           Dim FileLength As Long
           Dim strData As String
    
           SourceFile = FreeFile
           Open DiskFile For Binary Access Read As SourceFile
           FileLength = LOF(SourceFile)
           If FileLength = 0 Then
            Close SourceFile
            MsgBox DiskFile & " Empty or Not Found."
           Else
            strData = String(FileLength, 0)
            Get SourceFile, , strData
            SomeControl.Text = strData
            Close SourceFile
           End If
          End Sub
    
          Sub FormToFile(DiskFile As String, SomeControl As Control)
           'Saves data from the form into a temp file on the local hard drive.
    
           Dim DestinationFile As Long
           Dim FileLength As Long
           Dim strData As String
    
           If Len(Dir$(DiskFile)) > 0 Then
            Kill DiskFile
           End If
           DestinationFile = FreeFile
           Open DiskFile For Binary As DestinationFile
           strData = SomeControl.Text
           Put DestinationFile, , strData
           Close DestinationFile
          End Sub
    
  3. 使用默认的 Form1:
    1. 添加一个 RichTextBox 控件,并将其 Name 属性设为 rtbText。
    2. 添加一个命令按钮,并将其 Name 属性设置为 cmdPrev,Caption 属性为 Prev。
    3. 添加一个命令按钮,将其 Name 属性设置为 cmdNext,Caption 属性为 Next。
    4. 再添加一个命令按钮,将其 Name 属性设置为 cmdSave,Caption 属性为 Update。
  4. 把下列代码粘贴到窗体中:
       Option Explicit
    
       Dim DiskFile As String
    
          Private Sub cmdNext_Click()
           If (rsset.RecordCount > 0) And (Not rsset.EOF) Then
            rsset.MoveNext
            If Not rsset.EOF Then
             rtbText.Text = ""
             ColumnToFile rsset.Fields("pr_info"), DiskFile
             FileToForm DiskFile, rtbText
            Else
             rsset.MoveLast
            End If
           End If
          End Sub
    
          Private Sub cmdPrev_Click()
           If (rsset.RecordCount > 0) And (Not rsset.BOF) Then
            rsset.MovePrevious
            If Not rsset.BOF Then
             rtbText.Text = ""
             ColumnToFile rsset.Fields("pr_info"), DiskFile
             FileToForm DiskFile, rtbText
            Else
             rsset.MoveFirst
            End If
           End If
          End Sub
    
          Private Sub cmdSave_Click()
           FormToFile DiskFile, rtbText
           FileToColumn rsset.Fields("pr_info"), DiskFile
          End Sub
    
          Private Sub Form_Activate()
           rtbText.Text = ""
           If rsset.RecordCount > 0 Then
            rsset.MoveFirst
            ColumnToFile rsset.Fields("pr_info"), DiskFile
            FileToForm DiskFile, rtbText
           End If
          End Sub
    
          Private Sub Form_Load()
    
           Dim ConnectString As String
           Dim anerror As ADODB.Error
           Dim Sql As String
    
           On Error GoTo handler
    
           DiskFile = App.Path & "\BLOB.txt"
    
           'Set the connect string to use pubs on your SQL server.
           ConnectString = _
           "Driver={SQL SERVER};Server=<yourserver>;Database=pubs;UID=sa;pwd=;"
           Sql = "SELECT pr_info FROM pub_info;"
           Set cn = New ADODB.Connection
           cn.ConnectionString = ConnectString
           cn.Open
           Set rsset = New ADODB.Recordset
           rsset.Open Sql, cn, adOpenKeyset, adLockOptimistic, adCmdText
          Exit Sub
    
          handler:
           For Each anerror In cn.Errors
            Debug.Print anerror.Number & ":  " & anerror.Description & _
            " - " & anerror.SQLState
           Next anerror
          End Sub
    
  5. 把连接字符串中的 ServerName 改成您自己服务器的名称。
  6. 运行示例工程。 RichTextBox 控件将包含记录集的第一条记录。
  7. 选择 Next 按钮,您将看到 RichTextBox 的内容更改到下一记录,直到最后一条记录。 Next 按钮调用了记录集的 MoveNext 方法,然后调用 ColumnToFile 及 FileToForm 方法。
  8. 选择 Prev 按钮,可看到 RichTextBox 内容更改到前一记录,一直到第一条记录。 Prev 按钮调用了记录集的 MovePrevious 方法,然后调用 ColumnToFile 及 FileToForm 方法。
  9. 在文本框中键入一些新的文字,然后单击 Update,即可对当前记录的文本字段进行修改。 Update 按钮调用了 FormToFile 及 FileToColumn 方法,然后调用记录集的 Update 方法。 这样,数据库中的新数据应得到了更新。
(c) Microsoft Corporation 1999,保留所有权利。 作者:Randy Russell, Microsoft Corporation.



(c) Microsoft Corporation 1999,保留所有权利。 作者:Scott Mason, Microsoft Corporation。

属性

文章编号: 180368 - 最后修改: 2014年2月22日 - 修订: 1.1
这篇文章中的信息适用于:
  • Microsoft ActiveX Data Objects 1.5
  • Microsoft ActiveX Data Objects 2.0
  • Microsoft ActiveX Data Objects 2.1 Service Pack 2
  • Microsoft ActiveX Data Objects 2.5
  • Microsoft ActiveX Data Objects 2.6
  • Microsoft Visual Basic 6.0 专业版?当用于
    • Microsoft Windows NT 4.0
    • Microsoft Windows 95
  • Microsoft Visual Basic 5.0 专业版?当用于
    • Microsoft Windows NT 4.0
    • Microsoft Windows 95
  • Microsoft Visual Basic 6.0 企业版?当用于
    • Microsoft Windows NT 4.0
    • Microsoft Windows 95
  • Microsoft Visual Basic 5.0 企业版?当用于
    • Microsoft Windows NT 4.0
    • Microsoft Windows 95
  • Microsoft Office 97 专业版?当用于
    • Microsoft Windows NT 4.0
    • Microsoft Windows 95
关键字:?
kbnosurvey kbarchive kbhowto kbado200 kbado210sp2 kbado250 kbado260 kbmdac200 kbmdac250 kbmdac260 kbdatabase kbvbp500 kbgrpdsvbdb kbvbp600 kbsqlserv kbgrpdsmdac kbado KB180368
Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性,不作任何声明。 所有该等文件及有关图形均"依样"提供,而不带任何性质的保证。Microsoft和/或其各供应商特此声明,对所有与该等信息有关的保证和条件不负任何责任,该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下,在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中,Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润不负任何责任。

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com