VB.net setting negitive values to red and positive to blue in a chart

Hey Robert

I couldn't get the code to display red and blue

Here is the code I am using.
Any help would be great

 Private Sub VenueData()

        Dim dt As DataTable
        dt = CreateVenueDataTable()

        ' frmBetAssist.Chart2.Titles.Add("Title1")
        frmBetAssist.Chart2.Titles("Title1").Text = "Profit By Venue"
        'frmBetAssist.Chart2.Titles("Title1").DockedToChartArea = Nothing

        'Set the DataSource property of the Chart control to the DataTabel
        frmBetAssist.Chart2.DataSource = dt

        'Give two columns of data to Y-axle
        frmBetAssist.Chart2.Series(0).YValueMembers = "Profit"

        'Set the X-axle as date value
        frmBetAssist.Chart2.Series(0).XValueMember = "Venue"


        frmBetAssist.Chart2.Series(0).Points.DataBind(dt.Rows, "Venue", "Profit", "color=color")
        'frmBetAssist.Chart2.Series(0).ToolTip = "#CUSTOMPROPERTY(color)"
        For Each p As DataPoint In frmBetAssist.Chart2.Series(0).Points
            p.Color = IIf(p.YValues(0) < 0, Color.Red, Color.Blue)
        Next




        'Bind the Chart control with the setting above
        'frmBetAssist.Chart2.DataBind()

    End Sub

    Private Function CreateVenueDataTable() As DataTable
        Dim str As String = "C:\BA\ChartData\ChartData.txt"
        Dim list As List(Of String) = New List(Of String)()
        Dim list2 As List(Of String) = New List(Of String)()


        If (System.IO.File.Exists(str)) Then
            list.Clear()
            list.AddRange(System.IO.File.ReadAllLines(str))
            list2 = list
            Dim profit As Decimal = 0
            'Create a DataTable as the data source of the Chart control
            Dim dt As New DataTable

            'dt.Columns.Add("Venue")
            'dt.Columns.Add("Profit")
            dt.Columns.AddRange({New DataColumn("Venue", GetType(String)), New DataColumn("Profit", GetType(Decimal)), New DataColumn("color", GetType(String))})
            dt.Columns("color").Expression = "IIF(Profit < 0, 'red', 'blue')"

            For i As Integer = 0 To list.Count - 1
                Dim parts As String() = list(i).Split(New Char() {";"c})
                Dim valueToSearch As String = parts(2)
                Dim exists As Boolean = Nothing
                For Each dRow As DataRow In dt.Rows
                    If Convert.ToString(dRow(0)).Contains(valueToSearch) = True Then
                        exists = True
                        Exit For
                    ElseIf Convert.ToString(dRow(0)).Contains(valueToSearch) = False Then
                        exists = False
                    End If
                Next
                If Not exists Then
                    For x As Integer = 0 To list2.Count - 1
                        Dim parts1 As String() = list2(x).Split(New Char() {";"c})
                        If parts(2) = parts1(2) Then
                            profit = profit + parts1(5)
                        End If
                    Next
                    Dim dr As DataRow

                    'Add rows to the table which contains some random data for demonstration
                    dr = dt.NewRow()
                    dr("Venue") = parts(2)
                    dr("Profit") = profit
                    dt.Rows.Add(dr)
                End If
            Next
            Return dt
        End If

    End Function

Select allOpen in new window

The DataBind call replaces the statements on lines 11, 14 & 17 so those should not be necessary anymore.

Do you get an errror?

Can you give a few example lines from the text file?

Here's the file
ChartData.txt

Works now thanks for the help

Great help thks

OK, great! There are some other issues in your code. Are you aware/is this a work in progress or would you like me to share my thoughts on that?

by all means any help would be great

Couple of things:

1) 'profit' is only initialized once, should that not be reset inside the loop, say before line 66?

2) The comparison done on datarows seems to me dangerous, what if you ever get a key that is part of another key like "Sydney" and "Sydney South"? Probably better to check the specific field there as well.

That whole bit of code can probably be replaced by a nice bit of LINQ, now that is not my strongest subject but there are other experts here that dream in that sort of code ;-)

I would probably do something more like this:

    Private Function CreateVenueDataTable() As DataTable
        Dim str As String = "C:\BA\ChartData\ChartData.txt"
        Dim list As List(Of String) = New List(Of String)()

        'Create a DataTable as the data source of the Chart control
        Dim dt As New DataTable
        dt.Columns.AddRange({New DataColumn("Venue", GetType(String)), New DataColumn("Profit", GetType(Decimal))})
        If (System.IO.File.Exists(str)) Then
            list.Clear()
            list.AddRange(System.IO.File.ReadAllLines(str))
            Dim profit As Decimal = 0
            Dim dict As New Dictionary(Of String, Decimal)
            For i As Integer = 0 To list.Count - 1
                Dim parts As String() = list(i).Split(New Char() {";"c})
                Dim valueToSearch As String = parts(2)
                profit = parts(5)
                If dict.ContainsKey(valueToSearch) Then
                    dict(valueToSearch) += profit
                Else
                    dict.Add(valueToSearch, profit)
                End If
            Next
            For Each key As String In dict.Keys
                Dim dr As DataRow

                'Add rows to the table which contains some random data for demonstration
                dr = dt.NewRow()
                dr("Venue") = key
                dr("Profit") = dict(key)
                dt.Rows.Add(dr)
            Next
        End If
        Return dt
    End Function

Select allOpen in new window

Maybe the file won't get very big but going through it several times for each line can get very slow. Dictionary already has the ContainsKey out of the box.

Actually, Dictionary is not even necessary as DataTable has a Select that can tell you if the key is already in there.

    Private Function CreateVenueDataTable() As DataTable
        Dim str As String = "C:\BA\ChartData\ChartData.txt"
        Dim list As List(Of String) = New List(Of String)()

        'Create a DataTable as the data source of the Chart control
        Dim dt As New DataTable
        dt.Columns.AddRange({New DataColumn("Venue", GetType(String)), New DataColumn("Profit", GetType(Decimal))})
        If (System.IO.File.Exists(str)) Then
            list.Clear()
            list.AddRange(System.IO.File.ReadAllLines(str))
            Dim profit As Decimal = 0
            For i As Integer = 0 To list.Count - 1
                Dim parts As String() = list(i).Split(New Char() {";"c})
                Dim valueToSearch As String = parts(2)
                profit = parts(5)
                Dim drs() As DataRow = dt.Select("Venue = '" & valueToSearch.Replace("'", "''") & "'")
                If drs.Length > 0 Then
                    drs(0)("Profit") += profit
                Else
                    Dim dr As DataRow

                    'Add rows to the table which contains some random data for demonstration
                    dr = dt.NewRow()
                    dr("Venue") = valueToSearch
                    dr("Profit") = profit
                    dt.Rows.Add(dr)
                End If
            Next
        End If
        Return dt
    End Function

Select allOpen in new window

both work a treat
Is one way better than the other??

Not really, I just introduced the Dictionary to be able to quickly see if a key already exists before realizing that DataTable already provides similar function. In a production environment I would probably run some tests to see if either of them is significantly faster or uses less memory, but in this case I'm guessing that will not be a major concern... (yet?)

cool thx again for you advice and help
Jason