Print Page | Close Window

Add a picture from MS Access DB

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Writing Code
Forum Discription: .NET programming API, report integration
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=13937
Printed Date: 28 Mar 2024 at 2:57am


Topic: Add a picture from MS Access DB
Posted By: Enzyme80
Subject: Add a picture from MS Access DB
Date Posted: 01 Aug 2011 at 10:50am
Background - a long time ago my company decided to do signature capture.  When they implimented this they decided to save the gif image (the signature) directly into the database as a long binary field.  Now inside of access I can easily get this to print out however I am trying to get it to work with the built in Crystal Reports for VS 2005 and cannot seem to get it to work.
 
I can get the image to display with VB.NET outside of Crystal Reports. 
 
One thing I have been reading is that I should not save the image to the database but rather the path to the image, well this is not really much of a choice as it is a legacy system.
 
Another thing I am reading is that I should have it to a dataset and then pull the image that way.  I would perfer not to do this as I don't have much experience with datasets and the rest of my report is already pulling all of the information that I need.
 
What I would like to know is how would I take a long binary field from MS Access and display the graphic that it creates in Crystal Reports for VS 2005. 
 
Thanks ahead of time with any help provided.



Replies:
Posted By: hilfy
Date Posted: 02 Aug 2011 at 7:30am
Part of the problem may be the format of the image.  I've been able to get this to work with other databases when the image is stored as a jpeg, but never with gif  format.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Posted By: Enzyme80
Date Posted: 03 Aug 2011 at 6:53am

How were you able to do it with jpegs?   My new idea is to get the signature out of the database and save it to a jpeg or a gif (whatever it will allow) and then have a picture in crystal reports and just update the image to the one I just created.

Having a problem no matter how I try to do this - Not sure if this will help anyone but this is how I do it in VBA.
 
lLen = LenB(RS.Fields("SIG"))
            For x = 0 To lLen - 1
                ReDim Preserve GIF(x)
                GIF(x) = RS.Fields("SIG").Value(x)
            Next
           
           
            Open strAccess & "mypic.gif" For Binary Access Write As #lfile Len = UBound(GIF)
            Put #lfile, 1, GIF
            Close #lfile
           
            SIG.SizeMode = 3
            SIG.Picture = strAccess & "mypic.gif"
            SIG.Visible = True
            Kill strAccess & "mypic.gif"
 
tried basically the same thing and I create the mypic.gif but I cannot for the life of me figure out how to get the SIG data into that file.
 
 


Posted By: Enzyme80
Date Posted: 08 Aug 2011 at 6:11am
I was able to figure out how to accomplish this.  Hopefully it will benefit someone else in the future.
 
First add an ole object to the report and select the file you want to use (in my test i used C:\mygif.gif)  I had to make sure it was a GIF but not sure if that is needed.  Make sure you check the link checkbox so if the file gets updated it will reflect on the report.
 
The code below takes SIG (the long binary data from the database) and saves it to mygif.gif. 
 
mytest = "Select SIG from SIG where SIG.CN = 1001 and SIG.[IN] = 101 and SIG.FRM = 1 "
Dim myqbj As New Data.OleDb.OleDbCommand(mytest, objConn)
Dim myresultQry As Data.OleDb.OleDbDataReader = Myqbj.ExecuteReader()

If myresultQry.HasRows Then

     myresultQry.Read()
     Dim pictureData As Byte() = Nothing
     pictureData = myresultQry.Item("SIG")
     Dim fsr As IO.FileStream = New IO.FileStream("C:\mygif.gif",    IO.FileMode.OpenOrCreate, IO.FileAccess.ReadWrite, IO.FileShare.ReadWrite)
     fsr.Write(pictureData, 0, pictureData.Length)
     fsr.Close()
End If



Print Page | Close Window