Creating a Crystal Report based on a dataset

[Ralph1965]

Hello,

I'm writing a VB application and I want to create a report using Crystal Reports 9. I want to base it on a dataset that I populate programatically. When I view the report, no records show up, and there are records in the underlying table. Here is the code:



Dim con As New OleDbConnection
Dim cmd As New OleDbCommand
Dim da As New OleDbDataAdapter
Dim cb As OleDbCommandBuilder
Dim ds As New NamesNumbersDataSet 'NamesNumbersDataSet was generated when I added the dB
Dim rpt As New CrystalReport1


con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Ralph\Documents\Visual Studio 2010\Projects\Crystal Reports using OLEdB\Crystal Reports using OLEdB\Crystal Reports using OLEdB\NamesNumbers.mdb"
con.Open()
cmd.Connection = con
cmd.CommandText = "select First, Last from tblNames"
da.SelectCommand = cmd
cb = New OleDbCommandBuilder(da)
da.Fill(ds)
rpt.SetDataSource(ds)
CrystalReportViewer1.ReportSource = rpt
CrystalReportViewer1.RefreshReport()
CrystalReportViewer1.Show()
con.Close()

Why are there no records showing up on the report? It is based on NamesNumbersDataSet.xsd


Thanks,

Ralph1965

[jggtz]

Where are you assigning the rpt file to rpt object?

[wes4dbt]

da.Fill(ds)

You use a dataadapter to fill a datatable, like "da.Fill(ds.YourdataTableName)"
Also, if you still show no records, test how many records are in "ds.YourdataTableName"

[Ralph1965]

Hi,

I DIM rpt as new CrystalReport1. Doesn't this assign the report to rpt?

I got the report to display records when I use da.fill(dt), where dt is a datatable. The thing now is I want the report to show data from two tables. dt has the data that I want on the report. I know this as I populated a list box with the data in dt, and it's all there.

When I created the report, I based it on the dataset that has both tables. When I go through the report expert and get to the screen that shows the relationships between tables, the relationship is correct, and all of the fields in both tables show up on the screen that allows you to pick the fields to display in the report. When I run the application, the report shows data from only one table. Can I display data from two tables using a dataset? I really want to base my reports on datasets, and not actual databases, because I want to use SQLServer Compact Edition, and I can find no way to use SQLServerCE databases with Crystal Reports.


Thanks,

Ralph1965

[wes4dbt]

Can I display data from two tables using a dataset?

Sure you can, but you have to "Fill" them both.

Here's one I use,

        Dim rpt As New rptLotListing
        Dim ds As New WaterTablesDataSet
        Dim taG As New WaterTablesDataSetTableAdapters.groupsTableAdapter
        Dim taL As New WaterTablesDataSetTableAdapters.lotsTableAdapter
        Dim taU As New WaterTablesDataSetTableAdapters.usersTableAdapter
        Dim taO As New WaterTablesDataSetTableAdapters.ownersTableAdapter
        If Me.ReportName = "LotsNoDoubles" Then
            taG.FillByNoDoubles(ds.groups)
            taL.FillByNoDoubles(ds.lots)
        ElseIf Me.ReportName = "LotsAll" Then
            taG.FillByAll(ds.groups)
            taL.FillAll(ds.lots)
        End If
        taU.FillByUserIdRange(ds.users, Me.UserStartComboBox.Text, Me.UserEndComboBox.Text)
        taO.FillByActiveOwners(ds.owners)
        rpt.SetDataSource(ds)
        rpt.SummaryInfo.ReportTitle = clsCoInfo.CompName & vbNewLine & "Lots Listing"
        frm.CrystalReportViewer1.ReportSource = rpt
        frm.ShowDialog()
        frm.Dispose()
        rpt.Dispose()
        ds.Dispose()

[Ralph1965]

Hi, wes4dbt,

Thank you for your help, but I am having a hard time applying the idea to my code. Could you help me?

Here's my code:

Dim con As New SqlCeConnection
Dim cmd As New SqlCeCommand
Dim da As New SqlCeDataAdapter
Dim cb As New SqlCeCommandBuilder
Dim ds As New PhoneBookDBDataSet
Dim rpt As New rptNamesNumbers


con.ConnectionString = "Data Source=|DataDirectory|\PhoneBookDB.sdf"
con.Open()
cmd.Connection = con


cmd.CommandText = "SELECT tblNames.First, tblNames.Last, tblNumbers.Phone " + _
"FROM tblNames INNER JOIN tblNumbers ON tblNames.ID = tblNumbers.NameID"
da.SelectCommand = cmd
cb = New SqlCeCommandBuilder(da)
da.Fill(ds, "tblNames")
da.Fill(ds, "tblNumbers")
rpt.SetDataSource(ds)
CrystalReportViewer1.ReportSource = rpt
CrystalReportViewer1.RefreshReport()
CrystalReportViewer1.Show()
con.Close()

On the second da.fill,I get an error telling me to relax the constraints on my dataset, so I removed the primary key, and still got the error. Where / how is WaterTablesDataSetTableAdapters defined or created? I am sorry, but I'm pretty new at this stuff

Thanks,

Ralph1965

[Ralph1965]

Hi, wes4dbt,

I got the report working!!!!!!!!!!!

Ralph1965