10 Replies - 2386 Views - Last Post: 10 November 2012 - 01:23 AM Rate Topic: -----

#1 lulu79  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-October 12

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

Posted 09 November 2012 - 12:48 AM

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.Crimson
                    End If
                Next
        



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

Is This A Good Question/Topic? 0
  • +

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

#2 lucky3  Icon User is offline

  • Friend lucky3 As IHelpable
  • member icon

Reputation: 231
  • View blog
  • Posts: 765
  • Joined: 19-October 11

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

Posted 09 November 2012 - 01:38 AM

And what's wrong with the solution you were so happy about?
Was This Post Helpful? 0
  • +
  • -

#3 lulu79  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-October 12

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

Posted 09 November 2012 - 02:05 AM

I have to make a change on that.

Before this the requirement is only to change to red when exceed 25.

But now, it has other requirement where after it exceed 25,value in database status will become 1 and the bar color will change to red (means the status now is in danger).

Then, user will click a button to fill in a countermeasure form. After press submit button, a value will become 2 and the chart color will change to green (means the status now is safe).

That's why I have to make a change on the previous chart..
Was This Post Helpful? 0
  • +
  • -

#4 lucky3  Icon User is offline

  • Friend lucky3 As IHelpable
  • member icon

Reputation: 231
  • View blog
  • Posts: 765
  • Joined: 19-October 11

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

Posted 09 November 2012 - 02:17 AM

OK, so first, if value goes above 25, you change bar to red, and you have it working that way. Then user clicks something, and solves the problem. Database status value changes BUT shouldn't the numeric value, which was before above 25 change too? If it does, then reload chart, and you're done, if not, then you might want to reconsider your solution.
Was This Post Helpful? 0
  • +
  • -

#5 lulu79  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-October 12

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

Posted 09 November 2012 - 02:41 AM

Yes, after user take action, the bar color will change from red to green.
But the numeric value of the bar (from example 45) will not change.

