Hey I am adjusting Fast Report to create some reports, and I have created a loop that runs through query results stored inside a datatable and assigns each TenantName to a Variable, butvariable. However, the code I wrote seems bulky and would get out of hand if I needed more variables.
Would there be a more sensible way to do this?
Here is my code below.
If cmbReports.Text = "Conditions Of Tenancy" Then
Dim ListReport = New FastReport.Report
ListReport.Load("C:\Users\richard\Documents\BMSSouthSide\BMSPanda\Reports\CONDITIONSOFTENANCY.frx")
ListReport.SetParameterValue("CRMConnectionString", "Data .....
Dim strSql As String = "Select TenantForename + ' ' + TenantSurname as FullName From Tenants Where '" & lblLeaseIDValue.Text & "' = LeaseID and PropertyID =" & lblPropertyIDValue.Text
Dim dtb As New DataTable
Dim ten1 As String
Dim ten2 As String
Dim ten3 As String
Dim ten4 As String
Dim ten5 As String
Dim ten6 As String
Dim ten7 As String
Dim ten8 As String
Dim ten9 As String
Dim ten10 As String
Dim ten11 As String
Dim IntTenantID As Integer
Using cnn As New SqlConnection(My.Settings.BMSSouthSideConnectionString)
cnn.Open()
Using dad As New SqlDataAdapter(strSql, cnn)
dad.Fill(dtb)
End Using
Dim Ten As Integer = dtb.Rows.Count
Do Until Ten = 0
For Each row As DataRow In dtb.Rows
If Ten = 11 Then Ten1 = row("FullName")
If Ten = 10 Then Ten2 = row("FullName")
If Ten = 9 Then Ten3 = row("FullName")
If Ten = 8 Then Ten4 = row("FullName")
If Ten = 7 Then Ten5 = row("FullName")
If Ten = 6 Then Ten6 = row("FullName")
If Ten = 5 Then Ten7 = row("FullName")
If Ten = 4 Then Ten8 = row("FullName")
If Ten = 3 Then Ten9 = row("FullName")
If Ten = 2 Then Ten10 = row("FullName")
If Ten = 1 Then Ten11 = row("FullName")
Ten = Ten - 1T
Next row
Loop
Dim strSql2 As String = "Select Top 1 TenantID From Tenants Where '" & lblLeaseIDValue.Text & "' = LeaseID and PropertyID =" & lblPropertyIDValue.Text
Dim datatableTenantID As New DataTable
Using Tenant As New SqlDataAdapter(strSql2, cnn)
Tenant.Fill(datatableTenantID)
End Using
For Each IntTenant As DataRow In datatableTenantID.Rows
IntTenantID = IntTenant("TenantID")
Next
cnn.Close()
End Using
Dim TenantList As String = Ten11 & " " & Ten10 & " " & Ten9 & " " & Ten8 & " " & Ten7 & " " & Ten6 & " " & Ten5 & " " & Ten4 & " " & Ten3 & " " & Ten2 & " " & Ten1
ListReport.SetParameterValue("PropertyID", Val(lblPropertyIDValue.Text))
ListReport.SetParameterValue("LeaseID", Val(lblLeaseIDValue.Text))
ListReport.SetParameterValue("TenantID", IntTenantID)
ListReport.SetParameterValue("TenantList", TenantList)
ListReport.Show()
End If
