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

本文曾發行於 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 Sub
  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 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」方法。這樣一來,即更新了資料庫中的新資料。


內容

文章識別碼:180368 - 最後檢閱時間:02/22/2014 15:08:35 - 修訂: 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
意見反應