Question Export Crystal Report to PDF file - causes CR Params Dialog Popup in CmdLineApp

shell_l_d

New member
Joined
Sep 21, 2010
Messages
4
Programming Experience
Beginner
Command Line Project:
Export crystal report to file in pdf/excel/word format & optionally email it.
Command line args incl input parameters for the crystal report.
VB6 being converted to VB.net using Visual Basic 2005 Express Edition.
Creator has since left & I need to fix/edit the application.

Having problems... when call Export, CR params dialog box pops up awaiting user input (& params not set properly), dont want this to popup.

Googling lots, cant find answer, examples use these references, but causes namespace or type not found in my project.
VB.NET:
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
    Dim objReport As New ReportDocument 
    Dim paraValue As New ParameterDiscreteValue
    Dim currValue As ParameterValues
    objReport.SetParameterValue("@whichclient", Value.ToString)

EXTRACTS of the VB.net code:
VB.NET:
'* References: added
'EventSystem 1.0 Type Library; COM; 1.0; C:\Windows\system32\es.dll
'Microsoft CDO for Windows 2000 Library; COM; 1.0; C:\Windows\system32\cdosys.dll
'Microsoft Scripting Runtime; COM; 1.0; C:\Windows\system32\scrrun.dll
'OLE Automation; COM; 2.0; C:\Windows\system32\stdole2.tlb
'Crystal Reports 9 ActiveX Designer Run Time Library; COM; 9.0; C:\Program Files\Crystal Decisions\Report Designer Component\craxdrt9.dll
'Crystal Report Viewer Control 9; COM; 9.2; C:\Program Files\Common Files\Crystal Decisions\2.0\crystalreportviewers\ActiveXViewer\crviewer9.dll
'Crystal Reports 9 Library; COM; 9.2; C:\Program Files\Crystal Decisions\Report Designer Component\craxdui.dll

'* References: couldnt add, said already existed:
'Microsoft ActiveX Data Objects 2.8 Library; C:\Program Files\Common Files\System\ado\msado15.dll

    Dim strRepCmdLnArgs(20) As String
    Dim strParamName(20) As String
    Dim strParamValue(20) As String
    Dim strReportParamName(20) As String
    Dim strReportParamValue(20) As String
    Dim a_strArgs() As String

