ImgEdit display via SQL - urgent

Posted on 2002-03-21
Last Modified: 2008-02-01
Hi there

I'm trying to use the imaging control on a vb form. I can get the control to work via a fixed file location ie(C:\test.gif"). However I want the source to be a BLOB from a SQL database.

This is what I have so far.......

    objRS.Open "SELECT Data FROM Test", cnxn, adOpenKeyset, adLockOptimistic
    mstream.Type = adTypeBinary
    mstream.Write objRS.Fields("Data").Value
    frmControl.ImgEdit1.Image = mstream.Read

But it errors out........on the line

Is this possible or do I have to write the file out, use it and then delete it?

Any help would be appreciated.

Many thanks


PS These are all the points I have available
Question by:john_son74
  • 5
  • 3
  • 2
  • +2
LVL 18

Expert Comment

ID: 6886324
i believe you have to write the file out to a temp file and then delete it.

Author Comment

ID: 6886608
Hi bobbit31

How would I write it out to a temp file?
LVL 18

Expert Comment

ID: 6886665
mstream.SaveToFile "<path>", adSaveCreateOverWrite
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 18

Expert Comment

ID: 6886672
note: to delete the file simply do,

Kill "<path>"

Accepted Solution

tanvirs earned 37 total points
ID: 6887032
If you have SQL-Server installed on your machine.
Find Sample in
C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Samples\ado\unzip_ado.exe

Unzip this file. You will find multiple Dir.
look for VB\employee\
Run Employee.vbp & you will find what you require.
Some of the code i am pasting it here for you.


