declare @minten int;
declare @maxten int;
declare @query varchar(8000);
declare @dims varchar(8000);
declare @ifs varchar(8000);
declare @TenList varchar(8000);
set @minten = 1;
set @maxten = 11;
with tmp(t) as (
select @minten
union all
select t+1
from tmp
where t< @maxten)
Select * into #tmp from tmp
Select @dims = coalesce(@dims + 'dim Ten' + cast(t as varchar(3)) + ' as String
', 'dim Ten' + cast(t as varchar(3)) + ' as String
') from tmp#tmp
order by t;
with tmp(t) as (
select @minten
union all
select t+1
from tmp
where t< @maxten)
Select @ifs = coalesce(@ifs + 'If Ten = ' + cast(t as varchar) + ' Then Ten' + cast(t as varchar(3)) + ' = row("FullName")
','If Ten = ' + cast(t as varchar) + ' Then Ten' + cast(t as varchar(3)) + ' = row("FullName")
') from tmp#tmp
order by t;t
Select @TenList = coalesce(@TenList + ' & " " & Ten' + cast(t as varchar(3)),'Ten' + cast(t as varchar(3)))
from #tmp
order by t
set @query = '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
' + @dims + '
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
' + @ifs + '
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 &' "+ "@TenList &+ 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'
Select @query
--Execute (@query)
