If cbClient.Text <> "<ALL>" Then
strSql = "SELECT name, value as average FROM (" & vbCrLf
strSql = strSql & "Select '" & cbClient.Text & "' as [Name], AVG([" & cbClient.Text & "]" & ".Value) as [Value] from "
strSql = strSql & "[" & cbClient.Text & "]" & " where [" & cbClient.Text & "].Date Between #" & mindate & "# And #" & maxdate & "# " & vbCrLf
strSql = strSql & ");"
Else
strSql = "SELECT name, value as average FROM (" & vbCrLf
Dim FILENAME As String
For i = 0 To schemaTable.Rows.Count - 1
FILENAME = schemaTable.Rows(i).Item("TABLE_NAME")
If (Not FILENAME.StartsWith("~TMP")) And Not (FILENAME.StartsWith("Msys")) And Not (FILENAME.StartsWith("fx_")) And Not (FILENAME.StartsWith("pr_")) And Not (FILENAME.StartsWith("cd_")) Then
If IsFirstSubquery Then
IsFirstSubquery = False
Else
strSql = strSql & "union " & vbCrLf
End If
strSql = strSql & "Select '" & FILENAME & "' as [Name], AVG([" & FILENAME & "]" & ".Value) as [Value] from "
strSql = strSql & "[" & FILENAME & "]" & " where [" & FILENAME & "].Date Between #" & mindate & "# And #" & maxdate & "# " & vbCrLf
End If
Next i
strSql = strSql & ");"
End If
cmd = New OleDbCommand(strSql, con)
dr = cmd.ExecuteReader
'The control datagridview does not have the items property, but it has the datasource property
'then we can use it to use that we need to assign the results of the query to an
'arraylist, for instance, then assing this arraylist to the datasource property of
'the datagrid
Dim QueryResult As New ArrayList
While dr.Read
Dim dummy As New Average
dummy.Name = dr("name")
If Not IsDBNull(dr("average")) Then
dummy.Average = dr("average")
Else
dummy.Average = "0"
End If
QueryResult.Add(dummy)
End While
dgvAverage.DataSource = QueryResult
'set the "average" column display order as the second column
Me.dgvAverage.Columns("average").DisplayIndex = 1
'average being the index of the column
Me.dgvAverage.Columns("average").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
'set the "name" column display order as the first column
Me.dgvAverage.Columns("name").DisplayIndex = 0
'name being the index of the column
Me.dgvAverage.Columns("name").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
dr.Close()
cmd.Dispose().
Private Class Average
Private _name As String
Private _average As String
Public Property Name() As String
Get
Return _name
End Get
Set(ByVal value As String)
_name = value
End Set
End Property
Public Property Average() As String
Get
Return _average
End Get
Set(ByVal value As String)
_average = Math.Round(Double.Parse(value)).ToString
If _average.Length < 16 Then
Dim strTemp As String = String.Empty
Dim chr As Char() = _average.ToCharArray
For i As Integer = chr.Length - 1 To 0 Step -1
If (chr.Length - i) Mod 3 = 0 And i <> 0 Then
strTemp = "," & chr(i) & strTemp
Else
strTemp = chr(i) & strTemp
End If
Next
_average = strTemp
End If
End Set
End Property
End Class