Solved

Add an image field to a recordset for reporting purpose

Posted on 2004-08-19
8
1,155 Views
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()
     showImage
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
    oStream.Open
   
   
    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, ?
    reportRecordset.Open
   
    'Open the existing Recordset
    Do While Not RS.EOF
        oStream.LoadFromFile "c:\7485_492.jpg"
        reportRecordset.AddNew
        For I = 0 To RS.Fields.Count - 1
            reportRecordset.Fields(I).Value = RS.Fields(I).Value
        Next I
        reportRecordset("test").Value = oStream.Read
        reportRecordset.Update
        reportRecordset.MoveNext
        RS.MoveNext
    Loop
   
   Set showImage = reportRecordset
   
End Function






0
Comment
Question by:PradeepYadhav
  • 3
  • 2
8 Comments
 
LVL 70

Accepted Solution

by:
É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.AddNew
    rstData.Fields("Message").Value = pstrTitle
    Call SavePictureToDB(GetChartPicture, rstData, "Chart")
    rstData.Update
    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

'PURPOSE: SAVES PICTURE IN IMAGEBOX, PICTUREBOX, OR SIMILAR
'CONTROL TO RECORDSET RS IN FIELD NAME FIELDNAME

'FIELD TYPE MUST BE BINARY (OLE OBJECT IN ACCESS)


'SAMPLE USAGE
'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, _
   adLockOptimistic
'oRs.AddNew

'SavePictureToDB Picture1, oRs, "MYFIELD"
'oRs.Update
'oRs.Close

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
ErrorHandler:
MsgBox Err.Number & ": " & Err.Description
End Function
0
 
LVL 4

Assisted Solution

by:Prestaul
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, ?
with
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
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 12377164
points to emoreau
0
 
LVL 4

Expert Comment

by:Prestaul
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.
0
 
LVL 70

Expert Comment

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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

808 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