Automatic variable generation from data table

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. 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?

```
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")

• Use array, this will reduce by a lot your number of variables.

• Name your variable properly ("ten" should be "tenant")

• Normaly, variable starts with a lower case in .net

• Do not concatenate your strings for queries, this will increase your chances of injections

• Your datatable is a bit useless in your case, you can write directly to your variables with a datareader

• Your listReport is only needed at the end, I think you could initialize it there instead

• I would separate this into multiple functions. A "GetTenants" that returns the list, a "GetTenantID" and a "ShowReport". This way these function can be out of the UI and reused if needed.

-
Your report takes multiple tenant names but only one ID, this seems strange.

If cmbReports.Text = "Conditions Of Tenancy" Then

    Dim tenants As New List(Of String)
    Dim tenantID As Integer

    Dim propertyId As Integer
    Dim leaseId As Integer

    If Not Int32.TryParse(lblPropertyIDValue.Text, propertyId) Then
        ' Not an integer, this is an error
    End If

    If Not Int32.TryParse(lblLeaseIDValue.Text, leaseId) Then
        ' Not an integer, this is an error
    End If

    tenants = GetTenants(propertyId, leaseId)
    tenantID = GetTenantID(propertyId, leaseId)

    ShowReport(tenants, tenantID, propertyId, leaseId)

End If

Function GetTenants(ByVal propertyId As Integer, ByVal leaseId As Integer) As List(Of String)

    Dim tenants As New List(Of String)

    Using cnn As New SqlConnection(My.Settings.BMSSouthSideConnectionString)
        cnn.Open()

        Dim command As New SqlCommand("Select  TenantForename + ' ' + TenantSurname as FullName From Tenants Where LeaseID = @LeaseID and PropertyID = @PropertyID", cnn)

        command.Parameters.Add("@LeaseID", SqlDbType.Int).Value = leaseId
        command.Parameters.Add("@PropertyID", SqlDbType.Int).Value = propertyId

        Using reader As command.ExecuteReader()
            Do While reader.Read()
                tenants.Add(reader.GetString("FullName"))
            Loop
        End Using

        cnn.Close()
    End Using

    Return tenants
End Function

Function GetTenantID(ByVal propertyId As Integer, ByVal leaseId As Integer) As Integer

    Dim tenantID As Integer

    Using cnn As New SqlConnection(My.Settings.BMSSouthSideConnectionString)
        cnn.Open()

        Dim command As New SqlCommand("Select Top 1 TenantID From Tenants Where LeaseID = @LeaseID and PropertyID = @PropertyID", cnn)

        command.Parameters.Add("@LeaseID", SqlDbType.Int).Value = leaseId
        command.Parameters.Add("@PropertyID", SqlDbType.Int).Value = propertyId

        Using reader As command.ExecuteReader()
            If reader.Read() Then
                tenantID = reader.GetInt32("TenantID")
            End If
        End Using

        cnn.Close()
    End Using

    Return tenantID
End Function

Sub ShowReport(ByVal tenants As List(Of String), ByVal tenantID As Integer, ByVal propertyId As Integer, ByVal leaseId As Integer)

    Dim listReport = New FastReport.Report

    listReport.Load("C:\Users\richard\Documents\BMSSouthSide\BMSPanda\Reports\CONDITIONSOFTENANCY.frx")
    listReport.SetParameterValue("CRMConnectionString", "Data .....

    Dim tenantList As String = String.Join(" ", tenants.ToArray())

    ListReport.SetParameterValue("PropertyID", propertyId)
    ListReport.SetParameterValue("LeaseID", tenantID)
    ListReport.SetParameterValue("TenantID", tenantID)
    ListReport.SetParameterValue("TenantList", tenantList)
    ListReport.Show()

End Sub

StackExchange Code Review Q#93731, answer score: 9