Datagrid - Conditional formatting

Thanks to RonaldBiemans I have a DataGrid bound and formatted to the hard drive information from a series of servers (the names of which I am returning via a shared function / datareader from a SQL table) - What I need to do is to try and throw some conditional formatting into the mix. I need to evaluate the free space number and determine the percentage against the drive size and highlight the cell (free space) accordingly, per the below:

> 25% free = white
<= 25% free = yellow
<= 10% free = orange
<= 5% free = red

Below is the code - Thanks!
'---------------------------------------------------------------------------------
Imports System.Management
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Sql
Imports System.Collections.SortedList
Imports Microsoft.VisualBasic.Collection
Imports System.Drawing
Imports System.Web.Mail

Partial Class sandbox2_aspx

    Dim dt As New DataTable
    Dim dt2 As New DataTable

    Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim dr As SqlDataReader = Data.ServersGET()

        dt.Columns.Add("Server", GetType(System.String))
        dt.Columns.Add("Diskname", GetType(System.String))
        dt.Columns.Add("Disk Size")
        dt.Columns.Add("Space Used")
        dt.Columns.Add("Free Space")

        While dr.Read()
            Dim strComputer As String = dr("SERVER").ToString()
            SystemList(strComputer)
        End While

        With datagrid1
            .BorderColor = System.Drawing.Color.Gray
            .CellPadding = 3
            .AutoGenerateColumns = True
            .HeaderStyle.Font.Name = "verdana"
            .HeaderStyle.Font.Size = FontUnit.Parse("10px")
            .ItemStyle.Font.Name = "verdana"
            .ItemStyle.Font.Size = FontUnit.Parse("10px")
            .HeaderStyle.Font.Bold = True
            .HeaderStyle.BackColor = System.Drawing.Color.FromArgb(37, 51, 82)
            .HeaderStyle.ForeColor = System.Drawing.Color.White
            .DataSource = Diskdata()
            .DataBind()
            .Visible = True
            .ShowFooter = False
        End With

    End Sub

    Private Sub SystemList(ByVal strComputer As String)

        Dim co As ConnectionOptions = New ConnectionOptions
        co.Username = "nsadm13"
        co.Password = "houston"
        Dim ms As System.Management.ManagementScope = New System.Management.ManagementScope("\\" & strComputer & "\root\cimv2", co)
        Dim oq As System.Management.ObjectQuery = New System.Management.ObjectQuery("Select * from Win32_LogicalDisk where DriveType=3")
        Dim Query1 As ManagementObjectSearcher = New ManagementObjectSearcher(ms, oq)
        Dim queryCollection1 As ManagementObjectCollection = Query1.Get()
        Dim mo As ManagementObject

        For Each mo In queryCollection1
            Dim dr As DataRow = dt.NewRow
            Dim SU As Double = CType(mo("Size").ToString, Long) - CType(mo("FreeSpace").ToString, Long)
            dr.Item("Server") = strComputer.ToUpper()
            dr.Item("Diskname") = mo("Name").ToString().ToUpper()
            dr.Item("Disk Size") = Data.ConvertBytes(CType(mo("Size").ToString, Long))
            dr.Item("Space Used") = Data.ConvertBytes(CType(SU, Long))
            dr.Item("Free Space") = Data.ConvertBytes(CType(mo("FreeSpace").ToString, Long))
            dt.Rows.Add(dr)

        Next
    End Sub

    Private Function Diskdata() As DataTable

        Dim dt2 As New DataTable
        dt2.Columns.Add("Server")
        dt2.Columns.Add("DiskInfo")

        For Each dr2 As DataRow In dt.Rows
            If Not dt2.Columns.Contains(dr2.Item("Diskname").ToString) Then
                dt2.Columns.Add(dr2.Item("Diskname").ToString, GetType(System.String))
            End If
        Next

        Dim dv2 As DataView = dt2.DefaultView
        Dim ex As Integer

        For x As Integer = 0 To dt.Rows.Count - 1
            Dim dr2 As DataRow
            Dim dr7 As DataRow
            Dim dr8 As DataRow
            dv2.Sort = "Server"
            ex = dv2.Find(dt.Rows(x).Item(0))
            If ex >= 0 Then
                dr2 = dt2.Rows(ex)
                dr7 = dt2.Rows(ex + 1)
                dr8 = dt2.Rows(ex + 2)
            Else
                dr2 = dt2.NewRow
                dr7 = dt2.NewRow
                dr8 = dt2.NewRow
            End If
            dr2(0) = dt.Rows(x).Item(0)
            dr7(0) = dt.Rows(x).Item(0)
            dr8(0) = dt.Rows(x).Item(0)

            dr2(1) = dt.Columns(2).ColumnName
            dr7(1) = dt.Columns(3).ColumnName
            dr8(1) = dt.Columns(4).ColumnName

            dr2.Item(dt2.Columns.IndexOf(dt.Rows(x).Item("diskname"))) = dt.Rows(x).Item(2)
            dr7.Item(dt2.Columns.IndexOf(dt.Rows(x).Item("diskname"))) = dt.Rows(x).Item(3)
            dr8.Item(dt2.Columns.IndexOf(dt.Rows(x).Item("diskname"))) = dt.Rows(x).Item(4)
            If ex = -1 Then
                dt2.Rows.Add(dr2)
                dt2.Rows.Add(dr7)
                dt2.Rows.Add(dr8)
            End If
        Next
        dv2.Sort = ""
        dv2 = Nothing
        For x As Integer = 0 To dt2.Rows.Count
            If x Mod 3 <> 0 Then
                dt2.Rows(x).Item(0) = ""
            End If
        Next
        Return (dt2)

    End Function

    Private Sub Grid1_ItemDataBound(ByVal sender As Object, ByVal e As DataGridItemEventArgs) Handles DataGrid1.ItemDataBound

        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
            e.Item.Cells(0).Font.Bold = True
            e.Item.Cells(0).HorizontalAlign = HorizontalAlign.Left
            e.Item.Cells(1).HorizontalAlign = HorizontalAlign.Left
            'e.Item.HorizontalAlign = HorizontalAlign.Center
        ElseIf e.Item.ItemType = ListItemType.Header Then
            'e.Item.Cells(1).HorizontalAlign = HorizontalAlign.Right
        End If

    End Sub

