DOCUMENT:Q180368 04-FEB-1998 [vbwin] TITLE :HOWTO: Retrieve and Update a SQL Server Text Field Using ADO PRODUCT :Microsoft Visual Basic for Windows PROD/VER:WINDOWS:5.0 OPER/SYS:NT Win95 WINDOWS KEYWORDS:vb5all vb5howto ====================================================================== --------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual Basic Control Creation, Professional, and Enterprise Editions for Windows, version 5.0 on the following platforms: NT, Win95 --------------------------------------------------------------------- SUMMARY ======= This article demonstrates how to access and update large text fields (Binary Large Objects/BLOBS) using ADO. This is accomplished using the GetChunk and AppendChunk methods of an ADODB RecordSet's field object. MORE INFORMATION ================ Step by Step Sample Project --------------------------- 1. Open a new project. Form1 is created by default. In Projects, References, set a reference to the Microsoft ActiveX Data Objects 1.5 Library. 2. Add a new standard module to your project and paste in the following code: 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. Add a new form to your project and also do the following: a. Add a RichTextBox control and set its name property to "rtbText." b. Add a CommandButton and set its name property to "cmdPrev" and its Caption property to "Prev." c. Add a CommandButton and set its name property to "cmdNext" and its Caption property to "Next." d. Add a CommandButton and set its name property to "cmdSave" and its Caption property to "Update." 4. Paste the following code into the form: 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=;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. Change the ServerName in the connectstring to your server name. 6. Run the sample project. The RichTextBox will contain the first record of the recordset. 7. Select the "Next" button and you will see the contents of the RichTextBox change to the next record until you reach the last record. The "Next" button is calling the recordset's "MoveNext" method and then calling the ColumnToFile and FileToForm methods. 8. Select the "Prev" button and you will see the contents of the RichTextBox change to the previous record until you reach the first record. The "Prev" button is calling the recordset's "MovePrevious" method and then calling the ColumnToFile and FileToForm methods. 9. Type something new in the text box and click "Update" to modify the text field in whatever record you are on. The "Update" button calls the FormToFile and FileToColumn methods, which in turn calls the recordset's "Update" method. The new data should get updated in the database. (c) Microsoft Corporation 1998, All Rights Reserved. Contributions by Randy Russell, Microsoft Corporation (c) Microsoft Corporation 1998, All Rights Reserved. Contributions by Scott Mason, Microsoft Corporation Additional query words: chunk BLOB ====================================================================== Keywords : vb5all vb5howto Technology : ado Version : WINDOWS:5.0 Platform : NT Win95 WINDOWS Issue type : kbhowto ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 1998.