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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
Closed, 500 points refunded.
The Experts Exchange
Community Support Moderator of all Ages

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.