End Class
tbaseflugAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mmarinovCommented:
Hi tbaseflug,

you can try something like that

Private Sub Grid1_ItemDataBound(ByVal sender As Object, ByVal e As DataGridItemEventArgs) Handles DataGrid1.ItemDataBound

        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
            e.Item.Cells(0).Font.Bold = True
            e.Item.Cells(0).HorizontalAlign = HorizontalAlign.Left
            e.Item.Cells(1).HorizontalAlign = HorizontalAlign.Left

            if  Int32.Parse(e.Item.Cells(4).Text) > 25  Then
                e.Item.Cells(4).BackColor = Color.White
            Else If Int32.Parse(e.Item.Cells(4).Text) <26 Then
                e.Item.Cells(4).BackColor = Color.Yellow
.
.
.

           
        ElseIf e.Item.ItemType = ListItemType.Header Then
            'e.Item.Cells(1).HorizontalAlign = HorizontalAlign.Right
        End If

    End Sub


Regards!
B..M
mmarinov
0
arvindb1Commented:
0
tbaseflugAuthor Commented:
All -

Not sure if I mentioned it but this is a web app.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

mmarinovCommented:
my code is just for web application :-)

B..M
mmarinov
0
tbaseflugAuthor Commented:
The issue is that the datagrid is looping through a series of servers - with 4 rows of data associated with each server.  The percentage is located on every fourth row - so would need to only compare the data in the cells on every fourth row.
0
RonaldBiemansCommented:
Hi Tbaseflug, Do you want to highlight the entire row or just the cell containing the free space ?
0
RonaldBiemansCommented:
sorry stupid question, forget it.

The problem is that we need information from another row, so the only way to go, is to go back to your DT2.

try this

    Private Sub DataGrid1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemDataBound

        Dim li As ListItemType
        If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then
            If e.Item.Cells(1).Text = "FreeSpace" Then
                For yy As Integer = 2 To e.Item.Cells.Count - 1
                    If Not IsDBNull(dt2.Rows(e.Item.ItemIndex).Item(yy)) Then
                        Select Case dt2.Rows(CInt(e.Item.ItemIndex)).Item(yy) / dt2.Rows(e.Item.ItemIndex - 2).Item(yy)
                            Case Is <= 0.05
                                e.Item.Cells(yy).BackColor = Color.Red
                            Case Is <= 0.1
                                e.Item.Cells(yy).BackColor = Color.Orange
                            Case Is <= 0.25
                                e.Item.Cells(yy).BackColor = Color.Yellow
                            Case Else
                                e.Item.Cells(yy).BackColor = Color.White
                        End Select
                    End If
                Next
            End If
        End If
    End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.