VB.net Automatic chart update with new data entry

Question

my chart loads data from a datagridview.

Please how can i automatically update my chart with new data if new values are inserted into the datagridview.

Thanks

---

my chart is binded to table1 and table 2 in my datagridview. my datagrid gets values from a datatable. here is a sample:

        Dim myData As New DataTable
        wcAdapter.SelectCommand = wcCommand
        wcAdapter.Fill(myData)

-

        Chart1.DataSource = myData

        Chart1.Series("Series1").ValueMemberX = "table1"
        Chart1.Series("Series1").ValueMembersY = "table2"

any suggestions?

Answer 1

Hi Gbolahan,

since the new data is inserted into a database, you will only need to rebind the gridview to it's source in order to display the new incoming data.

You should isolate the code that binds data to your chart in a function and have it called every time a new field gets inserted:

Function FillChartWithData()
   Dim myData As New DataTable
   wcAdapter.SelectCommand = wcCommand
   wcAdapter.Fill(myData)
   ...
   Chart1.Series("Series1").ValueMemberX = "table1"
   Chart1.Series("Series1").ValueMembersY = "table2"
End Function

EDIT

I looked at your coded and it seems you're missing the part responsible for inserting new data inside the 'ratingout' table. You should create a function that allows you to insert new data, something along the line of:

Dim insertRating = "INSERT INTO ratingout VALUES (@NewTeleSalesName, @NewDate);"
Dim insertCmd As New MySqlCommand(insertRating, wcconn)
insertCmd.Parameters.Add("@NewTeleSalesName", MySqlDbType.VarChar, 255, "teleSalesName")
insertCmd.Parameters.Add("@NewDate", MySqlDbType.Datetime, 8, New DateTime(2010, 8, 5))
insertCmd.ExecuteNonQuery()

Answer 2

 Try
        wcconn.Open()
        Dim wcCommand As New MySqlCommand()
        ''telesales name


        ' Dim wcQuery = "SELECT ID, Telesales, SUBSTRING(lastupdatedate, 1, 10) as 'Day', SUBSTRING(lastupdatetime FROM -9 FOR 6) as 'Time' FROM ratingout where Telesales='" & cbTelesales.Text & "' and lastupdatedate= '" & newDate & "' and lastupdatedate is not null and lastupdatetime is not null ORDER BY lastupdatetime ;"
        ' wcCommand.Connection = wcconn
        ' wcCommand.CommandText = wcQuery

        Dim newDate As String
        newDate = dateWorkCheck.Text
        newDate = newDate.Replace("/", "-")

        Dim y, m, d As String
        y = newDate.Substring(6, 4)
        m = newDate.Substring(3, 2)
        d = newDate.Substring(0, 2)

        newDate = y & "-" & m & "-" & d

        Dim wcQuery = "SELECT ID, Telesales, lastupdatedate as 'Day', SUBSTRING(lastupdatetime FROM -8 FOR 2)  as 'Time' FROM ratingout where Telesales='" & cbTelesales.Text & "' and lastupdatedate= '" & newDate & "' and lastupdatedate is not null and lastupdatetime is not null ORDER BY lastupdatetime ;"
        wcCommand.Connection = wcconn
        wcCommand.CommandText = wcQuery


        Dim wcData As New DataTable
        wcAdapter.SelectCommand = wcCommand
        wcAdapter.Fill(wcData)



        Dim i = 0
        If wcData.Rows.Count = 0 Then
            wcAdapter.Dispose()
            Try

                Dim wQuery = "SELECT ID, Telesales, lastupdatedate as 'Day', SUBSTRING(lastupdatetime FROM -8 FOR 2)  as 'Time' FROM ratingout where Telesales='" & cbTelesales.Text & "' and lastupdatedate= '" & dateWorkCheck.Text & "' and lastupdatedate is not null and lastupdatetime is not null ORDER BY lastupdatetime ;"
                wcCommand.Connection = wcconn
                wcCommand.CommandText = wQuery


                Dim wData As New DataTable
                wcAdapter.SelectCommand = wcCommand
                wcAdapter.Fill(wData)

                wData.Columns.Add("tt")
                wData.Columns.Add("num")


                wcData.Columns.Add("tt")
                wcData.Columns.Add("num")
                'dgvWorkCheck.AutoSizeRowsMode = DataGridViewAutoSizeRowMode.AllCells
                Dim dr As DataRow
                For Each dr In wData.Rows
                    If lastV Is Nothing OrElse Not ColumnEqual(lastV, dr("Time")) Then
                        ''check if first value is nothing
                        If lastV = Nothing Then
                            lastV = "00"
                            l = "0"
                        Else

                            dr("tt") = lastV
                            dr("num") = l
                            'wcData.Tables("ratingout").Rows(I)("ID") = dr("ID")
                        End If
                        ListBox1.Items.Add(lastV & " <--> " & l)
                        lastV = dr("Time")
                        l = 1
                    ElseIf lastV Is Nothing OrElse ColumnEqual(lastV, dr("Time")) Then
                        l += 1
                        'Dim series1 As New Series()
                        'series1.Points.Add(l)
                    End If


                    For I = I To wData.Rows.Count
                        If I <> wData.Rows.Count Then
                            I += 1
                            If i = wData.Rows.Count Then

                                dr("tt") = lastV
                                dr("num") = l

                                ListBox1.BeginUpdate()
                                ListBox1.Items.Add(dr("Telesales") & " between[" & lastV & " and 17:00, ] <--> " & l & "[ records ]")
                                ListBox1.EndUpdate()
                            End If

                            GoTo n
                        Else
                            MsgBox("last data")
                        End If
                    Next
 n:
                Next
                txtRec.Text = wData.Rows.Count

                dgvWorkCheck.DataSource = wData

                ''chart
                Dim ChartArea2 As ChartArea = New ChartArea()
                Dim Legend2 As Legend = New Legend()
                Dim Series2 As Series = New Series()
                Dim Chart2 = New Chart()
                Me.Controls.Add(Chart2)

                ChartArea2.AxisX.LabelStyle.Angle = -90
                ChartArea2.AxisX.LabelStyle.Interval = 1

                ChartArea2.AxisY.LabelStyle.Angle = -90
                ChartArea2.AxisY.LabelStyle.Interval = 5

                ChartArea2.Name = "ChartArea2"
                Chart2.ChartAreas.Add(ChartArea2)
                Legend2.Name = "Legend2"
                Chart2.Legends.Add(Legend2)
                Chart2.Location = New System.Drawing.Point(12, 113)
                Chart2.Name = "Chart2"
                Series2.ChartArea = "ChartArea2"
                Series2.Legend = "Legend2"
                Series2.Name = "Series2"
                Chart2.Series.Add(Series2)
                Chart2.Size = New System.Drawing.Size(1145, 604)
                Chart2.TabIndex = 0
                Chart2.Text = "Chart2"

                Chart2.Series("Series2").XValueMember = "tt"
                Chart2.Series("Series2").YValueMembers = "num"

                Chart2.DataSource = dgvWorkCheck.DataSource

                Chart2.DataBind()

            Catch ex As Exception
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
            Exit Try