Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Crystal Reports - passing Parameters to Report Fails With Access Database

Posted on 2014-02-11
6
Medium Priority
?
691 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 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

916 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