Add an image field to a recordset for reporting purpose

Posted on 2004-08-19
Last Modified: 2013-11-25
Hi Experts

I like to know how to add an image field to a recordset and load an image into the field. I am writing a component. Will use this component in crystal 9.0 to display the image in the report.

Can you figure out what am i doing wrong. I am not getting an image in the crystal report. I am getting a string instead of the image.

Public gCn As ADODB.Connection
Public gawCn As Object
Public Sub Load()
End Sub

Public Function showImage() As ADODB.Recordset
    Set gawCn = CreateObject("awConnection.Connection")
    Set gCn = gawCn.GetConnection
    Dim RS As New ADODB.Recordset
    RS.Open gawCn.sql("SELECT * FROM [testtable]"), gCn, adOpenForwardOnly, adLockReadOnly, adCmdText
    Dim reportRecordset As ADODB.Recordset
    Set reportRecordset = New ADODB.Recordset
    Dim Name As String
    Dim DType As ADODB.DataTypeEnum
    Dim size As Integer
    Dim valu As Object
    Dim oStream As ADODB.Stream
    ' Create a new instance
    Set oStream = New ADODB.Stream
    oStream.Type = adTypeBinary
    For I = 0 To RS.Fields.Count - 1
        Name = RS.Fields(I).Name
        DType = RS.Fields(I).Type
        size = RS.Fields(I).DefinedSize
        reportRecordset.Fields.Append Name, DType, size, adFldUpdatable
    Next I
    reportRecordset.Fields.Append "test", adBinary, ?
    'Open the existing Recordset
    Do While Not RS.EOF
        oStream.LoadFromFile "c:\7485_492.jpg"
        For I = 0 To RS.Fields.Count - 1
            reportRecordset.Fields(I).Value = RS.Fields(I).Value
        Next I
        reportRecordset("test").Value = oStream.Read
   Set showImage = reportRecordset
End Function

Question by:PradeepYadhav
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 70

Accepted Solution

Éric Moreau earned 250 total points
ID: 11842130
I have used these methods in the past:

Public Function GetChartPictureInRecordset(ByVal pstrTitle As String, ByVal psngMinValue As Single) As ADODB.Recordset
Dim rstData As ADODB.Recordset

Set rstData = gclsData.GetRecordset("select UserID AS Message, Chart from ReportTempBreakUpChart", adOpenStatic, adLockBatchOptimistic, adUseClient, True)

    ChartFX1.Axis(AXIS_Y).Min = psngMinValue
    rstData.Fields("Message").Value = pstrTitle
    Call SavePictureToDB(GetChartPicture, rstData, "Chart")
    Set GetChartPictureInRecordset = rstData
End Function

Public Function GetChartPicture() As Object
    Set GetChartPicture = ChartFX1.GetPicture(1)
End Function

Public Function SavePictureToDB(PictControl As Object, _
   RS As Object, FieldName As String) As Boolean



'Dim sConn As String
'Dim oConn As New ADODB.Connection
'Dim oRs As New ADODB.Recordset
'sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyDb.MDB;Persist Security Info=False"
'oConn.Open sConn
'oRs.Open "SELECT * FROM MYTABLE", oConn, adOpenKeyset, _

'SavePictureToDB Picture1, oRs, "MYFIELD"

Dim oPict As StdPicture

Dim sDir As String
Dim sTempFile As String
Dim iFileNum As Integer
Dim lFileLength As Long

Dim abBytes() As Byte

On Error GoTo ErrorHandler
If Not TypeOf RS Is ADODB.Recordset Then Exit Function
Set oPict = PictControl  ' GetChartPicture  ' PictControl.Picture
If oPict Is Nothing Then Exit Function

'Save picture to temp file
sDir = GetTempDir
If sDir = "" Then sDir = "C:\"
sTempFile = sDir & "0X2341KLZX.dat"
SavePicture oPict, sTempFile

'read file contents to byte array
iFileNum = FreeFile
Open sTempFile For Binary Access Read As #iFileNum
lFileLength = LOF(iFileNum)
ReDim abBytes(lFileLength)
Get #iFileNum, , abBytes()
'put byte array contents into db field
RS.Fields(FieldName).AppendChunk abBytes()
Close #iFileNum

'Don't return false if file can't be deleted
On Error Resume Next
Kill sTempFile
SavePictureToDB = True
Exit Function
MsgBox Err.Number & ": " & Err.Description
End Function

Assisted Solution

Prestaul earned 250 total points
ID: 11842953
If you are getting a string instead of an image then the problem probably lies with your report setup and not your recordset.  Are you using a TTX file?  If so can you post it here?  If you are using a TTX file then you should walk through it and check all of your field sizes and types as that is by far the most likely culprit in my mind.

Also might be helpful if you post the routine that launches Crystal and calls the posted function.

If you are still worried that the image data is not getting into the recordset propery then try replacing:
reportRecordset.Fields.Append "test", adBinary, ?
reportRecordset.Fields.Append "test", adLongVarBinary, 20000, adFldIsNullable

(The DifinedSize is different by DBMS but I have always used arbitrary values with no problems.)

Also try checking the actual size of the field data after inserting the picture to make sure that the image is there:
debug.print reportRecordset.Fields("test").ActualSize
LVL 70

Expert Comment

by:Éric Moreau
ID: 12377164
points to emoreau

Expert Comment

ID: 12380945
emoreau, did you look at his code to see if it works?  There is nothing wrong with the data that is ending up in his recordset.  The problem is either his field definition (which you did not address) or the way that he is trying to display the image in Crystal (which you did not address).

emoreau posted good code, but it was not the code that the author needed.  There are only two posters.  Please split the points as each of us made valid attempts to help and posted good tips/code.
LVL 70

Expert Comment

by:Éric Moreau
ID: 12380988
no problem splitting points

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Microsoft Access combo box help 2 57
Getting warning: You are about to delete 1 row(s) 9 61
SLMGR Switches Are Not Working On KMS Host 3 169
VB6 ListBox Question 4 62
Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

737 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