Error in setting datasource - Crystal reports

[Former Member]

Good day,

I am currently experiencing a problem with setting my datasource to a report in Crystal Reports.

Strangely, the report works in VS 2010, but when running as an executable, the program fails when assigning the datasource.

My code:

    Public Sub PurchasesByVendor()

        Try

            Dim sql As String = "SELECT tblVendors.VendorID, tblVendors.VendorName, tblPO.POID, tblPO.EnterDate, tblCustomers.Company, tblOrders.RefNum, Sum(tblPOItems.ExtPrice) AS SumOfExtPrice " & _

                                "FROM (((tblPO INNER JOIN tblPOItems ON tblPO.POID = tblPOItems.POID) INNER JOIN tblVendors ON tblPO.VendorID = tblVendors.VendorID) LEFT JOIN tblOrders ON tblPO.OrderID = tblOrders.OrderID) LEFT JOIN tblCustomers ON tblOrders.CustomerID = tblCustomers.CustomerID " & _

                                "WHERE (((tblPO.EnterDate) Between #" & dpStart.Text & "# And #" & dpFinish.Text & "#)) " & _

                                "GROUP BY tblVendors.VendorID, tblVendors.VendorName, tblPO.POID, tblPO.EnterDate, tblCustomers.Company, tblOrders.RefNum; "

            Dim con As New OleDb.OleDbConnection

            Dim da As OleDb.OleDbDataAdapter

            Dim ds As New DataSet

            con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Program Files\Mail-Shop\MSApp2k.mdb"

            con.Open()

            da = New OleDb.OleDbDataAdapter(sql, con)

            da.Fill(ds, "PO")

            Dim povList As List(Of POV) = New List(Of POV)

            If ds.Tables("PO").Rows.Count > 0 Then

                For i = 0 To ds.Tables("PO").Rows.Count - 1

                    Dim pov As POV = New POV()

                    pov.VendorName = ds.Tables("PO").Rows(i).Item(1)

                    pov.PONumber = ds.Tables("PO").Rows(i).Item(2)

                    pov.PODate = ds.Tables("PO").Rows(i).Item(3)

                    If IsDBNull(ds.Tables("PO").Rows(i).Item(4)) = False Then

                        pov.Company = ds.Tables("PO").Rows(i).Item(4)

                    End If

                    If IsDBNull(ds.Tables("PO").Rows(i).Item(5)) = False Then

                        pov.Order = ds.Tables("PO").Rows(i).Item(5)

                    End If

                    pov.Total = ds.Tables("PO").Rows(i).Item(6)

                    povList.Add(pov)

                Next

            End If

            'MsgBox(povList.Count)

            Dim datas As POVD = New POVD()

            Dim dt As New DataTable("povd")

            'Dim dt As DataTable = datas.Tables.Add("hc")

            dt.Columns.Add("Vendor", GetType(String))

            dt.Columns.Add("PONumber", GetType(Integer))

            dt.Columns.Add("PODate", GetType(Date))

            dt.Columns.Add("Company", GetType(String))

            dt.Columns.Add("OrderNbr", GetType(Integer))

            dt.Columns.Add("Total", GetType(Double))

            dt.Columns.Add("Charged", GetType(Double))

            datas.Tables.Add(dt)

            For Each dr In povList

                Dim vendor = dr.VendorName

                Dim ponbr = dr.PONumber

                Dim poDate As Date = DateValue(DateTime.Parse(dr.PODate))

                Dim company = dr.Company

                Dim order = dr.Order

                Dim total = dr.Total

                Dim charged = dr.getCharged(dr.PONumber)

                Dim nw As DataRow = datas.Tables(0).NewRow()

                nw(0) = vendor

                nw(1) = ponbr

                nw(2) = poDate

                nw(3) = company

                nw(4) = order

                nw(5) = total

                nw(6) = charged

                datas.Tables(0).Rows.Add(nw)

            Next

            '' bind the datasource+

            Dim objRpt As New PurchaseByVendor

            objRpt.SetDataSource(datas.Tables(0))

            Dim wfp As New WindowFormReport()

            wfp.CrystalReportsViewer1.ViewerCore.ReportSource = objRpt

            Me.NavigationService.Navigate(wfp)

            con.Close()

            con.Dispose()

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

    End Sub

Thanks!

[former_member188030 · Accepted solution]

What version fo CR you are using, what service pack?

What CR components are installed on the client machine for deployment?

When using datasets to pass the data to Crystal reports, the issue occurs due to datasets in 50% of cases.

What exact error do you get?

Could you try below steps which would tell you if its an issue with Crystal or with getting data into datasets.

- Once the data is populated in the dataset and before doing a "objRpt.SetDataSource(datas.Tables(0))", write the data to a datagrid.

- While deploying the EXE, you could observe the datagrid to check fi the datasets do return some data.

This may not be a Crystal issue.

if you get the data in the datagrid and not in the report then do a 'ds.writeXml("XMLfilepath.xml")'. this will generate the XML file with the data from the dataset. the open the report indeaigner and try to assign the XMl to the report and see if the report works in thed esigner.

- Bhushan

Senior Engineer

SAP Active Global Support

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Getting started and moving ahead with Crystal Reports .NET applications.

[Former Member · Accepted solution]

I am using Service Pack 10 right now.

I have tried the method you have suggested with the XML already. My report works great in Visual Studio, but once deployed to an EXE, both methods fail. So I know that it is producing data.

I can't even run the EXE on the developer machine outside of Visual Studio or the bin folder. The EXE works INSIDE the bin folder.

In fact, I do not get any error message despite using a Try-Catch.....

[former_member188030 · Accepted solution]

could you post the error mesage with complete stack trace?

When you say 'both methods fail' does it mean you dont get the data in a datagrid?

Thanks,

Bhushan