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

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





0
Vincent_Monaghan
Asked:
Vincent_Monaghan
1 Solution
 
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
0
 
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
0
 
GranModCommented:
Closed, 500 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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