Error reading image from SQL Server

Posted on 2006-04-10
Last Modified: 2013-12-25
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.


Question by:Vincent_Monaghan

    Author Comment

    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
    LVL 75

    Expert Comment

    by:Anthony Perkins
    >>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?

    Accepted Solution

    Closed, 500 points refunded.
    The Experts Exchange
    Community Support Moderator of all Ages

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now