Solved

Crystal Reports - passing Parameters to Report Fails With Access Database

Posted on 2014-02-11
6
645 Views
Last Modified: 2014-02-12
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
Comment
Question by:gcook17
  • 3
  • 2
6 Comments
 
LVL 18

Accepted Solution

by:
vasto earned 250 total points
ID: 39852154
Check if the collection crParameterFieldDefinitions contains the falling key. Do you have keys , which contains just numbers ?
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 39852452
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
 

Author Comment

by:gcook17
ID: 39853953
The keys are strings and are the parameters the report is looking for.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Closing Comment

by:gcook17
ID: 39854119
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 39854370
Access you may have to use ' ' or " " around the parameters

mlmcc
0
 

Author Comment

by:gcook17
ID: 39854436
Thank you.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access 2016 7 33
Documents and settings folder 30 76
Access VBA, adding Progress Bar in code to allow execution. 7 28
Should I keep recordsets open? 3 23
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 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