• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

Saving/Reading images in SQL7 w/ VB6

How do I save an image that the user has dropped into an OLE control to a SQL7 database.  Also, how do I query that db and display the image in the ole control? What is the property of the OLE control that relates to the image, .data?  I am using ADO 2.1 and 3 tiers architecture.  
1 Solution
I've found the answer to your problem....(and mine). Okay some of this code has been used from an answer back in 1999 which came from an example that originally used Oracle. This example connects to whatever open recordset you pass in, with the picture field name.

Method 1: SetPicture puts a picture into the database (this assumes a bitmap file - you can save your OLE image first if you want or modify the code to read the bytes from a IPictureDisp object). To save the picture use the following code:


Method 2: GetPicture retrieves the picture from the current record from a passed in recordset. It does use a temporary file to write out the picture object. It returns an IPictureDisp object. To set the picture property of a picture control you would use the following command:

set picture1.picture = getPicture("picFieldName",rsRecSetThatIsOpenObject")

I hope that helps.

The code:

Public Function setPicture(ByVal strPicturePath As String, strPicField As String, ByVal rs As ADODB.Recordset) As Boolean
  ' note: this requires the record to already exist - it will insert the
  ' picture at the current position in the recordset
  ' Returns true if success - false otherwise
  Const BlockSize = 100000
  Dim ByteData() As Byte   'Byte array for Blob data.
  Dim SourceFile As Integer
  ' Save Picture image to the table column.
  SourceFile = FreeFile
  Open strPicturePath For Binary Access Read As SourceFile
  FileLength = LOF(SourceFile)  ' Get the length of the file.
  If FileLength = 0 Then
    Close SourceFile
    setPicture = False
    Exit Function
    Numblocks = FileLength / BlockSize
    LeftOver = FileLength Mod BlockSize
    ReDim ByteData(LeftOver)
    Get SourceFile, , ByteData()
    rs(strPicField).AppendChunk ByteData()
    ReDim ByteData(BlockSize)
    For i = 1 To Numblocks
      Get SourceFile, , ByteData()
      rs(strPicField).AppendChunk ByteData()
    Next i
    rs.Update   'Commit the new data.
    Close SourceFile
    setPicture = True
  End If
End Function

Public Function getPicture(strPicField As String, ByVal rs As ADODB.Recordset) As IPictureDisp
  Const BlockSize = 100000
  Dim ByteData() As Byte   'Byte array for picture file.
  Dim DestFileNum As Integer
  Dim DiskFile As String

  ' Remove any existing destination file.
  DiskFile = App.Path & "\temp.bmp"
  If Len(Dir$(DiskFile)) > 0 Then
     Kill DiskFile
  End If
  DestFileNum = FreeFile
  FileLength = rs(strPicField).ActualSize
  Open DiskFile For Binary As DestFileNum
  Numblocks = FileLength / BlockSize
  LeftOver = FileLength Mod BlockSize
  rs.Move 0, adBookmarkCurrent
  ByteData() = rs(strPicField).GetChunk(LeftOver)
  Put DestFileNum, , ByteData()
  For i = 1 To Numblocks - 1
      ByteData() = rs(strPicField).GetChunk(BlockSize)
      Put DestFileNum, , ByteData()
  Next i
  Close DestFileNum

  Set getPicture = LoadPicture(App.Path & "\temp.bmp")
End Function
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now