[RESOLVED] How to load an image from a db WITHOUT databinding??

[szlamany]

Ok - just put lots of images into a database table.

Using this method right now to get them into an image control.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "Exec GetStuPhoto_P '" & s3 & "'", gCn, adOpenForwardOnly, adLockReadOnly, adCmdText
If rs.EOF Then
    rs.Close
    Set rs = Nothing
End If
Set .imgImage.DataSource = rs
.imgImage.DataField = rs.Fields(0).Name
rs.Close
Set rs = Nothing

This works fine - takes the place of the old code:

.imgImage.Picture = LoadPicture(s1)

But it requires binding - which requires MSBIND.DLL be on the users machine and registered. I've got over a 1000 PC's that would need this install - not a very happy moment for the IT department!

So .Net does it this way:

Drc.CommandText = "Select ActualImage From Student Where ActualImage is not null and StuId=" & strId
Try
    Dim PictureData As Byte() = DirectCast(Drc.ExecuteScalar, Byte())
    Using stream As New IO.MemoryStream(PictureData)
        Dim im As Image = New Bitmap(stream)
        pbxStudent.Image = im
        pbxStudent.Visible = True
        pbxStudent.BringToFront()
    End Using
Catch ex As Exception
    pbxStudent.Visible = False
End Try

Nice and sweet - picturedata comes into a byte array - memorystream with bitmap turns it back into an image - and it's loaded into the image control.

How could I do this same thing in VB6???

I do not want to take the binary data and create a temp jpg file for loadpicture - that is not an option.

Thanks!

[RhinoBull]

