Question Chart Questions (Series colors, graph refresh on timer, etc)

[jsmith08]

I am working on an application that ties into a SQL database on the back end. Currently the main form contains a series of combo boxes (Graph type, data format, location, and customer), two date pickers, and a button to launch the selections in a new form. Everything works just fine except there are a couple of things I can't seem to accomplish.

Example Data:

         Company_Name       Percent TMHB
              A                 100.00 
              B                  22.50    
              C                  43.25

Case Select (with commented out lines for variations attempted):

                'For i As Integer = 0 To results.Tables("TMHB").Rows.Count - 1
                'Select Case results.Tables("TMHB").Rows(0).Item(column.ToString)
                Select Case results.Tables("TMHB").Rows(i).Item("Percent TMHB")
                    Case Is <= 24
                        Chart1.Series(seriesName).Color = Color.Green
                    Case 25 To 49
                        Chart1.Series(seriesName).Color = Color.Yellow
                    Case 50 To 74
                        Chart1.Series(seriesName).Color = Color.Orange
                    Case 75 To 99
                        Chart1.Series(seriesName).Color = Color.Red
                    Case Is >= 100
                        Chart1.Series(seriesName).Color = Color.Black
                End Select

1) I would like to be able to use some sort of case statement to color the bars in my chart based on value. I have tried approaching this in a couple of different ways but neither of the options seem to work. First, I tried this (the If Not is used because depending on what values are passed to the stored procedure in SQL, different columns will be returned):

    For Each column In results.Tables("TMHB").Columns
                If Not ((column.ToString = "Year") Or (column.ToString = "Month") Or (column.ToString = "Company_Name")) Then
                    Dim Series1 As New Series
                    Dim seriesName As String = column.ToString
                    Series1.Name = seriesName
                    Chart1.Series.Add(seriesName)
                    Chart1.Series(seriesName).YValueMembers = seriesName
                    If combo_Customer.SelectedValue = "All" Then
                        Chart1.Series(seriesName).XValueMember = "Company_Name"
                    Else
                        Chart1.Series(seriesName).XValueMember = "Month"

....

Only one series is created with this method, so I can see how/why a Select Case would set all bars the same color. I have also tried creating a series for each row in the returned dataset but it results in 22 bars of different colors, for each of the rows (484 bars):

For i As Integer = 0 To results.Tables("TMHB").Rows.Count - 1
                Dim Series1 As New Series
                Dim seriesName As String = results.Tables("TMHB").Rows(i).Item("Company_Name")
                Series1.Name = seriesName
                Chart1.Series.Add(seriesName)
                Chart1.Series(seriesName).YValueMembers = "Percent TMHB"
                If combo_Customer.SelectedValue = "All" Then
                    Chart1.Series(seriesName).XValueMember = "Company_Name"
                Else
                    Chart1.Series(seriesName).XValueMember = "Month"
                End If

So is there any way to modify the second set of code there to only select the value from the "Percent TMHB" cell that corresponds with Row(i), rather than ALL values from the "Percent TMHB" column for EACH Row(i)?

2) Since I am drawing/creating these graphs in a new window, is there any way to also create a new timer on the "newFrm" and have the tick refresh the data? I've struggled with this but I feel like it should be easy. Say someone chooses a "TMHB", "Percentage", "Chicago", "All" (Graph type, data format, location, customer) and launches the graph, which opens in a new window, then they want to do "TMHB", "Percentage", "LA", "All" and have both windows up, refreshing automatically throughout the day. Can I somehow store the SelectCommand.Parameters values in the new form/timer so that it will query the database again every 5 minutes?

Example code for the StoredProcedure and creating the new form:

If combo_GraphType.SelectedItem = "TMHB" Then
            Dim newFrm As New Form
            Dim newChart As New Chart
            Dim newChartArea As New ChartArea
            newFrm.Size = New System.Drawing.Size(689, 426)
            newFrm.MinimumSize = newFrm.Size
            newChart.Dock = DockStyle.Fill

            newFrm.Show()
            newFrm.Controls.Add(newChart)
            newChart.ChartAreas.Add(newChartArea)
            newChart.Series.Clear()
            newChartArea.AxisX.Interval = 1

Try
                DBConnect()
                Dim sQuery As New SqlDataAdapter("usp_TMHB", DBConnect)
                sQuery.SelectCommand.CommandType = CommandType.StoredProcedure
                sQuery.SelectCommand.Parameters.Add(New SqlParameter("@location", SqlDbType.VarChar, 20, ParameterDirection.Input))
                sQuery.SelectCommand.Parameters.Add(New SqlParameter("@customer", SqlDbType.VarChar, 50, ParameterDirection.Input))
                sQuery.SelectCommand.Parameters.Add(New SqlParameter("@dataformat", SqlDbType.VarChar, 20, ParameterDirection.Input))
                sQuery.SelectCommand.Parameters.Add(New SqlParameter("@startdate", SqlDbType.DateTime, ParameterDirection.Input))
                sQuery.SelectCommand.Parameters.Add(New SqlParameter("@enddate", SqlDbType.DateTime, ParameterDirection.Input))
                sQuery.SelectCommand.Parameters("@location").Value = combo_Location.SelectedItem
                sQuery.SelectCommand.Parameters("@customer").Value = combo_Customer.SelectedValue
                sQuery.SelectCommand.Parameters("@dataformat").Value = dataformat
                sQuery.SelectCommand.Parameters("@startdate").Value = dt_From.Value.ToString("MM-dd-yyyy 00:00:00.000")
                sQuery.SelectCommand.Parameters("@enddate").Value = dt_To.Value.ToString("MM-dd-yyyy 23:59:59.999")
                Dim results As New DataSet()
                sQuery.Fill(results, "TMHB")

Thanks!

 

[jsmith08]

Anyone have any thoughts on this? I was able to rework the code somewhat to get the bars colored properly by creating y-axis points based on the "Percent TMHB" values but all the bars get squeezed into a single x-axis value (1) with no "Company A", "Company B", or "Company C" x-axis labels. I can supply more code, screenshots, etc. as needed. Just need to know what is missing or how I can better illustrate the issue if my original post is not clear.

Thanks