Blank subreport with valid dataset datasource in VB.Net

[Carbo18]

I have created a report which contains a sub report. I have based both the report and subreport on seperate datasets. Once I run the following code I get the correct records in the recordsets, but when the report is printed I get no data in the subreport (only the labels). I have also linked the report and subreport correctly.

Here is the code:

Friend Sub Print()
If mPatient.IsDirty Then
If MsgBox("Would you like to save the changes made to patient " & vbCrLf & _
mPatient.LastName & ", " & mPatient.FirstName & "?" & vbCrLf & _
"Otherwise they will not be reflected in the report.", _
MsgBoxStyle.YesNo, "Save Patient?&quot = MsgBoxResult.Yes Then
Try
Cursor.Current = Cursors.WaitCursor
mPatient.Save()
CType(Me.MdiParent, Main).RefreshLists()
Cursor.Current = Cursors.Default
ToggleTestResultButtons()
Catch ex As Exception
Cursor.Current = Cursors.Default
MsgBox(ex.ToString)
End Try
End If
End If

''Create an instance of the strongly-typed report object
Dim pr As New PatientReport
''Pass the populated dataset to the report
pr.OpenSubreport("PatientSub.rpt&quot.SetDataSource(BuildSubReportDataSet())
pr.SetDataSource(BuildDataSet())

''Print the report
pr.PrintToPrinter(1, False, 0, 0)
End Sub

Private Function BuildDataSet() As DataSet
Dim cn As OleDbConnection
''Create a instance of a Dataset
Dim ds As New DataSet
Dim strSQL As String
strSQL = "SELECT Patient.ID, PatientNo, FirstName, LastName, Sex, DateOfBirth, " & _
"Address1, Address2, City, StateProv, PostalCode, " & _
"Country, Phone, WorkPhone, Fax, OtherPhone, Email, " & _
"zzAntipsychoticMedication.AntipsychoticMedication, Dose, " & _
"PrescriptionDate, Notes FROM zzAntipsychoticMedication " & _
"RIGHT JOIN (StateProv RIGHT JOIN (Country RIGHT JOIN " & _
"Patient ON " & _
"Country.ID = Patient.CountryID) ON " & _
"StateProv.ID = Patient.StateProvID) ON " & _
"zzAntipsychoticMedication.ID = Patient.AntipsychoticMedication " & _
"WHERE Patient.ID='{" & mPatient.ID.ToString & "}'"
Try
''Create and open a connection using the connection string
cn = New OleDbConnection(CNN_STRING)
Dim da As New OleDbDataAdapter(strSQL, cn)
''Fill the dataset with the data retrieved. The name of the table
''in the dataset must be the same as the table name in the report.
da.Fill(ds, "Patient&quot

Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
Return ds
End Function

Private Function BuildSubReportDataSet() As DataSet
Dim cn As OleDbConnection
''Create a instance of a Dataset
Dim ds As New DataSet
Dim strSQL As String
strSQL = "SELECT TestResultID, PatientID, TestResultDate, Weight, Height, " & _
"HDL, LDL, Triglycerides, SystolicBP, InsulinResistance, " & _
"DiabetesMellitus, FamilyHistory, Smoking, AttendingPhysician, BMI, " & _
"Risk, Baseline, RelativeRisk, Age FROM TestResults " & _
"WHERE PatientID='{" & mPatient.ID.ToString & "}'"
Try
''Create and open a connection using the connection string
cn = New OleDbConnection(CNN_STRING)
Dim da As New OleDbDataAdapter(strSQL, cn)
''Fill the dataset with the data retrieved. The name of the table
''in the dataset must be the same as the table name in the report.
da.Fill(ds, "TestResults&quot

Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
Return ds
End Function

Please help if you can... It is urgent although I can only give 125 points... If you find the queries a little odd, its because they use GUIDs, there are no problems with them they were checked.

Thanks

 

[FVTrainer]

I haven't played with .Net yet, so I'm just going to offer a guess here. In the vb 6 world, when something like this happens in an ADO-based report, it's often because the dataset has gone out of scope by the time the report prints. The way to deal with that in vb 6 is to have a line of code like:

pr.ReadRecords

before printing and before the dataset goes out of scope (which happens if you inadvertantly close the recordset before running the report.

In the case of a subreport, you would use the same method with the subreport object (here, assuming a subreport called prSubreport):

prSubreport.ReadRecords

Since the object model should be the same (or very similar) in .Net, I would suspect you should have the ability to do this same step. It doesn't appear to me that your dataset is obviously going out of scope, but, like I said, I haven't immersed myself in the .Net world yet.