Private Function FillDataFields()
    On Error GoTo ErrHandler:
    Dim fld As ADODB.Field
    Dim byteChunk() As Byte
    Dim strNote As String
    Dim Offset As Long
    Dim Totalsize As Long
    Dim Remainder As Long
    Dim NumOfChuncks As Long
    Dim CurrentRecPos As Long
    Dim FieldSize As Long
    Dim FileNumber As Integer
    Const HeaderSize As Long = 78
    Const ChunkSize As Long = 100
    Const TempFile As String = "tempfile.tmp"
    ' Initialize fields.
    txtEID.Text = ""
    txtLastName.Text = ""
    txtFirstName.Text = ""
    txtTitle.Text = ""
    txtCTitle.Text = ""
    txtBirthDate.Text = ""
    txtHireDate.Text = ""
    txtAddress.Text = ""
    txtCity.Text = ""
    txtRegion.Text = ""
    txtZipCode.Text = ""
    txtCountry.Text = ""
    txtHomePhone.Text = ""
    txtExtension.Text = ""
    txtNotes.Text = ""
    txtReportsTo.Text = ""
    Image1.Picture = Nothing
    ' Initialize employee name and record position.
    lblName.Caption = ""
    txtRecCnt = ""
    Set Flds = rs.Fields
    ' Display record position.
    txtRecCnt = Str(rs.AbsolutePosition) + " of " + Str(rs.RecordCount)
    ' Assign the CurrentRecPos value.
    CurrentRecPos = rs.AbsolutePosition
    ' Display the employee data.
    For Each fld In Flds
        FieldSize = fld.ActualSize
        If FieldSize > 0 Then                                 ' Rule out the none possibility.
            Select Case fld.Name
                Case "EmployeeID"
                    txtEID.Text = Str(fld.Value)
                Case "LastName"
                    txtLastName.Text = fld.Value
                Case "FirstName"
                    txtFirstName.Text = fld.Value
                Case "Title"
                    txtTitle.Text = fld.Value
                Case "TitleOfCourtesy"
                    txtCTitle.Text = fld.Value
                Case "BirthDate"
                    txtBirthDate.Text = Str(fld.Value)
                Case "HireDate"
                    txtHireDate.Text = Str(fld.Value)
                Case "Address"
                    txtAddress.Text = fld.Value
                Case "City"
                    txtCity.Text = fld.Value
                Case "Region"
                    txtRegion.Text = fld.Value
                Case "PostalCode"
                    txtZipCode.Text = fld.Value
                Case "Country"
                    txtCountry.Text = fld.Value
                Case "HomePhone"
                    txtHomePhone.Text = fld.Value
                Case "Extension"
                    txtExtension.Text = fld.Value
                Case "Photo"
                    FileNumber = FreeFile
                    Open TempFile For Binary Access Write As FileNumber
                    Totalsize = FieldSize - HeaderSize          ' Substract it from the total size.
                    byteChunk() = fld.GetChunk(HeaderSize)      ' Get rid of the header.
                    NumOfChuncks = Totalsize \ ChunkSize
                    Remainder = Totalsize Mod ChunkSize
                    If Remainder > 0 Then
                        byteChunk() = fld.GetChunk(Remainder)
                        Put FileNumber, , byteChunk()
                    End If
                    Offset = Remainder
                    Do While Offset < Totalsize
                        byteChunk() = fld.GetChunk(ChunkSize)
                        Put FileNumber, , byteChunk()
                        Offset = Offset + ChunkSize
                    Close FileNumber
                    Image1.Picture = LoadPicture(TempFile)
                    Kill (TempFile)
                Case "Notes"
                    Totalsize = FieldSize / 2                   ' Becuase of being WChar
                    NumOfChuncks = Totalsize \ ChunkSize
                    Remainder = Totalsize Mod ChunkSize
                    If Remainder > 0 Then
                        strNote = fld.GetChunk(Remainder)
                    End If
                    Offset = Remainder
                    Do While Offset < Totalsize
                        strNote = strNote + fld.GetChunk(ChunkSize)
                        Offset = Offset + ChunkSize
                    txtNotes.Text = strNote
                Case "ReportsTo"
                    Dim FindString As String
                    FindString = "EmployeeID = " + Str(fld.Value)
                    rs.MoveFirst                                    ' Move to the first record to ensure a search for the whole records.
                    rs.Find FindString, 1, adSearchForward          ' Search for the whole records.
                    If rs.EOF = False Then                          ' Did find the match.
                        txtReportsTo.Text = rs!firstname + " " + rs!LastName
                    End If
                    rs.Move (CurrentRecPos - rs.AbsolutePosition)   ' Move back to the record before the search.
            End Select
        End If
    ' Display employee name.
    lblName.Caption = txtFirstName.Text + "  " + txtLastName.Text
    Exit Function
    MsgBox Err.Description, , "Error "
End Function
LVL 18

Expert Comment

ID: 6887103
why did you propose that as an answer? i don't believe it provides any information that john_son doesn't already know. (and i believe i've already answered this question)


Expert Comment

ID: 6887137
No i am affriad not, you have not answered the question for john_son. I have given a perfect example you can also have a look at it.
LVL 18

Expert Comment

ID: 6887157
> Image1.Picture = LoadPicture(TempFile)

The original question was asking if you had to save the blob data from the database to a tempfile and if so, how to do it. (i showed him that)

the above you posted does the same thing (ie. doesn't read the blob data directly from the database)

Expert Comment

ID: 6887440
Thats correct it will write to a file & then display it from the file. but have not explained how to write it to the file.

Author Comment

ID: 6888102
Hi all

I just checked in..........

Tanvirs the code you gave is very good, but it still creates a temp file (which I think is going to be the only way....) and doesnt transpose directly from sql to the image control.

Thanks also Bobbit31 for your input.

Assuming that a temp file is the only way, I will split the points between you (sorry I dont have any more than 75 to give)

Appreciate your help


LVL 16

Expert Comment

ID: 7753508

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
- split points tanvirs bobbit31
Please leave any comments here within the
next seven days.

Expert Comment

ID: 7809846
per recommendation

Community Support Moderator @Experts Exchange

bobbit31 points for you at:

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Paint/Redraw window while dragging 16 83
passing parameter in sql procedure 9 65
Child Form in front 4 49
VBA: copy range dynamically based on config sheet v2 3 50
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

828 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