?
Solved

Datagrid - Conditional formatting

Posted on 2004-11-18
7
Medium Priority
?
1,591 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:tbaseflug
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 28

Expert Comment

by:mmarinov
ID: 12620035
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
 
LVL 5

Expert Comment

by:arvindb1
ID: 12622108
0
 

Author Comment

by:tbaseflug
ID: 12625382
All -

Not sure if I mentioned it but this is a web app.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 28

Expert Comment

by:mmarinov
ID: 12625618
my code is just for web application :-)

B..M
mmarinov
0
 

Author Comment

by:tbaseflug
ID: 12626609
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
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12642562
Hi Tbaseflug, Do you want to highlight the entire row or just the cell containing the free space ?
0
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 2000 total points
ID: 12642988
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Loops Section Overview
Suggested Courses
Course of the Month15 days, 22 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question