I tried reloading the bar chart but it still not work :(/>

What am I supposed to do to make it work? any help from you guys is much appreciated.
Was This Post Helpful? 0
  • +
  • -

#6 lucky3  Icon User is offline

  • Friend lucky3 As IHelpable
  • member icon

Reputation: 231
  • View blog
  • Posts: 765
  • Joined: 19-October 11

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

Posted 09 November 2012 - 03:24 AM

Then on chart load/reload event do the following:
Check if value >= 25 and status is 2 (or whatever you have it for red bar), and let it be red, else, make it blue.
Was This Post Helpful? 0
  • +
  • -

#7 lulu79  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-October 12

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

Posted 09 November 2012 - 09:25 PM

I have insert client-side for the page to reload after certain minutes. (using timer)

But the problem is, even i set the value to 2 in database and refresh the page or open/close the page, the color still wont change to green.
(logically, when it checks the value in database is 2, it will become green and if 1, it will become red right?)

I dont know whether my loop is not correct or I cant make a condition like that (condition take from database)
Was This Post Helpful? 0
  • +
  • -

#8 lucky3  Icon User is offline

  • Friend lucky3 As IHelpable
  • member icon

Reputation: 231
  • View blog
  • Posts: 765
  • Joined: 19-October 11

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

Posted 09 November 2012 - 11:54 PM

If I could only guess, what your code looks like... perhaps I could help you then... hmmmmmmmmmmmmm
Was This Post Helpful? 0
  • +
  • -

#9 lulu79  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-October 12

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

Posted 10 November 2012 - 12:36 AM

sorry.. here is my server-side code:

[code]
Option Explicit On
Imports System.Data.SqlClient
Imports System.Data
Imports System.Web.UI.DataVisualization.Charting
Imports System.Drawing
Imports System.Net.Mail
Imports Microsoft.Office.Interop.Outlook
Imports System.Runtime.InteropServices.Marshal
Imports Microsoft.Office.Interop

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"

DBConn.Open()

Dim SQLstr As String = " SELECT dbo.VAC_LineStatus.Line, SUM(KYUERR1 + VISION1 + KEIJYOU1) AS PICKUP_ERR, SUM((VAC_JSINFO.KYUERR1 + "
SQLstr &= " VAC_JSINFO.VISION1 + VAC_JSINFO.KEIJYOU1) * VAC_JSINFO.UNITCOST) AS RM, "
SQLstr &= " dbo.VAC_LineStatus.Status "
SQLstr &= " FROM dbo.VAC_LineStatus INNER JOIN "
SQLstr &= " VAC_JSINFO ON dbo.VAC_LineStatus.Line = VAC_JSINFO.LINE "
SQLstr &= " WHERE (VAC_JSINFO.COLLECTDATE > '" & strtTime & "') AND "
SQLstr &= " (VAC_JSINFO.COLLECTDATE < '" & endTime & "') AND (VAC_JSINFO.KYUERR1 >= 0) "
SQLstr &= " GROUP BY VAC_JSINFO.LINE, dbo.VAC_LineStatus.Line, dbo.VAC_LineStatus.Status "
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

'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) > 20 Then
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.Crimson
End If
Next

End If
Next
End Sub

Public Function GetMax() As String

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"


DBConn.Open()



Dim SQLstr As String = " SELECT TOP 1 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)
Dim max As String

max = myCommand.ExecuteScalar().ToString()

Return max


End Function

Private Sub Chart1_PostPaint(ByVal sender As Object, ByVal e As ChartPaintEventArgs) Handles Chart1.PostPaint

If TypeOf e.ChartElement Is ChartArea Then

Dim area As ChartArea = CType(e.ChartElement, ChartArea)

Dim x1 As Single = CSng(area.AxisX.ValueToPixelPosition(area.AxisX.Minimum))
Dim x2 As Single = CSng(area.AxisX.ValueToPixelPosition(area.AxisX.Maximum))
Dim y As Single = CSng(area.AxisY.ValueToPixelPosition(20))

Dim x3 As Single = CSng(area.AxisX.ValueToPixelPosition(area.AxisX.Minimum))
Dim x4 As Single = CSng(area.AxisX.ValueToPixelPosition(area.AxisX.Maximum))
Dim y1 As Single = CSng(area.AxisY.ValueToPixelPosition(25))

e.ChartGraphics.Graphics.DrawLine(New Pen(Color.Blue, width:=5), x1, y, x2, y)
e.ChartGraphics.Graphics.DrawLine(New Pen(Color.Red, width:=5), x3, y1, x4, y1)

End If

End Sub


End Class
[/codel
Was This Post Helpful? 0
  • +
  • -

#10 lucky3  Icon User is offline

  • Friend lucky3 As IHelpable
  • member icon

Reputation: 231
  • View blog
  • Posts: 765
  • Joined: 19-October 11

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

Posted 10 November 2012 - 12:57 AM

In your
For Each dp As DataPoint In Chart1.Series("Wilma").Points
If dp.YValues(0) > 20 Then
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.Crimson
End If
Next



you need to get dp x index, and compare it to the "Status" value on the same index in grpDT.Rows.

This post has been edited by lucky3: 10 November 2012 - 12:58 AM

Was This Post Helpful? 0
  • +
  • -

#11 lulu79  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 02-October 12

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

Posted 10 November 2012 - 01:23 AM

I'm sorry but can you please show me the example?
I google how to compare it with value from database but I cant understand how can I compare with the dp x index.

Is it something like this? :

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  'Find all points with a second Y value equal to 10, and change their color.
  Dim index As Integer = 0
  'Find first point with a Y2 value of 10.
  Dim dataPoint As DataPoint = Chart1.Series("Series1").Points.FindValue(10, "Y2", index)
  While Not (dataPoint Is Nothing)
        dataPoint.Color = Color.FromArgb(255, 128, 128)
        'Find all other data points with a second Y value 10.
        index += 1
        dataPoint = Chart1.Series("Series1").Points.FindValue(10, "Y2", index)
  End While
End Sub



Was This Post Helpful? 0
  • +
  • -

Page 1 of 1