Error reading image from SQL Server

I have written an app to maintain large text and images in SQL Server using VB6. I can get it to work for text, but not for images. Here is the sample code. It runs without error but gives an error when it tries to load the image into the image object (Image1).

Create std VB app and copy code to Form1, also insert image object Image1 into Form1

Reference Microsoft ActiveX Data Objects 2.6


Option Explicit

Private Sub Form_Load()
End Sub

Public Sub ReadX()
    Dim Cnxn As ADODB.Connection
    Dim strCnxn As String
    Dim cmdTEXT As ADODB.Command
    Dim strSQL As String

    Dim filenumber As Long
    Dim strmIn As ADODB.Stream
    Dim strmOut As ADODB.Stream
    Dim strNote As String
    Dim byteChunk() As Byte
    Const TempFile As String = "tempfile.tmp"
    On Error GoTo ErrorTrap

    Set Cnxn = New ADODB.Connection
    Cnxn.Provider = "SQLOLEDB"
    Cnxn.Open "server=(local); database=Northwind; uid=sa"

    Set cmdTEXT = New ADODB.Command
    Set cmdTEXT.ActiveConnection = Cnxn

'    strSQL = "DECLARE @ptrval varbinary(16);" & _
'             "SELECT @ptrval = TEXTPTR(Notes) From Employees Where EmployeeID=1;" & _
'             "READTEXT Employees.Notes @ptrval 0 0"

    strSQL = "DECLARE @ptrval varbinary(16);" & _
             "SELECT @ptrval = TEXTPTR(Photo) From Employees Where EmployeeID=1;" & _
             "READTEXT Employees.Photo @ptrval 0 0"

     cmdTEXT.CommandText = strSQL

' Execute the command, open the return stream, and read the result.
    Set strmOut = New ADODB.Stream
'    strmOut.Type = adTypeBinary
    cmdTEXT.Properties("Output Stream").Value = strmOut
    cmdTEXT.Execute , , adExecuteStream
    strmOut.Position = 0
'   Debug.Print strmOut.ReadText(adReadAll)
    filenumber = FreeFile
    Open TempFile For Binary Access Write As filenumber
    byteChunk() = strmOut.ReadText(adReadAll)
    Put filenumber, , byteChunk()
    Close filenumber
    Image1.Picture = LoadPicture(TempFile)

   Set Cnxn = Nothing

   Exit Sub
    Debug.Print Err.Number & " " & Err.Description

End Sub


I think the problem is with the stream converting the image binary to string, but if I include the line

    strmOut.Type = adTypeBinary

Then the Execute statement throws the folowing error :

3219 Operation is not allowed in this context.


Who is Participating?
Closed, 500 points refunded.
The Experts Exchange
Community Support Moderator of all Ages
Vincent_MonaghanAuthor Commented:
Found the answer myself on the Microsoft Knowledgebase as follows:

Copy the following code to the Form of a std VB6 app with an image object (Image1).
(Add Reference to MS Activex Data Objects 2.5 or greater)

Option Explicit

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim bstream As ADODB.Stream

Private Sub Command1_Click()
    Dim strNOTES As String
On Error GoTo Errortrap

    Set cn = New ADODB.Connection
    cn.Provider = "SQLOLEDB"
    cn.Open "server=(local); database=Northwind; uid=sa; password=sa"
    Set rs = New ADODB.Recordset
    rs.Open "Select * from Employees where employeeID=1", cn, adOpenKeyset, adLockOptimistic
    Set bstream = New ADODB.Stream
    bstream.Type = adTypeBinary
    bstream.Write rs.Fields("Photo").Value
    bstream.SaveToFile "c:\publogo.gif", adSaveCreateOverWrite
    Image1.Picture = LoadPicture("c:\publogo.gif")
    Exit Sub
    Debug.Print "Error:" & Str(Err.Number) & "; Desc:" & Err.Description
    Resume Next
End Sub

It's obvious from this that there is no need to woory about SQL Server pointers to BLOB's as the combination of the ADO recordeset and stream manage this for you!

I wish to close the question now!

Thank you
Anthony PerkinsCommented:
>>I wish to close the question now!<<

Here is how you do it form the EE Help:

I answered my question myself. What do I do?
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.

All Courses

From novice to tech pro — start learning today.