Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 694
  • Last Modified:

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(vstrRepName, vstrRepTitle, vstrRepFormula)

            If vblRepSendToPrinter Then 'SEND DIRECTLY TO PRINTER
                lobjCRRptDoc.PrintOptions.PrinterName = vstrPrinterName
                lobjCRRptDoc.PrintToPrinter(viNumCopies, False, viStartPage, viEndPage)

            Else 'PRINT PREVIEW USER MANUALLY SELECT PRINTER
                If viRefNumType = 0 Then
                    lobjRptForm = New frmReportViewer
                Else
                    lobjRptForm = New frmReportViewer(viRefNumType, viRefNum)
                End If
                lobjRptForm.Text = vstrRepTitle
                mcolRptViewerForms.Add(lobjRptForm)
                '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 crParameterFieldDefinitions As ParameterFieldDefinitions
                            Dim crParameterFieldLocation As ParameterFieldDefinition
                            Dim crParameterValues As ParameterValues

                            ' Get the report parameters collection.
                            crParameterFieldDefinitions = lobjCRRptDoc.DataDefinition.ParameterFields
                            ' Add a parameter value - START
'FAILS HERE ##################################                            
crParameterFieldLocation = crParameterFieldDefinitions.Item(prI.Key.ToString)
'########################################## SEE ERROR TEXT BELOW
                            crParameterValues = crParameterFieldLocation.CurrentValues
                            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
                            crParameterDiscreteValue.Value = prI.Value
                            crParameterValues.Add(crParameterDiscreteValue)
                            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

                        Catch ex1 As Exception

                        End Try
                    Next
                    'Set the viewer to the report object to be previewed.
                    .ReportSource = lobjCRRptDoc
                    '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
                End With
                lobjRptForm.OpenReportViewer(vstrRepName, 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
0
gcook17
Asked:
gcook17
  • 3
  • 2
2 Solutions
 
vastoCommented:
Check if the collection crParameterFieldDefinitions contains the falling key. Do you have keys , which contains just numbers ?
0
 
mlmccCommented:
I am having trouble following the code since it appears if the report is sent to the printer directly, parameters aren't used but if viewed then parameters are used.

Are all parameters strings?

mlmcc
0
 
gcook17Author Commented:
The keys are strings and are the parameters the report is looking for.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
gcook17Author Commented:
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.
0
 
mlmccCommented:
Access you may have to use ' ' or " " around the parameters

mlmcc
0
 
gcook17Author Commented:
Thank you.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now