...etc...

    Public Sub SendEmail(ByVal pi_strRecipient As String, ByVal pi_strSender As String, ByVal pi_strSubject As String, ByVal pi_strMessage As String, ByVal pi_strSMTP As String)
        Dim objMail = New clsEmail
        Call objMail.SendEmail(pi_strRecipient, pi_strSubject, pi_strMessage, pi_strSender, pi_strSMTP, strExportFilename)
        Exit Sub
    End Sub

    Sub Main()
	...etc...
        a_strArgs = Environment.GetCommandLineArgs()

        'Get parameter & report parameter names & values from command line (0=path/appname)
        For intCounter = 1 To UBound(a_strArgs)

            intEqualPos = InStr(a_strArgs(intCounter), "=")
            intStrLen = Len(a_strArgs(intCounter))
            strRepCmdLnArgs = a_strArgs(intCounter).Split(New Char() {","c})

            'Fill arrays: strParamName, strParamValue, strReportParamName, strReportParamValue with commandline args passed
            For intCounter2 = 0 To UBound(strRepCmdLnArgs)
                strRepCmdLnArgs(intCounter2) = Replace(strRepCmdLnArgs(intCounter2), "'", String.Empty)
                intEqualPos = InStr(strRepCmdLnArgs(intCounter2), "=")
                intStrLen = Len(strRepCmdLnArgs(intCounter2))
                strTempValue = Right(strRepCmdLnArgs(intCounter2), intStrLen - intEqualPos)
                'if it's a report parameter field, it starts with @@
                If Left(strRepCmdLnArgs(intCounter2), 2) = "@@" Then
                    strTempName = Mid(strRepCmdLnArgs(intCounter2), 2, intEqualPos - 2)
                    strReportParamName(intRepParamCounter) = strTempName
                    strReportParamValue(intRepParamCounter) = strTempValue
                    intRepParamCounter = intRepParamCounter + 1
                Else
                    strTempName = Left(strRepCmdLnArgs(intCounter2), intEqualPos - 1)
                    strParamName(intParamCounter) = strTempName
                    strParamValue(intParamCounter) = strTempValue
                    intParamCounter = intParamCounter + 1
                End If
            Next intCounter2

        Next intCounter
	...etc...

        Call PrintReport(strParamReportName, strParamDSN, strParamExportType, strParamExportFileName)
        If blnSendEmail = True Then
            Call SendEmail(strRecipient, strSender, strSubject, strMessage & vbCr & "THIS IS AN AUTOMATED MESSAGE, DO NOT REPLY ", strSMTP)
        End If

	Exit Sub
    End Sub

    Public Sub PrintReport(ByVal pi_strReportName As String, ByVal pi_strDSN As String, ByVal pi_strExportType As String, ByVal pi_strExportFileName As String)

        Dim objCrApp As CRAXDRT.Application = New CRAXDRT.Application
        Dim objCrRpt As CRAXDRT.Report = objCrApp.OpenReport(pi_strReportName, crOpenReportByDefault)
        Dim objParams As CRAXDRT.ParameterFieldDefinitions = objCrRpt.ParameterFields
        Dim objParam As CRAXDRT.ParameterFieldDefinition
        Dim objDb As CRAXDRT.Database = objCrRpt.Database
        Dim objTables As CRAXDRT.DatabaseTables = objDb.Tables
        Dim objTable As CRAXDRT.DatabaseTable = objTables.Item(1)
        Dim objCrExport As CRAXDRT.ExportOptions = objCrRpt.ExportOptions
        Dim objConnDummy As ADODB.Connection = New ADODB.Connection
        Dim intParamCount As Integer = 0
        Dim intCounter As Integer = 0
        Dim strParamName2 As String = ""
        Dim strRepParamName As String = ""
        Dim strParamValue2 As String = ""
        Dim strPassword As String = ""
 	...etc...

        ' Set connection details for crystal report
        With objTable
            .ConnectionProperties("FILEDSN").Value = pi_strDSN
            .ConnectionProperties("Password").Value = strPassword
            .ConnectionProperties("Database").Value = objConnDummy.DefaultDatabase
        End With

        'Set strParamName2 & strParamValue2 for each ReportParam
        'and set the relevant parameter by calling: objParam.AddCurrentValue(strParamValue2) in the Crystal Report itself

        ' Loop through parameters in Crystal Report itself
        For intParamCount = 1 To objParams.Count
            objParam = objParams.Item(intParamCount)
            strParamName2 = objParam.ParameterFieldName

            ' Loop through repParams passed in via command line
            For intCounter = 0 To intRepParamCounter - 1
                strRepParamName = Mid(strReportParamName(intCounter), 2)

                ' If Crystal Report parameter = repParams from command line, set it 
                If UCase(strParamName2) = UCase(strRepParamName) Then
                    'check data type
                    Select Case objParam.ValueType
                        Case crStringField
                            strParamValue2 = strReportParamValue(intCounter)
                            objParam.ClearCurrentValueAndRange()
                            objParam.AddCurrentValue(strParamValue2)
                        Case crDateTimeField
                            If UCase(strParamName2) = "@STARTD" Then
                                strHmsTime = " 00:00:00"
                            ElseIf UCase(strParamName2) = "@ENDD" Then
                                strHmsTime = " 23:59:59"
                            End If
                            '0 = today, -1 = yesterday, etc...
                            If IsNumeric(strReportParamValue(intCounter)) = True Then
                                dtDateTime = DateTime.Parse(Today & strHmsTime)
                                If strReportParamValue(intCounter) <> "0" Then
                                    dtDateTime = DateAdd("d", CDbl(strReportParamValue(intCounter)), dtDateTime)
                                End If
                                strDateTime = Format(dtDateTime, scDateFormat2)
                            Else
                                dtDateTime = DateTime.Parse(strReportParamValue(intCounter) & strHmsTime)
                                strDateTime = Format(dtDateTime, scDateFormat2)
                            End If
                            strParamValue2 = strDateTime
                            objParam.AddCurrentValue(strDateTime)
                        Case crBooleanField
                            If strReportParamValue(intCounter) = 1 Then
                                strParamValue2 = "true"
                                objParam.AddCurrentValue(True)
                            Else
                                strParamValue2 = "false"
                                objParam.AddCurrentValue(False)
                            End If
                        Case Else
                            strParamValue2 = CDbl(strReportParamValue(intCounter))
                            objParam.AddCurrentValue(strParamValue2)
                    End Select

                    ' Prepare Report Title for top of Crystal Report (that shows the parameters that were selected)
                    objCrRpt.ReportTitle = objCrRpt.ReportTitle & strParamName2 & " = " & strParamValue2 & vbCr

                End If

            Next intCounter

        Next intParamCount

        ' set export criteria: DestinationType, DiskFileName, FormatType
        With objCrExport
            .DestinationType = crEDTDiskFile
            .DiskFileName = pi_strExportFileName
            strExportFilename = .DiskFileName
            Select Case UCase(pi_strExportType)
                Case "PDF"
                    .FormatType = crEFTPortableDocFormat
                Case "EXCEL"
                    .FormatType = crEFTExcel97
                Case "DOC"
                    .FormatType = crEFTWordForWindows
                Case "RPT"
                    .FormatType = crEFTCrystalReport
                Case "XLSDATA"
                    .FormatType = crEFTExcel80Tabular
                    .ExcelUseTabularFormat = True
                    .ExcelUseConstantColumnWidth = True
                    .ExcelConstantColumnWidth = 150
                Case Else
                    .FormatType = crEFTPortableDocFormat
            End Select
        End With

        ' delete file if exists
        objFile = New Scripting.FileSystemObject
        If objFile.FileExists(pi_strExportFileName) = True Then
            objFile.DeleteFile(pi_strExportFileName)
        End If

        'Export the report 
        objCrRpt.Export(False)    'Supposed to NOT prompt user but it DOES
        'objCrRpt.Export()        'Causes many user prompts
        'objCrRpt.Close()         'Not defined
        objCrRpt = Nothing

        Exit Sub
    End Sub
 
