I've created a process by which users can dynamically add data input fields to data input screens. Works just fine, I am able to capture data, store it in the database, and retrieve the data. No problem. Now I need to report out the data in a report. We use ActiveReports v11, I found an example that creates a report layout dynamically here. Of course I had to modify the code to fit my circumstances. When I run the report I always get 4 label and textboxes pairs for the second record in the employee dataset, no idea why and I (apparently) can't see anything wrong with my code. I've confirmed that the data table has 1 row with 3 columns for each iteration of the report, yet no matter what I do I get a duplicate of the 1st label/textbox pair in the report output. Perhaps another pair of eyes can see what I cannot. The report code:
Imports GrapeCity.ActiveReports.SectionReportModel
Imports System.Drawing
Public Class AdditionalData
Public Property As String = ""
Private m_arrayFields As ArrayList
Private MylabelFont As Font = New Font("Arial", 12)
Private MyTextFont As Font = New Font("Arial", 12)
'Create an array to hold the fields defined by the user
Public WriteOnly Property LabelList() As ArrayList
Set(ByVal Value As ArrayList)
m_arrayFields = Value
End Set
End Property
Private Top As Single = 0.9F 'Place the first label/textbox pair under the report title
Private Left As Single = 0.967F 'Left margin
Private m_defaultHeight As Single = 0.25F
Private m_defaultWidth As Single = 4.0F
'Set up report formatting and add label/textbox pairs based on user definition
Private Sub constructReport()
Try
Me.Detail.CanGrow = True
Me.Detail.CanShrink = True
Me.Detail.KeepTogether = True
Dim i As Integer
For i = 0 To m_arrayFields.Count - 1
'For all fields defined by the user create a label and a textbox
Dim lbl As New Label
lbl.ForeColor = Color.Blue
lbl.Font = MylabelFont
'Set the label to display the name of the defined field
lbl.Text = m_arrayFields(i) + ":"
'Set the location of each label
lbl.Top = Top
lbl.Left = Left
lbl.Width = 0.9F
lbl.Height = m_defaultHeight
Me.Detail.Controls.Add(lbl)
Dim txt As New GrapeCity.ActiveReports.SectionReportModel.TextBox
'Set the textbox to display data
txt.DataField = m_arrayFields(i)
txt.ForeColor = Color.Black
txt.Font = MyTextFont
'Set the location of the textbox
txt.Top = Top
txt.Left = Left + 1.0F
txt.Width = m_defaultWidth
txt.Height = m_defaultHeight
Me.Detail.Controls.Add(txt)
'Increment the vertical location by adding the height of the added controls
Top = Top + m_defaultHeight
Next
Label_name.Text = EmployeeName
Catch ex As Exception
LogError("Code File:AdditionalData.vb/constructReport - ERROR: An error occurred formatting the reports layout, the error is: " & ex.Message & Chr(10) & "Trace:" & Chr(10) & GetExceptionInfo(ex))
End Try
End Sub
Private Sub AdditionalData_ReportStart(sender As Object, e As EventArgs) Handles Me.ReportStart
constructReport()
End Sub
End Class
The report layout and data table is created by this code:
Dim Additionalreport As New AdditionalData
If Department.HasAdditionalData = 1 Then
AdditionalData = GetAdditionalDataFields(MyConnection)
AdditionalDataTable = New DataTable
If AdditionalData.Tables(0).Rows.Count > 0 Then
' The department defined data they want to collect and report out
Dim Labels As New ArrayList
For Each MyDataRow3 As DataRow In AdditionalData.Tables(0).Rows
Labels.Add(MyDataRow3("Title"))
AdditionalDataTable.Columns.Add(MyDataRow3("Title"))
Next
Additionalreport.LabelList = Labels
End If
The report is executed by the following code:
For Each MyDataRow2 In MyDataSet.Tables(0).Rows
If Department.HasAdditionalData = 1 Then 'If additional data is defined, run report
AdditionalDataTable.Clear()
AdditionalDataTable.AcceptChanges()
Dim myRow As DataRow
Dim DataFieldColumnName As String = ""
AdditionalDataValues = GetAdditionalDataFieldData(MyConnection, MyDataRow2("EmployeeID"))
'lets populate the table for the reports data source
' First add a row to the datatable
myRow = AdditionalDataTable.NewRow
For Each MyDataRow5 As DataRow In AdditionalDataValues.Tables(0).Rows
DataFieldColumnName = GetDataFieldTitle(MyConnection, MyDataRow5("DataFieldkey"))
myRow(DataFieldName) = MyDataRow5("DataFieldValue")
Next
AdditionalDataTable.Rows.Add(myRow)
Additionalreport.EmployeeName = MyDataRow2("FirstName") & " " & MyDataRow2("LastName")
Additionalreport.DataSource = AdditionalDataTable
Additionalreport.Run()
rpt.Document.Pages.AddRange(Additionalreport.Document.Pages)
End If
Next
The employee data is contained in a dataset passed into the CompileAdditionalDataReport sub. The report is run for each record in the data set table. Debug code shows that each record created in AdditionalDataTable has 3 columns and 1 row of data, yet when the report is run, the second run always produces 4 label/textbox pairs with the first label/textbox pair duplicated on the last line in the report. The data displayed is always correct, just can't understand why the second run has 4 label/textbox pairs displayed as only 3 are defined. Can anyone see what's causing this to happen, I obviously can't.
Apparently the second time the report was run, an artifact from the first run existed on the second run, still don't where the artifact came from and how it remained, but it's there. I changed my code to generate a new copy of the report layout each time it's run and the phantom output disappeared. I created a function that creates the layout and returns the data table via a reference variable and a new copy of the report, my code:
Public Function GenerateAdditionalDataReport(ByRef pAdditionalDatatable As DataTable) As AdditionalData
Dim AdditionalData As New DataSet
Dim AdditionalDataValues As New DataSet
Dim AdditionalDataTable As New DataTable
Dim Additionalreport As New AdditionalData
Dim MyLeagueInfo As New AssociationInformation
Dim MyConnection As OracleConnection = OpenConnection(Current.Session("USERNAME"), Current.Session("PASSWORD"))
Dim MyRegOptions As New DataSet
Department = Current.Session("DepartmentInfo")
If MyLeagueInfo.HasAdditionalData = 1 Then
AdditionalData = GetAdditionalDataFields(MyConnection)
AdditionalDataTable = New DataTable
If AdditionalData.Tables(0).Rows.Count > 0 Then
' They collect their own data
Dim Labels As New ArrayList
For Each MyDataRow3 As DataRow In AdditionalData.Tables(0).Rows
Labels.Add(MyDataRow3("Title"))
AdditionalDataTable.Columns.Add(MyDataRow3("Title"))
Next
Additionalreport.LabelList = Labels
End If
End If
pAdditionalDatatable = AdditionalDataTable
MyConnection.Close()
MyConnection.Dispose()
MyConnection = Nothing
Return Additionalreport
End Function
Then I run the report for each person in the dataset:
If Department.HasAdditionalData = 1 Then
Additionalreport = GenerateAdditionalDataReport(AdditionalDataTable)
Dim myRow As DataRow
Dim DataFieldName As String = ""
AdditionalDataValues = GetAdditionalDataFieldData(MyConnection, MyDataRow2("EmployeeID"))
' lets populate the table for the reports datasource
' First create a new row to the datatable
myRow = AdditionalDataTable.NewRow
For Each MyDataRow5 As DataRow In AdditionalDataValues.Tables(0).Rows
DataFieldName = GetDataFieldTitle(MyConnection, MyDataRow5("DataFieldkey"))
myRow(DataFieldName) = MyDataRow5("DataFieldValue")
Next
AdditionalDataTable.Rows.Add(myRow)
Additionalreport.EmployeeName = MyDataRow2("FirstName") & " " & MyDataRow2("LastName")
Additionalreport.DataSource = AdditionalDataTable
Additionalreport.Run()
rpt.Document.Pages.AddRange(Additionalreport.Document.Pages)
End If
Next