Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Crystal Reports - passing Parameters to Report Fails With Access Database

Posted on 2014-02-11
6
Medium Priority
?
672 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 18

Accepted Solution

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

Assisted Solution

by:mlmcc
mlmcc earned 1000 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
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.

 

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 101

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

715 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