1 Replies - 297 Views - Last Post: 13 January 2018 - 05:31 PM Rate Topic: -----

#1 Damein   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 67
  • Joined: 29-March 14

SQL / Line Graph Questions

Posted 12 January 2018 - 10:08 AM

So I've got some weather data I want to crunch and I've got a "working" version done up but I think there's a lot of room for improvement, so just wanted to see what you guys thought :)

So it pulls Temp, Humidity, Pressure and a Timestamp from a DB and puts it into a line graph. Now my main issue is the first entry input into the line graph shows the time stamp, but not the data. Now I had to put the Dim Time(11) instead of Dim Time(10) or else my For statement to create the Temperature line graph would hang and nothing else would happen past that point. So if I could get some help to figure out why that is that would be awesome. I'm sure its something super simple I'm just missing :(

Thanks!

Imports System.Data
Imports System.Configuration
Imports MySql.Data.MySqlClient

Public Class Form1

    Dim Temps(10) As String
    Dim Hum(10) As String
    Dim Prs(10) As String
    Dim Time(11) As String
    Dim Count As Integer

    Dim Statement As String

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load


        Statement = "SELECT Temperature, Humidity, Pressure, timeStamp FROM readings ORDER BY ID DESC LIMIT 1"
        Using con As New MySqlConnection(DBConnection)
            Using cmd As New MySqlCommand(Statement)
                Using sda As New MySqlDataAdapter()
                    cmd.Connection = con
                    sda.SelectCommand = cmd

                    Dim ds As New DataSet()
                    sda.Fill(ds)

                    For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
                        TempTextBox.Text = ds.Tables(0).Rows(i).Item(0) & ""
                        HumTextBox.Text = ds.Tables(0).Rows(i).Item(1) & "%"
                        PrsTextBox.Text = ds.Tables(0).Rows(i).Item(2) & "in"
                        TimeStampBox.Text = ds.Tables(0).Rows(i).Item(3)
                    Next



                End Using
            End Using
        End Using

        TempTextBox.SelectionAlignment = HorizontalAlignment.Center
        HumTextBox.SelectionAlignment = HorizontalAlignment.Center
        PrsTextBox.SelectionAlignment = HorizontalAlignment.Center

        Count = 1

        Statement = "SELECT Temperature, Humidity, Pressure, timeStamp FROM readings ORDER BY ID DESC LIMIT 500"
        Using con As New MySqlConnection(DBConnection)
            Using cmd As New MySqlCommand(Statement)
                Using sda As New MySqlDataAdapter()
                    cmd.Connection = con
                    sda.SelectCommand = cmd

                    Dim ds As New DataSet()
                    sda.Fill(ds)
                    For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
                        If Count < 10 Then
                            If ds.Tables(0).Rows(i).Item(3).ToString.Contains(":00") Then
                                Temps(Count) = ds.Tables(0).Rows(i).Item(0)
                                Hum(Count) = ds.Tables(0).Rows(i).Item(1)
                                Prs(Count) = ds.Tables(0).Rows(i).Item(2)
                                Time(Count) = ds.Tables(0).Rows(i).Item(3)
                                Count += 1
                            End If
                        End If


                    Next



                End Using
            End Using
        End Using



        Count = 1
        For Each Element As String In Temps
            If Count < 10 Then
                Chart1.Series("Temps").Points.AddXY(Time(Count), Element)
                Count += 1
            End If


        Next

        Count = 1
        For Each Element As String In Hum
            If Count < 10 Then
                Chart2.Series("Humidity").Points.AddXY(Time(Count), Element)
                Count += 1
            End If
        Next


        Count = 1
        For Each Element As String In Prs
            If Count < 10 Then
                Chart3.Series("Pressure").Points.AddXY(Time(Count), Element)
                Count += 1
            End If
        Next

    End Sub

End Class




Is This A Good Question/Topic? 0
  • +

Replies To: SQL / Line Graph Questions

#2 IronRazer   User is offline

  • Custom Control Freak
  • member icon

Reputation: 1505
  • View blog
  • Posts: 3,808
  • Joined: 01-February 13

Re: SQL / Line Graph Questions

Posted 13 January 2018 - 05:31 PM

If you want tips to improve the code, the very first thing i would HIGHLY recommend is to turn on Option Strict and correct the type conversion errors you have. Get use to using it so you do not fall into the bad programming habits i see so often. For example, you have declared the arrays shown below as String type arrays. They hold Strings.
    Dim Temps(10) As String
    Dim Hum(10) As String
    Dim Prs(10) As String
    Dim Time(11) As String


However, later in the code you are using them to add the XY points to the Chart controls as shown in the three lines below. So, you are passing Strings from the arrays to the AddXY method of the Chart's DataPointCollection. If you take a look at the msdn documents for the DataPointCollection.AddXY Method you will see that you are suppose to be passing Double type numbers to it, not String types.
        Chart1.Series("Temps").Points.AddXY(Time(Count), Element) 'you are passing String types instead of Double types

        Chart2.Series("Humidity").Points.AddXY(Time(Count), Element) 'you are passing String types instead of Double types

        Chart3.Series("Pressure").Points.AddXY(Time(Count), Element) 'you are passing String types instead of Double types



I am also not sure if you understand that arrays are zero indexed, meaning the first element starts at the index of (0), not (1). So that means your Temps(10) array actually has 11 elements in it, it holds 11 Strings. I mention this because, in the loops where you add the data points to the 3 charts, you are starting your count variable at 1. So, you are completely skipping the first element at (0) in all the arrays in all three loops.

That could have something to do with your problem of having to declare the Time array 1 higher. However, that could also be from the values in your database having null values too i guess.

I would also guess that turning Option Strict on as i suggested you do, these lines would also show type conversion errors. The Item(n) property returns an Object type which you should be converting to a String type. This would be because, the Text property of any control requires a String type to be assigned to it.
                    For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
                        TempTextBox.Text = ds.Tables(0).Rows(i).Item(0) & "°"
                        HumTextBox.Text = ds.Tables(0).Rows(i).Item(1) & "%"
                        PrsTextBox.Text = ds.Tables(0).Rows(i).Item(2) & "in"
                        TimeStampBox.Text = ds.Tables(0).Rows(i).Item(3)
                    Next


You could use the ToString method to get a String representation of the Objects like...
                    For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
                        TempTextBox.Text = ds.Tables(0).Rows(i).Item(0).ToString & "°"
                        HumTextBox.Text = ds.Tables(0).Rows(i).Item(1).ToString & "%"
                        PrsTextBox.Text = ds.Tables(0).Rows(i).Item(2).ToString & "in"
                        TimeStampBox.Text = ds.Tables(0).Rows(i).Item(3).ToString
                    Next


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1