How to Change Bar Chart Color Based on Value in Database?

[lulu79]

How can I change the color of bar chart based on value in database column?

For example, in table "STATUS" in my database, I have 3 type of value. What I want is:

1) If the value = 0, then color will change to blue.

2) If the value = 1, then color will change to red.

3) If the value = 2, then color will change to green.

I use MSChart to create the chart.

I set the default color as blue.
I tried to store STATUS value in dataset and do for loop to retrieve the value.
If i found value = 2, then it will change to green.
Else the color will be red.
But its not worked.

Here is the code:

       For Each dp As DataPoint In Chart1.Series("Wilma").Points
            For i As Integer = 0 To grpDT.Rows.Count - 1
                    If (grpDT.Rows(i).Item("Status").ToString() = "2") Then
                        dp.Color = Color.Green
                    Else
                        dp.Color = Color.Red
                    End If
                Next
        Next

Can someone help me please. I'm stuck at this almost a week :(

[Pgmer]

logic looks ok. Check dp.Color is exact method to give the color to chart. You can try giving color to one chart in form load or click of button for testing. if this works then above code should work

[lulu79]

no it still not work.. i try to reload it and nothing happen. it still wont change to green.

Here is my complete code:

    Option Explicit On
    Imports System.Data.SqlClient
    Imports System.Data
    Imports System.Web.UI.DataVisualization.Charting
    Imports System.Drawing

    Partial Class LineSpoilage
        Inherits System.Web.UI.Page

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

            Dim DBConn As New SqlConnection("server=xxxx;uid=xxxx;pwd=xxxx;database=xxxx")
            Dim DBCmd As New SqlCommand
            Dim da As New SqlDataAdapter
            Dim ds As New DataSet()
            Dim strtTime As String
            Dim endTime As String

            strtTime = DateTime.Today.ToString("yyyy/MM/dd") & " " & "06:15:00"
            endTime = DateTime.Today.ToString("yyyy/MM/dd") & " " & "18:15:00"

            Try
                DBConn.Open()



                Dim SQLstr As String = " SELECT   LINE, SUM(KYUERR1 + VISION1 + KEIJYOU1) AS PICKUP_ERR, SUM((KYUERR1 + VISION1 + KEIJYOU1) * UNITCOST) AS RM "
                SQLstr &= " FROM         VAC_JSINFO "
                SQLstr &= " WHERE       (COLLECTDATE > '" & strtTime & "') AND (COLLECTDATE < '" & endTime & "') AND (KYUERR1 >= 0) "
                SQLstr &= " GROUP BY LINE "
                SQLstr &= " ORDER BY RM DESC "


                Dim myCommand As New SqlCommand(SQLstr, DBConn)
                da.SelectCommand = myCommand
                da.Fill(ds)


                DataGridView1.DataSource = ds
                DataGridView1.DataBind()



                '''''''''CREATE GRAPH ''''''''''''''

                With Chart1
                    .DataSource = ds
                    .Legends.Clear()
                    .ChartAreas.Clear()
                    .ChartAreas.Add(New ChartArea("Fred"))

                    With .ChartAreas("Fred")

                        'Play with this to get the effect you want
                        .AxisX.LabelStyle.Interval = 1
                        .AxisY.LabelStyle.Interval = 5
                        .AxisX.Title = "LINE"
                        .AxisY.Title = "SPOILAGE (RM)"
                        .AxisX.TitleFont = New Font("arial", 12)
                        .AxisY.TitleFont = New Font("arial", 12)
                        .AxisX.MajorGrid.Enabled = False

                        Dim maxY = GetMax()

                        If maxY < 30 Then
                            .AxisY.maximum = 30

                        End If
                    End With

                    .Series.Clear()
                    .Series.Add(New Series("Wilma"))

                    With .Series("Wilma")
                        .ChartArea = "Fred"
                        .ChartType = SeriesChartType.Column
                        .XValueType = ChartValueType.String
                        .YValueType = ChartValueType.Int64
                        .XValueMember = "LINE"
                        .YValueMembers = "RM"
                        .Color = Color.DodgerBlue



                    End With


                End With


            Catch ex As Exception


            End Try



            'Close Database connection 
            'and Dispose Database objects 

            DBConn.Close()



            label1.text = SumC1()
            label3.text = SumYC1()
            label4.text = SumYC2()

        End Sub

        Private Sub Chart1_Customize(ByVal sender As Object, ByVal e As System.EventArgs) Handles Chart1.Customize

            Dim DBConn As New SqlConnection("server=xxxx;uid=xxxx;pwd=xxxx;database=xxxx")
            Dim DBCmd As New SqlCommand
            Dim da As New SqlDataAdapter
            Dim ds As New DataSet()

            DBConn.Open()


            Dim SQLstr As String = "    SELECT     Line, Status "
            SQLstr &= "    FROM        VAC_LineStatus "

            Dim myCommand As New SqlCommand(SQLstr, DBConn)
            da.SelectCommand = myCommand
            da.Fill(ds)
            Dim grpDT As New DataTable
            grpDT = ds.Tables(0)

            For Each dp As DataPoint In Chart1.Series("Wilma").Points
              If dp.YValues(0) > 25 Then
                For i As Integer = 0 To grpDT.Rows.Count - 1
                    If dp.YValues(0) > 25 And (grpDT.Rows(i).Item("Status").ToString() = "2") Then
                        dp.Color = Color.Green
                    Else
                        dp.Color = Color.red
                    End If
                Next

               End If

            Next

        End Sub