gcook17
asked on
Crystal Reports - passing Parameters to Report Fails With Access Database
I have a VB.Net 2005 windows forms application that can use either an Access database or SQL Server database. I recently added several new Crystal Reports 2008 .rpt files that the user can open from the main application. The code below works fine when linked to a SQL Server database but fails when linked to Access.
Public Function CRReportWindowParams(ByRef vstrRepName As String, _
ByRef vstrRepTitle As String, _
ByRef vstrRepFormula() As String, _
ByRef vdictParams As Dictionary(Of Object, Object), _
Optional ByRef vblRepSendToPrinter As Boolean = False, _
Optional ByRef vstrPrinterName As String = "", _
Optional ByVal vstrExtraText As String = "", _
Optional ByVal viStartPage As Integer = 0, _
Optional ByVal viEndPage As Integer = 0, _
Optional ByVal viVisibleReport As Boolean = True, _
Optional ByVal viRefNumType As Integer = 2, _
Optional ByVal viRefNum As Integer = 0, _
Optional ByVal viNumCopies As Integer = 1) As Boolean
Dim lobjCRRptDoc As ReportDocument
Dim lstrErrorMsg As String
Dim liErrorCode As Integer
Dim lobjRptForm As frmReportViewer
'RefNumType =2 is a parameter query report
Try
Cursor.Current = Cursors.WaitCursor
'set parameetrs for this report
lobjCRRptDoc = GetReportDocument(vstrRepN ame, vstrRepTitle, vstrRepFormula)
If vblRepSendToPrinter Then 'SEND DIRECTLY TO PRINTER
lobjCRRptDoc.PrintOptions. PrinterNam e = vstrPrinterName
lobjCRRptDoc.PrintToPrinte r(viNumCop ies, False, viStartPage, viEndPage)
Else 'PRINT PREVIEW USER MANUALLY SELECT PRINTER
If viRefNumType = 0 Then
lobjRptForm = New frmReportViewer
Else
lobjRptForm = New frmReportViewer(viRefNumTy pe, viRefNum)
End If
lobjRptForm.Text = vstrRepTitle
mcolRptViewerForms.Add(lob jRptForm)
'lobjRptForm.UseWaitCursor = True
With lobjRptForm.crvRptViewer
'.UseWaitCursor = True
.ShowGotoPageButton = True
.ShowTextSearchButton = True
.ShowGroupTreeButton = True
.EnableDrillDown = True
.ShowPageNavigateButtons = True
.ShowZoomButton = True
.ShowLogo = False 'ms41
.ShowExportButton = True
.ShowPrintButton = True
.ShowRefreshButton = False 'must be false so parameter screen does not appear to unqualified users
.ShowCloseButton = True
.ShowPageNavigateButtons = True
'Set the viewer to the report object to be previewed.
'.ReportSource = lobjCRRptDoc
'Crystal reports parameters ++++++++++++++++++++++++++ ++++++++++ ++
Dim prI As KeyValuePair(Of Object, Object)
For Each prI In vdictParams
Try
Dim crParameterDiscreteValue As ParameterDiscreteValue
Dim crParameterFieldDefinition s As ParameterFieldDefinitions
Dim crParameterFieldLocation As ParameterFieldDefinition
Dim crParameterValues As ParameterValues
' Get the report parameters collection.
crParameterFieldDefinition s = lobjCRRptDoc.DataDefinitio n.Paramete rFields
' Add a parameter value - START
'FAILS HERE ########################## ########
crParameterFieldLocation = crParameterFieldDefinition s.Item(prI .Key.ToStr ing)
'######################### ########## ####### SEE ERROR TEXT BELOW
crParameterValues = crParameterFieldLocation.C urrentValu es
crParameterDiscreteValue = New CrystalDecisions.Shared.Pa rameterDis creteValue
crParameterDiscreteValue.V alue = prI.Value
crParameterValues.Add(crPa rameterDis creteValue )
crParameterFieldLocation.A pplyCurren tValues(cr ParameterV alues)
Catch ex1 As Exception
End Try
Next
'Set the viewer to the report object to be previewed.
.ReportSource = lobjCRRptDoc
'+++++++++++++++++++++++++ ++++++++++ ++++++++++ ++++++++++ ++++++++++
End With
lobjRptForm.OpenReportView er(vstrRep Name, vstrRepTitle, Nothing, lobjCRRptDoc, viRefNumType)
If viVisibleReport Then
lobjRptForm.Show()
Else
lobjRptForm.Visible = False
End If
End If
Return True
ERROR ************************** ********
{"Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"}
************************** ********** *****
I'll also link a picture for the error details from VB.Net. Any ideas of this would be greatly appreciated after wasting 20 hours fighting with this.
ErrorDetail.png
Public Function CRReportWindowParams(ByRef
ByRef vstrRepTitle As String, _
ByRef vstrRepFormula() As String, _
ByRef vdictParams As Dictionary(Of Object, Object), _
Optional ByRef vblRepSendToPrinter As Boolean = False, _
Optional ByRef vstrPrinterName As String = "", _
Optional ByVal vstrExtraText As String = "", _
Optional ByVal viStartPage As Integer = 0, _
Optional ByVal viEndPage As Integer = 0, _
Optional ByVal viVisibleReport As Boolean = True, _
Optional ByVal viRefNumType As Integer = 2, _
Optional ByVal viRefNum As Integer = 0, _
Optional ByVal viNumCopies As Integer = 1) As Boolean
Dim lobjCRRptDoc As ReportDocument
Dim lstrErrorMsg As String
Dim liErrorCode As Integer
Dim lobjRptForm As frmReportViewer
'RefNumType =2 is a parameter query report
Try
Cursor.Current = Cursors.WaitCursor
'set parameetrs for this report
lobjCRRptDoc = GetReportDocument(vstrRepN
If vblRepSendToPrinter Then 'SEND DIRECTLY TO PRINTER
lobjCRRptDoc.PrintOptions.
lobjCRRptDoc.PrintToPrinte
Else 'PRINT PREVIEW USER MANUALLY SELECT PRINTER
If viRefNumType = 0 Then
lobjRptForm = New frmReportViewer
Else
lobjRptForm = New frmReportViewer(viRefNumTy
End If
lobjRptForm.Text = vstrRepTitle
mcolRptViewerForms.Add(lob
'lobjRptForm.UseWaitCursor
With lobjRptForm.crvRptViewer
'.UseWaitCursor = True
.ShowGotoPageButton = True
.ShowTextSearchButton = True
.ShowGroupTreeButton = True
.EnableDrillDown = True
.ShowPageNavigateButtons = True
.ShowZoomButton = True
.ShowLogo = False 'ms41
.ShowExportButton = True
.ShowPrintButton = True
.ShowRefreshButton = False 'must be false so parameter screen does not appear to unqualified users
.ShowCloseButton = True
.ShowPageNavigateButtons = True
'Set the viewer to the report object to be previewed.
'.ReportSource = lobjCRRptDoc
'Crystal reports parameters ++++++++++++++++++++++++++
Dim prI As KeyValuePair(Of Object, Object)
For Each prI In vdictParams
Try
Dim crParameterDiscreteValue As ParameterDiscreteValue
Dim crParameterFieldDefinition
Dim crParameterFieldLocation As ParameterFieldDefinition
Dim crParameterValues As ParameterValues
' Get the report parameters collection.
crParameterFieldDefinition
' Add a parameter value - START
'FAILS HERE ##########################
crParameterFieldLocation = crParameterFieldDefinition
'#########################
crParameterValues = crParameterFieldLocation.C
crParameterDiscreteValue = New CrystalDecisions.Shared.Pa
crParameterDiscreteValue.V
crParameterValues.Add(crPa
crParameterFieldLocation.A
Catch ex1 As Exception
End Try
Next
'Set the viewer to the report object to be previewed.
.ReportSource = lobjCRRptDoc
'+++++++++++++++++++++++++
End With
lobjRptForm.OpenReportView
If viVisibleReport Then
lobjRptForm.Show()
Else
lobjRptForm.Visible = False
End If
End If
Return True
ERROR **************************
{"Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))"}
**************************
I'll also link a picture for the error details from VB.Net. Any ideas of this would be greatly appreciated after wasting 20 hours fighting with this.
ErrorDetail.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I switched the keys to index numbers (Int32) from string and now this works for Access and SQL Server reports. Not sure why it worked with string for SQL Server but not Access though. Thanks.
Access you may have to use ' ' or " " around the parameters
mlmcc
mlmcc
ASKER
Thank you.
ASKER