Check my last reply in your other thread (post #20) - really hope it works for you.

[jcis]

Maybe this thread's name should be "ByteArray to Picture" because that coonversion is the real problem, the rest can be done. This code will do everything, the problem is that conversion, i know you don't want to create a temp file but i'll do just that and maybe we can think how to replace just that part:
(I assume you already have Connection and some Image stored somewhere..)

    Set lRs = New ADODB.Recordset
    lRs.Open "SELECT TOP 1 ImageCol FROM TableImages", CN, adOpenKeyset, adLockOptimistic
    Me.Picture = GetPictureFromRecordset(lRs) 'Do something with it
    Set lRs = Nothing

Private Function GetPictureFromRecordset(pRs As ADODB.Recordset) As Picture
    Dim mStream     As ADODB.Stream
    Dim PicBits     As Variant
    
    Set mStream = New ADODB.Stream
    With mStream
        .Type = adTypeBinary
        .Open
        .Write pRs("ImageCol") 'Assuming ImageCol is the Image Column name
        .Position = 0          'Place at start position
        PicBits = .Read()      'Read all, PicBits will be our byte array
        
        'This code needs to be replaced (bytearray to picture conversion needed) 
        .SaveToFile App.Path & "\TempPic.jpg", adSaveCreateOverWrite
        Set GetPictureFromRecordset = LoadPicture(App.Path & "\TempPic.jpg")

    End With
    Set mStream = Nothing
End Function

[RhinoBull]

As Steve pointed out LoadPicture is not an option - we already suggested it in a slightly different way but he rejected it. Check out his other thread fo more info.

I do not want to take the binary data and create a temp jpg file for loadpicture - that is not an option.

[jcis]

Already know that, that's why i said "This code needs to be replaced (bytearray to picture conversion needed)". It will be easier to solve if we reduce it to find another way to make that conversion.

[RhinoBull]

Yea, but we are looking for that "replacement" as we speak so if you have one it would be nice if you post it.

[jcis]

I couldn't find anything about this byte array to bitmap conversion, but I found this method to save/load from/to DB using a property bag.
Save to Table:

Private Sub SaveImageToDB(pPic As Picture, rs As ADODB.Recordset, pColName As String)
    Dim pb As PropertyBag
    
    Set pb = New PropertyBag
    pb.WriteProperty "MyImage", pPic
    rs.Fields(pColName).AppendChunk pb.Contents
    rs.Update
    Set pb = Nothing
End Sub

Get From Recordset:

Private Function GetPictureFromRecordset(rs As ADODB.Recordset, _
                                         pColName As String) As Picture
    Dim pb As PropertyBag

    Set pb = New PropertyBag
    pb.Contents = rs.Fields(pColName).GetChunk(rs.Fields(pColName).ActualSize)
    Set GetPictureFromRecordset = pb.ReadProperty("MyImage")
    Set pb = Nothing
End Function

[szlamany]

I'm guessing that means I would need to change how the image was originally saved - right?

At this time the image were loaded by opening the .JPG file as BINARY and simply loading the binary data.

Looks to me like this method takes a picture control with an image in it and saves that to a property bag. Is that correct?

[RhinoBull]

No, I don't think you have to change anything - that sample simply doesn't work as is. It needs some work and I'm trying few things as we speak but no success yet.
I saw that code before (here is the original by Mattias Sjögren - I know the guys since VB-Zone days).

If I get anything working I will post it. Hopefully you get it first.

[szlamany]

I've gone with RB's suggestion to just go with temp files for now - until I can work out the details of memory methods that DO NOT REQUIRE new DEPENDENCIES!

I wish I was in .Net right now

At any rate - I used JCIS's code - only real change was to .Close before being able to use LOADPICTURE - otherwise it was blowing up.

                Dim rs As ADODB.Recordset
                Set rs = New ADODB.Recordset
                rs.Open "Exec " & Mid(s1, 2) & " '" & s3 & "'", gCn, adOpenForwardOnly, adLockReadOnly, adCmdText
                If rs.EOF Then
                    rs.Close
                    Set rs = Nothing
                End If
                Dim mStream As ADODB.Stream
                Dim PicBits As Variant
                
                Set mStream = New ADODB.Stream
                With mStream
                    .Type = adTypeBinary
                    .Open
                    .Write rs(0)
                    .Position = 0
                    PicBits = .Read()
                    .SaveToFile App.Path & "\" & CStr(glngConnId) & ".jpg", adSaveCreateOverWrite
                    .Close
                End With
                Set mStream = Nothing
                rs.Close
                Set rs = Nothing
                .imgImage.Picture = LoadPicture(App.Path & "\" & CStr(glngConnId) & ".jpg")
                Kill (App.Path & "\" & CStr(glngConnId) & ".jpg")

PS - Si just posted in the other thread - I might go re-visit the memory idea right now...

[szlamany]

Ok - this is now resolved - sorry for having two threads running on this issue.

Resolution is in this thread:

http://www.vbforums.com/showthread.php?t=482854

All done without temp files and without adding install references to the project.

[jcis]

Ok it's resolved, but i'll post some example code of the Property Bag approach. It receives a simple Picture as parameter, not a PictureBox.

'This assumes there is a Table named TableImages with a Column named ImageCol (Image)
Private Sub Command1_Click()
    Dim CN As ADODB.Connection
    Dim lRs As ADODB.Recordset

    Set CN = New ADODB.Connection
    CN.ConnectionString = "Driver={SQL Server};Server=JCI1;Database=Master;Uid=;Pwd=;"
    CN.Open

    Set lRs = New ADODB.Recordset
    lRs.Open "SELECT ImageCol FROM TableImages", CN, adOpenKeyset, adLockOptimistic

    SaveImageToDB Picture1.Picture, lRs, "ImageCol"             'Saves the Picture in Picture1
                                                                'Parameters: Pic, Recordset, Column Name in DB

    'Recreate lRs, it's not needed for this example to run but anyway..
    lRs.Close
    Set lRs = New ADODB.Recordset
    lRs.Open "SELECT ImageCol FROM TableImages", CN, adOpenKeyset, adLockOptimistic

    Picture2.Picture = GetPictureFromRecordset(lRs, "ImageCol") 'Loads the Picture from lRs to Picture2
                                                                'Parameters: Recordset, Column Name in DB

    lRs.Close
    Set lRs = Nothing

    CN.Close
    Set CN = Nothing
End Sub

Private Sub SaveImageToDB(pPic As Picture, rs As ADODB.Recordset, pColName As String)
    Dim pb As PropertyBag

    Set pb = New PropertyBag
    pb.WriteProperty "MyImage", pPic
    rs.Fields(pColName).AppendChunk pb.Contents
    rs.Update
    Set pb = Nothing
End Sub


Private Function GetPictureFromRecordset(rs As ADODB.Recordset, pColName As String) As Picture
    Dim pb As PropertyBag

    Set pb = New PropertyBag
    pb.Contents = rs.Fields(pColName).GetChunk(rs.Fields(pColName).ActualSize)
    Set GetPictureFromRecordset = pb.ReadProperty("MyImage")
    Set pb = Nothing
End Function

[szlamany]

Thanks for following up on that approach - always good to present several options.