Last edited:

shell_l_d

New member
Joined
Sep 21, 2010
Messages
4
Programming Experience
Beginner
Found & fixed problem... AddCurrentValue was expecting a date object but I was passing it a string, so the parameters weren't being set.

EXTRACTS of the PrintReport Sub in the VB.net code:
VB.NET:
                        Case crDateTimeField
                            If UCase(strParamName2) = "@STARTD" Then
                                strHmsTime = " 00:00:00"
                            ElseIf UCase(strParamName2) = "@ENDD" Then
                                strHmsTime = " 23:59:59"
                            End If
                            '0 = today, -1 = yesterday, etc...
                            If IsNumeric(strReportParamValue(intCounter)) = True Then
                                dtDateTime = DateTime.Parse(Today & strHmsTime)
                                If strReportParamValue(intCounter) <> "0" Then
                                    dtDateTime = DateAdd("d", CDbl(strReportParamValue(intCounter)), dtDateTime)
                                End If
                                strDateTime = Format(dtDateTime, scDateFormat2)
                            Else
                                dtDateTime = DateTime.Parse(strReportParamValue(intCounter) & strHmsTime)
                                strDateTime = Format(dtDateTime, scDateFormat2)
                            End If
                            strParamValue2 = strDateTime
                            'objParam.AddCurrentValue(strDateTime)    ' * expects DATE not STRING
                            objParam.AddCurrentValue(dtDateTime)     ' * this works

                            ' DEBUGGING
                            sMyTest = objParam.GetNthCurrentValue(1).ToString   '  "9/21/2010 12:00:00 AM" & "9/22/2010 11:59:59 PM" & "%"
                            sMyTest = objParam.IsCurrentValueSet.ToString     '  "True"
                            sMyTest = objParam.NeedsCurrentValue.ToString   '  "False"
 
Top Bottom