?
Solved

Add an image field to a recordset for reporting purpose

Posted on 2004-08-19
8
Medium Priority
?
1,181 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
[X]
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
8 Comments
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 1000 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 1000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

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…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
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…
Suggested Courses
Course of the Month13 days, 2 hours left to enroll

777 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