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()
    ReadX
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


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

'      DOES NOT WORK FOR IMAGE FIELDS  
'
    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.Open
'    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)

   Cnxn.Close
   Set Cnxn = Nothing

   Exit Sub
   
ErrorTrap:
    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.


Thanks





Vincent_MonaghanAsked:
Who is Participating?

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

x
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.Open
    bstream.Write rs.Fields("Photo").Value
    bstream.SaveToFile "c:\publogo.gif", adSaveCreateOverWrite
   
    Image1.Picture = LoadPicture("c:\publogo.gif")
   
    rs.Close
    cn.Close
   
    Exit Sub
   
Errortrap:
    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?
http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/VB_Databases/help.jsp#hi70
GranModCommented:
Closed, 500 points refunded.
GranMod
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.