Solved

Crystal Reports - passing Parameters to Report Fails With Access Database

Posted on 2014-02-11
6
639 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now