Member Avatar for Chair

Hi,

I'm making a printable Purchase Order form using Crystal Report. This report retrieves data from two tables. Example, I have fields PONumber,DateofPO,Airline,DateofFlight,ETA,ETD from a table called tblFlight. I have fields PassengerName and Rate on another table called tblPassenger. Each PONumber can have multiple passengers. How can I get it so that I'll just be generating one PO that shows the multiple passengers while the rest of the items show only once? I've placed the PONumber and DateofPO on the PageHeader and the rest of the items on the Details section. But right now, on the Details section, if I have 3 passengers, the other fields also show 3 times on the report. Also, I'm considering to make a query but I have a conditional statement on my query, a WHERE clause that retrieves data dynamically. My problem is I'm not sure where to put the query, will it be on the .rpt or just on the Form? NOTE: I'm coding my SQL queries on the application itself, not inside the server, so no stored procedures/functions for me here.

Would greatly appreciate any help. Thanks in advance.

  • 1 Contributor
  • 1 Reply
  • 423 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by Chair
Member Avatar for Chair

WELL, I've solved this (again) :=) Just in case, someone has the same problem: The solution is putting tblPassenger on a Crystal Report sub-report, and then link that to a primary field in the main report (in this case, PONumber is my primary field); you can do this linking through the Crystal Report Sub-report wizard. Then place this code on Form_Load of your report:

Dim con as New SqlConnection("your connection string")
Dim com as New SqlCommand
Dim adapt as New SqlDataAdapter
Dim ds as New DataSet
Dim rptdocument as New ReportDocument
Dim viewer as CrystalDecisions.Windows.Forms.CrystalReportViewer

con.Open()
        com.Connection = con
        com.CommandText = "yourquery"
        adapt.SelectCommand = com
        adapt.Fill(ds, "yourtable")
        rptdocument.Load("yourreportpath") 
        rptdocument.SetDataSource(ds)
        viewer.ReportSource = rptdocument
        viewer.RefreshReport()
        con.Close()
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.