fritzdsa
asked on
Refresh Problem in Crystal Reports
We have created a VB program which is using Crystal report 8.5 to show 2 .JPG files per record along with other data from a ACCESS database. The .JPG files are stored on the disk and the link to the same is in the database. Well everything works fine I see all the records properly along with the images but if I directly go to the last page I do not see the photo on that page but I do see the correct data. Also when I export the report to a .rtf or .pdf file all data is written properly except for the image data. I see the link at the place where the photo shud have been displayed e.g 14.jpg. Thanks in anticipation.
ASKER
The images are on the disk and we are using crystal viewer to display the image. In CR section we are manually displaying the image in the OLE object.
There should not be any issue, as I am using the same in my project with same configuration VB6, CR8.5 SP1, I had some problem when installed SP3, the system was slow, but did not try the same and got back to SP1.
I have tried exporting the file in both rtf and pdf without any issue.
Hope you are using following syntax of codes in your section_format event
Set pic.FormattedPicture = LoadPicture(fldPhoto.Value )
I have tried exporting the file in both rtf and pdf without any issue.
Hope you are using following syntax of codes in your section_format event
Set pic.FormattedPicture = LoadPicture(fldPhoto.Value
ASKER
We are using CR ver 8.5.0.217 and given below is the code snippet
Thanks in anticipation
Private Sub crsection_Format(ByVal pFormattingInfo As Object)
Dim l_rstRS As New ADODB.Recordset
Dim l_strSQL As String
Dim l_strPhoto As String
Dim l_strSignature As String
Dim l_FileObj As New FileSystemObject
Dim l_lngCount As Long
If m_rstRS.EOF = False Then
l_strPhoto = "C:\Autorec\Apps\sigma-tec h-gray\Exp ort\" & Left(m_rstRS("imagePath"), Len(m_rstRS("imagepath")) - 4) & "_t1.jpg"
l_strSignature = "C:\Autorec\Apps\sigma-tec h-gray\Exp ort\" & Left(m_rstRS("imagePath"), Len(m_rstRS("imagepath")) - 4) & "_t2.jpg"
If l_FileObj.FileExists(l_str Photo) = True Then Set pic1.FormattedPicture = LoadPicture(l_strPhoto)
If l_FileObj.FileExists(l_str Signature) = True Then Set pic2.FormattedPicture = LoadPicture(l_strSignature )
m_rstRS.MoveNext
End If
End Sub
Thanks in anticipation
Private Sub crsection_Format(ByVal pFormattingInfo As Object)
Dim l_rstRS As New ADODB.Recordset
Dim l_strSQL As String
Dim l_strPhoto As String
Dim l_strSignature As String
Dim l_FileObj As New FileSystemObject
Dim l_lngCount As Long
If m_rstRS.EOF = False Then
l_strPhoto = "C:\Autorec\Apps\sigma-tec
l_strSignature = "C:\Autorec\Apps\sigma-tec
If l_FileObj.FileExists(l_str
If l_FileObj.FileExists(l_str
m_rstRS.MoveNext
End If
End Sub
If you can get the fieldvalue in main query, you do not need to open recordset in crystal report format section event.
What I suggest is use FieldName.Value to get path of your photo. you can use
l_strPhoto = "C:\Autorec\Apps\sigma-tec h-gray\Exp ort\" & ImageFieldName.Value, Len(ImageFieldName.Value) - 4) & "_t1.jpg"
l_strSignature = "C:\Autorec\Apps\sigma-tec h-gray\Exp ort\" & Left(ImageField.Value, Len(ImageField.Value) - 4) & "_t2.jpg"
Where ImageFieldName is fieldname coming from your table query.
Also it would be fine if ImagePath is also stored in database as hard coded path is not flexible for porting applications.
What I suggest is use FieldName.Value to get path of your photo. you can use
l_strPhoto = "C:\Autorec\Apps\sigma-tec
l_strSignature = "C:\Autorec\Apps\sigma-tec
Where ImageFieldName is fieldname coming from your table query.
Also it would be fine if ImagePath is also stored in database as hard coded path is not flexible for porting applications.
ASKER
Hello stcindia,
We are actually using Cyrstal report objects with Crystal viewer and not crystal report designer. Even if we work with crystal report designer using imageFieldName.value it fails to display .JPG images but seems to work fine with .BMP images- But even with this method when we export to .rpt or .pdf format the first image is displayed in all the records.
We are actually using Cyrstal report objects with Crystal viewer and not crystal report designer. Even if we work with crystal report designer using imageFieldName.value it fails to display .JPG images but seems to work fine with .BMP images- But even with this method when we export to .rpt or .pdf format the first image is displayed in all the records.
I think, I need more information and codes on how you are passing data to Crystal Reports from VB
Are you using Crystal Reports within your VB Project (.dsr) or Designing outright external (.rpt) files and calling from VB.
Are you using Crystal Reports within your VB Project (.dsr) or Designing outright external (.rpt) files and calling from VB.
ASKER
Hello stcindia,
We are using outright external file i.e .rpt and calling from VB. Two oleobject i.e photo & signature are defined in the .rpt file. Given below is the code.
Dim Report As Report
Dim WithEvents CrSection As Section
Public pic1 As CRAXDDRT.OLEObject ' OLEObject
Public pic2 As CRAXDDRT.OLEObject
Dim app1 As New Application
Dim g_tmpstr(1000) As String
Dim pFormattinginfo1 As Object
Dim prflag As Boolean
Dim v As Integer
Public Flag As Boolean
Public m_lngCurrentPage As Long
Public m_lngTotalPage As Long
Public m_rstRS As New ADODB.Recordset
Public m_TotalRecords As Long
Private Sub crsection_Format(ByVal pFormattingInfo As Object)
Dim l_rstRS As New ADODB.Recordset
Dim l_strSQL As String
Dim l_strPhoto As String
Dim l_strSignature As String
Dim l_FileObj As New FileSystemObject
Dim l_lngCount As Long
If m_rstRS.EOF = False Then
l_strPhoto = "C:\Autorec\Apps\sigma-tec h-gray\Exp ort\" & Left(m_rstRS("imagePath"), Len(m_rstRS("imagepath")) - 4) & "_t1.jpg"
l_strSignature = "C:\Autorec\Apps\sigma-tec h-gray\Exp ort\" & Left(m_rstRS("imagePath"), Len(m_rstRS("imagepath")) - 4) & "_t2.jpg"
If l_FileObj.FileExists(l_str Photo) = True Then Set pic1.FormattedPicture = LoadPicture(l_strPhoto)
If l_FileObj.FileExists(l_str Signature) = True Then Set pic2.FormattedPicture = LoadPicture(l_strSignature )
m_rstRS.MoveNext
End If
End Sub
Private Sub CV_CloseButtonClicked(UseD efault As Boolean)
m_rstRS.Close
End Sub
Private Sub CV_FirstPageButtonClicked( UseDefault As Boolean)
m_lngCurrentPage = 1
m_rstRS.MoveFirst
End Sub
Private Sub CV_GotFocus()
m_lngCurrentPage = 1
End Sub
Private Sub CV_GoToPageNClicked(UseDef ault As Boolean, ByVal PageNumber As Integer)
Dim l_lngCount As Long
m_lngCurrentPage = PageNumber
m_rstRS.MoveFirst
For l_lngCount = 1 To 3 * (m_lngCurrentPage - 1)
m_rstRS.MoveNext
Next l_lngCount
End Sub
Private Sub CV_LastPageButtonClicked(U seDefault As Boolean)
Dim l_lngCount As Long
If m_lngCurrentPage = 0 Then
m_lngCurrentPage = 1
Else
m_lngCurrentPage = m_lngTotalPage 'PageNumber
End If
m_rstRS.MoveFirst
End Sub
Private Sub CV_NextPageButtonClicked(U seDefault As Boolean)
If m_lngCurrentPage = 0 Then
m_lngCurrentPage = 1
Else
m_lngCurrentPage = m_lngCurrentPage + 1
End If
m_rstRS.MoveFirst
For l_lngCount = 1 To 3 * (m_lngCurrentPage - 1)
m_rstRS.MoveNext
Next l_lngCount
End Sub
Private Sub CV_PrevPageButtonClicked(U seDefault As Boolean)
Dim l_lngCount As Long
If m_lngCurrentPage > 1 Then
m_lngCurrentPage = m_lngCurrentPage - 1
Else
m_lngCurrentPage = 1
End If
m_rstRS.MoveFirst
For l_lngCount = 1 To 3 * (m_lngCurrentPage - 1)
m_rstRS.MoveNext
Next l_lngCount
End Sub
Private Sub Form_Load()
Dim OrgVal As Integer
Dim l_rstRS As New ADODB.Recordset
Dim l_strSQL As String
Dim l_strPath As String
OrgVal = 0
Set Report = app1.OpenReport(App.Path & "\Prelist.rpt", 1)
If CrSection Is Nothing Then Set CrSection = Report.Sections("GH")
CV.EnableSelectExpertButto n = True
For I = 1 To CrSection.ReportObjects.Co unt
If CrSection.ReportObjects(I) .Kind = CRObjectKind.crOLEObject Then
OrgVal = OrgVal + 1
If OrgVal = 1 Then
flagpic = True
Set pic1 = CrSection.ReportObjects(I)
ElseIf OrgVal = 2 Then
Set pic2 = CrSection.ReportObjects(I)
End If
End If
Next I
If m_conMatch = "" Then
frmMismatchReport.mp_OpenD atabases
End If
l_strSQL = "select count(*) as C from DataMatch"
l_rstRS.Open l_strSQL, m_conMatch, adOpenDynamic, adLockOptimistic
If l_rstRS.EOF = False Then
m_lngTotalRecord = l_rstRS("c")
l_rstRS.Close
Else
MsgBox "No Matched Records"
Exit Sub
End If
l_strSQL = "select count(*)from DataMatch"
If m_rstRS.State = adStateOpen Then m_rstRS.Close
m_rstRS.Open l_strSQL, m_conMatch, adOpenDynamic, adLockOptimistic
If IsNull(m_rstRS.Fields(0).V alue) = False Then m_TotalRecords = m_rstRS.Fields(0).Value
m_lngTotalPage = Int(m_TotalRecords / 3)
If m_TotalRecords Mod 3 <> 0 Then m_lngTotalPage = m_lngTotalPage + 1
m_rstRS.Close
l_strSQL = "select ImagePath from DataMatch order by FormNo"
m_rstRS.Open l_strSQL, m_conMatch, adOpenDynamic, adLockOptimistic
l_strPath = Left(frmMismatchReport.txt Center, InStrRev(frmMismatchReport .txtCenter , "\", Len(frmMismatchReport.txtC enter), vbTextCompare)) & "\MatchData.mdb"
Report.Database.Tables(1). Location = l_strPath
CV.ReportSource = Report
CV.ViewReport 'Preview the report
CV.Zoom 100
End Sub
We are using outright external file i.e .rpt and calling from VB. Two oleobject i.e photo & signature are defined in the .rpt file. Given below is the code.
Dim Report As Report
Dim WithEvents CrSection As Section
Public pic1 As CRAXDDRT.OLEObject ' OLEObject
Public pic2 As CRAXDDRT.OLEObject
Dim app1 As New Application
Dim g_tmpstr(1000) As String
Dim pFormattinginfo1 As Object
Dim prflag As Boolean
Dim v As Integer
Public Flag As Boolean
Public m_lngCurrentPage As Long
Public m_lngTotalPage As Long
Public m_rstRS As New ADODB.Recordset
Public m_TotalRecords As Long
Private Sub crsection_Format(ByVal pFormattingInfo As Object)
Dim l_rstRS As New ADODB.Recordset
Dim l_strSQL As String
Dim l_strPhoto As String
Dim l_strSignature As String
Dim l_FileObj As New FileSystemObject
Dim l_lngCount As Long
If m_rstRS.EOF = False Then
l_strPhoto = "C:\Autorec\Apps\sigma-tec
l_strSignature = "C:\Autorec\Apps\sigma-tec
If l_FileObj.FileExists(l_str
If l_FileObj.FileExists(l_str
m_rstRS.MoveNext
End If
End Sub
Private Sub CV_CloseButtonClicked(UseD
m_rstRS.Close
End Sub
Private Sub CV_FirstPageButtonClicked(
m_lngCurrentPage = 1
m_rstRS.MoveFirst
End Sub
Private Sub CV_GotFocus()
m_lngCurrentPage = 1
End Sub
Private Sub CV_GoToPageNClicked(UseDef
Dim l_lngCount As Long
m_lngCurrentPage = PageNumber
m_rstRS.MoveFirst
For l_lngCount = 1 To 3 * (m_lngCurrentPage - 1)
m_rstRS.MoveNext
Next l_lngCount
End Sub
Private Sub CV_LastPageButtonClicked(U
Dim l_lngCount As Long
If m_lngCurrentPage = 0 Then
m_lngCurrentPage = 1
Else
m_lngCurrentPage = m_lngTotalPage 'PageNumber
End If
m_rstRS.MoveFirst
End Sub
Private Sub CV_NextPageButtonClicked(U
If m_lngCurrentPage = 0 Then
m_lngCurrentPage = 1
Else
m_lngCurrentPage = m_lngCurrentPage + 1
End If
m_rstRS.MoveFirst
For l_lngCount = 1 To 3 * (m_lngCurrentPage - 1)
m_rstRS.MoveNext
Next l_lngCount
End Sub
Private Sub CV_PrevPageButtonClicked(U
Dim l_lngCount As Long
If m_lngCurrentPage > 1 Then
m_lngCurrentPage = m_lngCurrentPage - 1
Else
m_lngCurrentPage = 1
End If
m_rstRS.MoveFirst
For l_lngCount = 1 To 3 * (m_lngCurrentPage - 1)
m_rstRS.MoveNext
Next l_lngCount
End Sub
Private Sub Form_Load()
Dim OrgVal As Integer
Dim l_rstRS As New ADODB.Recordset
Dim l_strSQL As String
Dim l_strPath As String
OrgVal = 0
Set Report = app1.OpenReport(App.Path & "\Prelist.rpt", 1)
If CrSection Is Nothing Then Set CrSection = Report.Sections("GH")
CV.EnableSelectExpertButto
For I = 1 To CrSection.ReportObjects.Co
If CrSection.ReportObjects(I)
OrgVal = OrgVal + 1
If OrgVal = 1 Then
flagpic = True
Set pic1 = CrSection.ReportObjects(I)
ElseIf OrgVal = 2 Then
Set pic2 = CrSection.ReportObjects(I)
End If
End If
Next I
If m_conMatch = "" Then
frmMismatchReport.mp_OpenD
End If
l_strSQL = "select count(*) as C from DataMatch"
l_rstRS.Open l_strSQL, m_conMatch, adOpenDynamic, adLockOptimistic
If l_rstRS.EOF = False Then
m_lngTotalRecord = l_rstRS("c")
l_rstRS.Close
Else
MsgBox "No Matched Records"
Exit Sub
End If
l_strSQL = "select count(*)from DataMatch"
If m_rstRS.State = adStateOpen Then m_rstRS.Close
m_rstRS.Open l_strSQL, m_conMatch, adOpenDynamic, adLockOptimistic
If IsNull(m_rstRS.Fields(0).V
m_lngTotalPage = Int(m_TotalRecords / 3)
If m_TotalRecords Mod 3 <> 0 Then m_lngTotalPage = m_lngTotalPage + 1
m_rstRS.Close
l_strSQL = "select ImagePath from DataMatch order by FormNo"
m_rstRS.Open l_strSQL, m_conMatch, adOpenDynamic, adLockOptimistic
l_strPath = Left(frmMismatchReport.txt
Report.Database.Tables(1).
CV.ReportSource = Report
CV.ViewReport 'Preview the report
CV.Zoom 100
End Sub
I am not very much familiar with this type of approch (CR Events in VB with RPT files), but I will definately check your codes in my installed environment and revert back.
Vinod
Vinod
The issue has been identified by Business Objects and solved in Service Pack, Apply latest service pack to resolve the problem.
http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do?cmd=displayKC&docType=kc&externalId=c2009770&sliceId=&dialogID=12904708&stateId=1%200%2012902972
Alternatvely try RDC Development useing (dsr) files example can be downloaded from
http://support.businessobjects.com/communityCS/FilesAndUpdates/cr8_vb_rdc_loadpic.exe.asp
http://technicalsupport.businessobjects.com/KanisaSupportSite/search.do?cmd=displayKC&docType=kc&externalId=c2009770&sliceId=&dialogID=12904708&stateId=1%200%2012902972
Alternatvely try RDC Development useing (dsr) files example can be downloaded from
http://support.businessobjects.com/communityCS/FilesAndUpdates/cr8_vb_rdc_loadpic.exe.asp
ASKER
Hi Vinod,
We are still having problems with the report. But now the problem is a little better as the first few pages are exporting right and only the last few pages are showing image path as image. Hope you can think of something
Fritz
We are still having problems with the report. But now the problem is a little better as the first few pages are exporting right and only the last few pages are showing image path as image. Hope you can think of something
Fritz
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Vinod,
Just got back from a long christmas vacation since the delay- yes as mentioned by you earlier we have applied the sp3 and thats why we were able to see the first few pages with the photograph and the last few pages show the image path name as the photograph. also when we goto a page number directly with CR viewer we see the image name as text instead of the image itself.
Fritz
Just got back from a long christmas vacation since the delay- yes as mentioned by you earlier we have applied the sp3 and thats why we were able to see the first few pages with the photograph and the last few pages show the image path name as the photograph. also when we goto a page number directly with CR viewer we see the image name as text instead of the image itself.
Fritz
How are you putting the images into the report?
mlmcc