如何:用 ADO 來取出並更新 SQL Server 的文字欄位

文章翻譯 文章翻譯
文章編號: 180368 - 檢視此文章適用的產品。
本文曾發行於 CHT180368
本文已封存。本文係以「現狀」提供且不會再更新。
全部展開 | 全部摺疊

在此頁中

結論

本文向您說明如何使用 ActiveX Data Object(ActiveX 資料物件,ADO)來存取並更新大型文字欄位(二進位大型物件/BLOBS)。此程序是透過 ADODB 記錄集 (RecordSet) 欄位物件的 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 S
    ub
    
  3. 使用預設「Form1」:
    1. 加入一個 RichTextBox 控制項,並將其 Name 內容設為「rtbText」。
    2. 加入一個 CommandButton,並將其 Name 內容設定為「cmdPrev」,Caption 內容為「Prev」。
    3. 加入一個 CommandButton,將其 Name 內容設定為「cmdNext」,Caption 內容為「Next」。
    4. 再加入一個 CommandButton,將其 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 S
    ub
    
  5. 將結合字串中的 ServerName 變更為自己的伺服器名稱。
  6. 執行範例專案。RichTextBox 控制項可包含記錄集的第一筆資料記錄。
  7. 選擇 [Next] 按鈕,可看到 RichTextBox 的內容變更為下一筆記錄,直到最後一筆資料記錄為止。[Next] 按鈕呼叫記錄集的「MoveNext」方法,並呼叫 ColumnToFile 及 FileToForm 方法。
  8. 選擇 [Prev] 按鈕,可看到 RichTextBox 內容變更為前一筆記錄,一直到第一筆資料記錄為止。[Prev] 按鈕呼叫了記錄集的「MovePrevious」方法,並呼叫 ColumnToFile 及 FileToForm 方法。
  9. 在文字方塊中鍵入一些新的文字,然後按一下 [Update],即可對作用資料記錄的文字欄位進行修改。[Update] 按鈕呼叫 FormToFile 及 FileToColumn 方法,然後呼叫記錄集的「Update」方法。這樣一來,即更新了資料庫中的新資料。


屬性

文章編號: 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 Visual Basic 6.0 Enterprise Edition
關鍵字:?
kbnosurvey kbarchive kbhowto kbado200 kbado210sp2 kbmdac200 kbmdac250 kbgrpmdac kbdatabase kbvbp500 kbvbp600 kbsqlserv kbado kbgrpvbdb 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