Solved

Add an image field to a recordset for reporting purpose

Posted on 2004-08-19
8
1,133 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 69

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 69

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 69

Expert Comment

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

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now