Push Method Failed to retrieve data from database

tomnorra
tomnorra used Ask the Experts™
on
Ok this one has puzzled me far too long now. I have a crystal report created using the push method. When I create the dataset and pass it to the report everything seems to work until the point the viewer tries to show the results. At this point I get the attached Error(Failed to retrieve data from database).
 Error MessageThe puzzling thing is this is a push method, the report should not be trying to retrieve anything from the database and the dataset is returned without issue from the database. I have recreated this report multiple times and every time the same result. I have modified the SP multiple times thinking it might be something to do with SQL Server 2005, same results. I created the report as a pull method as well, this actually worked correctly but I don't want any reports pulling data as company policy is to push a result to the report. All other reports(more than 200) using push method work fine because in pull method you have to set connection info, but as a push report this one just wants to be a pain.
Here is some code that defines the basics of what is being done:
 
sStartDate = Convert.ToDateTime(“2010-06-01 14:00:01”).ToString("yyyy-MM-dd HH:mm:ss")
sEndDate = Convert.ToDateTime(“2010-06-02 08:00:00”).ToString("yyyy-MM-dd HH:mm:ss")
objRptDS = m_objReports.GetDataSet("Execute spVaultLogImpProcRpt '" & sStartDate & "', '" & sEndDate & "'")
If (objRptDS Is Nothing) OrElse objRptDS.Tables.Count = 0 OrElse objRptDS.Tables(0).Rows.Count = 0 _
OrElse Not m_objReports.ViewCrystalReportDS(sTemplatePath & "rptVLIP.rpt", Nothing, "@dStartDateTime~" & sStartDate & "|@dEndDateTime~" & sEndDate, , frmRpt, , objRptDS) Then
    MessageBox.Show(sDefaultMsg, sDefaultTitle, MessageBoxButtons.OK, MessageBoxIcon.Information)
End If

Public Function ViewCrystalReportDS(ByVal sReportName As String, _
                    ByRef crvReports As CrystalDecisions.Windows.Forms.CrystalReportViewer, _
                    Optional ByVal sParams As String = "", Optional ByVal sSelectionFormula As String = "", _
                    Optional ByRef frmRpt As System.Windows.Forms.Form = Nothing, _
                    Optional ByVal bPrint As Boolean = False, Optional ByVal dsRpt As DataSet = Nothing) As Boolean
        Dim intCounter As Int32 = 0
        Dim intCounter1 As Int32 = 0
        Dim objReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument
		Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
        Dim crParameterValues As CrystalDecisions.Shared.ParameterValues
		Dim strParVal() As String
        Dim strVal() As String
        Dim iLoop As Int32 = 0
        Try
            objReport.Load(sReportName) 'Load The Report
            'Pass the dataset to the report
			If Not (dsRpt Is Nothing) Then objReport.SetDataSource(dsRpt.Tables(0)) Else Return False
            'Check If There Are Parameters In Report.
            intCounter = objReport.DataDefinition.ParameterFields.Count
            'Since ParameterFields Collection Picks Up The Selection Formula In The Count Even Though
            'It Is Not A Parameter We Have To Check If It Is A Formula When Count Is 1
            If intCounter = 1 Then
                If InStr(objReport.DataDefinition.ParameterFields(0).ParameterFieldName, ".", CompareMethod.Text) > 0 Then intCounter = 0
            End If
            'If There Are Parameters And String Is Not Empty
            If intCounter > 0 And sParams.Trim.Length > 0 Then
                strParVal = sParams.Split(Convert.ToChar("|")) 'Split The Parameter String
                For iLoop = 0 To UBound(strParVal)
                    If InStr(strParVal(iLoop), "~") > 0 Then
                        strVal = strParVal(iLoop).Split(Convert.ToChar("~")) 'Split The Values
                        'Apply Values To Parameters.
                        If strVal(1).ToUpper <> "NULL" Then
                            crParameterDiscreteValue.Value = strVal(1)
                        Else
                            crParameterDiscreteValue.Value = vbNullString
                        End If
                        crParameterValues = objReport.DataDefinition.ParameterFields(strVal(0)).CurrentValues
                        crParameterValues.Add(crParameterDiscreteValue)
                        objReport.DataDefinition.ParameterFields(strVal(0)).ApplyCurrentValues(crParameterValues)
                    End If
                Next iLoop
            End If
			'If Selection Formula Passed Then Use Formula
            If sSelectionFormula.Length > 0 Then objReport.RecordSelectionFormula = sSelectionFormula
            'Set Report Object To Show Report.
            If (crvReports Is Nothing) Then
                Dim frmViewer As New Windows.Forms.Form
                crvReports = New CrystalDecisions.Windows.Forms.CrystalReportViewer
                crvReports.ReportSource = Nothing
                crvReports.ReportSource = objReport
                crvReports.Left = 0
                crvReports.Top = 0
                crvReports.Name = "crvReports"
                crvReports.Width = 872
                crvReports.Height = 368
                crvReports.TabIndex = 0
                'crvReports.DisplayStatusBar = True
                crvReports.DisplayGroupTree = False
                crvReports.Show()
                crvReports.Refresh()
                frmViewer.Text = "Report Viewer"
                frmViewer.Width = 896
                frmViewer.Height = 416
                frmViewer.WindowState = FormWindowState.Maximized
                frmViewer.Controls.Add(crvReports)
                crvReports.Anchor = CType((((AnchorStyles.Top Or AnchorStyles.Bottom) _
                    Or AnchorStyles.Left) Or AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
                'frmViewer.Show()
                frmRpt = frmViewer
            Else
                crvReports.ReportSource = Nothing
                crvReports.ReportSource = objReport
                crvReports.Show()
            End If
			crvReports.ShowLogo = False
			crvReports.ReportSource = Nothing
            crvReports.ReportSource = objReport
            If bPrint Then
                frmRpt = Nothing
                objReport.PrintToPrinter(1, False, 0, 0)
            Else
                crvReports.Show()
            End If
            Application.DoEvents()
            Return True
        Catch ex As System.Exception
			MessageBox.Show("Error Creating Report " & ex.Message, "Error In View Crystal Report DS", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            Erase strParVal
            Erase strVal
        End Try
    End Function

Open in new window


Why is their failure to retrieve something that has already been retrieved?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Might be XML Schema which you have bind to design the Crystal report is having different schema from the dataset which you are biding from code.

Author

Commented:
no, verify database would adjust the schema to the correct version. This is not the case.
ok then check your crystal report parameters. It might be possible your crystal report parameter datatype getting mismatch with the values you provide from code.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
no, datetime is set for both
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Did you create the report against the database or a recordset?

If it was against the database then that is what Crystal looks for when you open the report regardless of the fact you are passing the data through a dataset.

mlmcc

Author

Commented:
I created the report using both formats. Using ODBC worked fine but that is a pull method and not what I want. Using ADO.NET dataset is what is failing.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:

Author

Commented:
No, I had not seen the article but I don't need to know how to create a report. As I said I have over 200 reports. Creating them is not the problem.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
If you are going to call them with the PUSH method, you have to create them against an XSD file not a database.

mlmcc

Author

Commented:
I did create it with an xml schema and got the same result. Not the issue.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
What database driver did you use to build the report?

mlmcc

Author

Commented:
can you be more specific in your question. I have explained I created and recreated this report several times using different drivers. The one not working is the ADO.NET XML version which is the one that shouldn't need a Database driver to access data as the data is to be passed to it using a dataset.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I use that one with XML files which are not the same as a dataset

On reading the article and checking with Crystal help I think you want to use the
MORE DATA SOURCES
     FIELD DEFINITION

From the Crystal Help
Field Definition
Field Definitions are special drivers used to create reports based on only a schema without actual data. This driver is included mainly for backward compatibility and is only used during the report creation process.

At runtime, a report created with this driver must be passed a recordset or dataset through a Crystal Data Object. For details on developing reports and applications with Crystal Data Objects, see the Crystal Reports Developer's Help (CrystalDevHelp.chm).

mlmcc

Author

Commented:
No I don't want field definition data. This is not a special situation. All other reports using push method were created the same way and are working without using field definition.

Author

Commented:
I have an update in that I have found that if I only put the varchar and date fields on the report the report will display but if I add the money fields I get the error mentioned above. It doesn't matter what money field and it doesn't matter what I do to format that field in the SP, I get the error if it is varchar, money, float, numeric etc. You name it I tried it and it gives the error. So this is not a login issue it is a field issue in what the database returns vs. what the field on the report is expecting. This is getting stranger by the minute.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Can you convert the $ fields to numbers or text in the SP and then display/use them in Crystal?

mlmcc
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
SOrry, didn't fully read the one sentence.  That is strange that you can't convert it in the SP.

So, if you don't select those fields in the SP it works or if they are there and you don't use them in the report does it work?

mlmcc

Author

Commented:
If I don't use them in the report, and more specifically even if I dummy the fields in the SP to something like 0 as Residual(1) or '' as Residual(1) and so forth the crystal report still fails. One thing I am going to check next is the field name may be the issue. It may be that Crystal doesn't like the parentheses being returned by the SP in the field name!
Commented:
Figured this one out on my own. It is that on a push method Crystal has some sort of trouble deciphering/handling the parentheses in the field names but on the pull method there is no problem. I recreated the stored procedure to return records with field names that did not include parentheses in the name and the report works fine as a push. Alleluia!!!

Hope this post will help some other poor soul some time and they won't have the problem I did.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial