Hi i'm developing an app where i'm reading a value from a device and putting it into a mysql db, i have setup a function where the vb will calculate whether the new value being read has changed by 3% from the last value and if it has run the mysql query. What i need to do it read the value into a variable, then a new value and compare the two with the % function, how do i read a value and maybe add a delay before reading the new value (and it being overwritten) and doing the calculation?

Here is my current code:

Imports System.Data.SqlClient
Imports System.Data
Imports MySql.Data.MySqlClient


Public Class MainForm

    Private Sub Timer2_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer2.Tick
        Label1.Text = EthernetIPforSLCMicro1.ReadAny("F16:0")
        Label2.Text = EthernetIPforSLCMicro1.ReadAny("F16:2")
        Label13.Text = EthernetIPforSLCMicro1.ReadAny("F15:0")
        Label14.Text = EthernetIPforSLCMicro1.ReadAny("F15:1")
        Label15.Text = EthernetIPforSLCMicro1.ReadAny("F15:2")
        Label16.Text = EthernetIPforSLCMicro1.ReadAny("F15:3")
        Label17.Text = EthernetIPforSLCMicro1.ReadAny("B13/16")
        Label18.Text = EthernetIPforSLCMicro1.ReadAny("B13/17")
        Label19.Text = EthernetIPforSLCMicro1.ReadAny("B13/18")
        Label20.Text = EthernetIPforSLCMicro1.ReadAny("B13/19")

        


    End Sub


    Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick

        If PercentIncreaseF16_0() = True Then

            Dim conn As New Odbc.OdbcConnection
            Dim connStr As String
            connStr = ("server=xxxx; user id=xxxx; password=xxxx; database=xxxx; pooling=false")
            'Declare inside of class >
            Dim SQLStr As String

            'SQL Staments

            'SQL query = myQuery = "SQL Statment"

            SQLStr = "INSERT into xxxxx(id, f16_0, t_stamp) VALUES(' ','" & Label1.Text & "','" & Now() & "')"

            'Write to SQL

            Dim MySQLConn As New MySqlConnection() 'The SQL Connection
            Dim MySQLCmd As New MySqlCommand() 'The SQL Command

            MySQLConn.ConnectionString = connStr 'Set the Connection String
            MySQLConn.Open() 'Open the connection

            MySQLCmd.Connection = MySQLConn 'Sets the Connection to use with the SQL Command
            MySQLCmd.CommandText = SQLStr 'Sets the SQL String
            MySQLCmd.ExecuteNonQuery() 'Executes SQL Commands Non-Querys only

            MySQLConn.Close() 'Close the connection

        End If

        If PercentIncreaseF16_2() = True Then

            Dim conn As New Odbc.OdbcConnection
            Dim connStr As String
            connStr = ("server=xxxxx; user id=xxxxx; password=xxxxx; database=xxxxx; pooling=false")
            'Declare inside of class >
            Dim SQLStr As String

            'SQL Staments

            'SQL query = myQuery = "SQL Statment"

            SQLStr = "INSERT into xxxxx(id, f16_2, t_stamp) VALUES(' ','" & Label2.Text & "','" & Now() & "')"

            'Write to SQL

            Dim MySQLConn As New MySqlConnection() 'The SQL Connection
            Dim MySQLCmd As New MySqlCommand() 'The SQL Command

            MySQLConn.ConnectionString = connStr 'Set the Connection String
            MySQLConn.Open() 'Open the connection

            MySQLCmd.Connection = MySQLConn 'Sets the Connection to use with the SQL Command
            MySQLCmd.CommandText = SQLStr 'Sets the SQL String
            MySQLCmd.ExecuteNonQuery() 'Executes SQL Commands Non-Querys only

            MySQLConn.Close() 'Close the connection

        End If

    End Sub


    Private Function PercentIncreaseF16_0() As Boolean
        Dim calc As Integer
        Dim top As Integer
        Dim bottom As Integer

        top = 3
        bottom = -3

        calc = ((Label1.Text - Label42.Text) / Label42.Text * 100)

        If (calc > bottom) And (calc < top) Then
            Return False
        Else
            Return True
        End If
    End Function

    Private Function PercentIncreaseF16_2() As Boolean
        Dim calc As Integer
        Dim top As Integer
        Dim bottom As Integer

        top = 3
        bottom = -3

        calc = ((Label2.Text - Label41.Text) / Label42.Text * 100)

        If (calc > bottom) And (calc < top) Then
            Return False
        Else
            Return True
        End If
    End Function

    Private Function PercentIncreaseF15_0() As Boolean
        Dim calc As Integer
        Dim top As Integer
        Dim bottom As Integer

        top = 3
        bottom = -3

        calc = ((Label13.Text - Label30.Text) / Label42.Text * 100)

        If (calc > bottom) And (calc < top) Then
            Return False
        Else
            Return True
        End If
    End Function

    Private Function PercentIncreaseF15_1() As Boolean
        Dim calc As Integer
        Dim top As Integer
        Dim bottom As Integer

        top = 3
        bottom = -3

        calc = ((Label14.Text - Label29.Text) / Label42.Text * 100)

        If (calc > bottom) And (calc < top) Then
            Return False
        Else
            Return True
        End If
    End Function

    Private Function PercentIncreaseF15_2() As Boolean
        Dim calc As Integer
        Dim top As Integer
        Dim bottom As Integer

        top = 3
        bottom = -3

        calc = ((Label15.Text - Label28.Text) / Label42.Text * 100)

        If (calc > bottom) And (calc < top) Then
            Return False
        Else
            Return True
        End If
    End Function

    Private Function PercentIncreaseF15_3() As Boolean
        Dim calc As Integer
        Dim top As Integer
        Dim bottom As Integer

        top = 3
        bottom = -3

        calc = ((Label16.Text - Label27.Text) / Label42.Text * 100)

        If (calc > bottom) And (calc < top) Then
            Return False
        Else
            Return True
        End If
    End Function

End Class

So i'm reading the current values into Label objects, then (hopefully) want to read those values into another set of Label's and then run the calculation function you see and then if "function() is true" run sql query?

Does this sound possible or can you suggest a better way to do this?

Thanks

Here's a better way.
There's really only need for one timer for the delay, if you set at high enough interval you also wont get any "cross calling" for the Tick event:

Imports System.Data.SqlClient
Imports System.Data
Imports MySql.Data.MySqlClient

Public Class MainForm
    Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        ReadValues()
        StoreValues()
    End Sub

    Private Sub ReadValues()
        Label1.Text = EthernetIPforSLCMicro1.ReadAny("F16:0")
        Label2.Text = EthernetIPforSLCMicro1.ReadAny("F16:2")
        Label13.Text = EthernetIPforSLCMicro1.ReadAny("F15:0")
        Label14.Text = EthernetIPforSLCMicro1.ReadAny("F15:1")
        Label15.Text = EthernetIPforSLCMicro1.ReadAny("F15:2")
        Label16.Text = EthernetIPforSLCMicro1.ReadAny("F15:3")
        Label17.Text = EthernetIPforSLCMicro1.ReadAny("B13/16")
        Label18.Text = EthernetIPforSLCMicro1.ReadAny("B13/17")
        Label19.Text = EthernetIPforSLCMicro1.ReadAny("B13/18")
        Label20.Text = EthernetIPforSLCMicro1.ReadAny("B13/19")
    End Sub

    Private Sub StoreValues()
        Dim MySQLConn As MySqlConnection = Nothing 'The SQL Connection
        Dim MySQLCmd As MySqlCommand = Nothing 'The SQL Command
        Try
            Dim connStr As String = "server=xxxx; user id=xxxx; password=xxxx; database=xxxx; pooling=false"
            Dim SQLStr As String

            'SQL Staments
            If PercentIncrease(Label1.Text, Label42.Text, Label42.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f16_0, t_stamp) VALUES(' ','" & Label1.Text & "','" & Now() & "')"
            ElseIf PercentIncrease(Label2.Text, Label41.Text, Label42.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f16_2, t_stamp) VALUES(' ','" & Label2.Text & "','" & Now() & "')"
            ElseIf PercentIncrease(Label13.Text, Label30.Text, Label42.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f16_2, t_stamp) VALUES(' ','" & Label13.Text & "','" & Now() & "')"
            ElseIf PercentIncrease(Label14.Text, Label29.Text, Label42.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f16_2, t_stamp) VALUES(' ','" & Label14.Text & "','" & Now() & "')"
            ElseIf PercentIncrease(Label15.Text, Label28.Text, Label42.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f16_2, t_stamp) VALUES(' ','" & Label15.Text & "','" & Now() & "')"
            ElseIf PercentIncrease(Label16.Text, Label27.Text, Label42.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f16_2, t_stamp) VALUES(' ','" & Label16.Text & "','" & Now() & "')"
            End If

            'Write to SQL
            MySQLConn = New MySqlConnection(connStr) 'The SQL Connection
            MySQLCmd = New MySqlCommand(SQLStr, MySQLConn) 'The SQL Command

            MySQLConn.Open() 'Open the connection
            MySQLCmd.ExecuteNonQuery() 'Executes SQL Commands Non-Querys only
            MySQLConn.Close() 'Close the connection
        Catch ex As Exception
            If MySQLConn.State = ConnectionState.Open Then
                MySQLConn.Close()
            End If
        End Try
    End Sub

    Private Function PercentIncrease(ByVal value1 As Integer, ByVal value2 As Integer, ByVal constant As Integer) As Boolean
        Dim calc As Integer
        Dim top As Integer = 3
        Dim bottom As Integer = -3

        calc = ((value1 - value2) / constant * 100)

        If calc > bottom AndAlso calc < top Then
            Return False
        Else
            Return True
        End If
    End Function
End Class

This looks amazing, far better than my attempt! It makes sense to me apart from the PercentIncrease() function, where exactly would i get value 1 & value 2 from? And i also dont understand the "constant" variable, where is that coming from? I understand the principle of the maths but its just the variables i'm scratching my head about. If you could give me a couple of pointer that would be great! Thanks

I do aplogise i do understand where you're getting value 1, value 2 & constant from :) whoops!

But back to my original post, i need to compare say label1.text and label42.text to see if there has been a % change but how do i read a value in label1.text then copy it to label42.text and it not overwrite it (for comparing purposes) so what i'm asking is, i need label1.text and label42.text to be different so the percentincrease() can compare them but they will be coming from the same source , ie. "f16:0"!

Any ideas?

Thanks

I compared all of your multiple methods.
And through them all, there was one constant value in the form of Label42.Text.
I figured that either
a) you made a typo when constructing the formula, or
b) you came up with your own solution
because that would make the formula come up with different answers every time, when compared to the formula I gave you a few days ago.

Also, if you examine the code in the StoreValues method, i call the method PercentIncrease with the various labels as arguments.
That's where value1 and value2 comes from.

Oh.

You can do this.
When you first start the program, immediatly fetch the value from the device and store it in both label1 and Label42.
Then during the timer tick event, perform the comparison, and if the result is as wanted, update the database and copy Label1 to Label42 and wait for the next tick event.

I'm sorry i did sort of make a typo, i copied and pasted all the label1.text and label42.text all the way down to show what i needed.

This is the actual code

'SQL Staments
            If PercentIncrease(Label1.Text, Label42.Text, Label42.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f16_0, t_stamp) VALUES(' ','" & Label1.Text & "','" & Now() & "')"
            ElseIf PercentIncrease(Label2.Text, Label41.Text, Label41.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f16_2, t_stamp) VALUES(' ','" & Label2.Text & "','" & Now() & "')"
            ElseIf PercentIncrease(Label13.Text, Label30.Text, Label30.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f15_0, t_stamp) VALUES(' ','" & Label13.Text & "','" & Now() & "')"
            ElseIf PercentIncrease(Label14.Text, Label29.Text, Label29.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f15_1, t_stamp) VALUES(' ','" & Label14.Text & "','" & Now() & "')"
            ElseIf PercentIncrease(Label15.Text, Label28.Text, Label28.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f15_2, t_stamp) VALUES(' ','" & Label15.Text & "','" & Now() & "')"
            ElseIf PercentIncrease(Label16.Text, Label27.Text, Label27.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f15_3, t_stamp) VALUES(' ','" & Label16.Text & "','" & Now() & "')"
            End If

So what i hope to happen is a value gets read into say label1.text and then copied into label42.text for comparing purposes, then label2.text gets copied into label41.text and so forth down the list...

So i presume i need to add some code into the readValues() to copy the values from the first block to their respective label.text's, if that makes sense??

I'm thinking i need a PercentIncrease() for every value i want to read??

Ok. I didn't quite think that through. :)
Try this instead:

Imports System.Data.SqlClient
Imports System.Data
Imports MySql.Data.MySqlClient

Public Class MainForm
    Private Sub MainForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MainForm.Load
        Timer1.Stop()

        ReadValues()
        Label42.Text = Label1.Text
        Label41.Text = Label2.Text
        Label30.Text = Label3.Text
        Label29.Text = Label4.Text
        Label28.Text = Label5.Text
        Label27.Text = Label6.Text
        ' And so on

        Timer1.Start()
    End Sub

    Private Sub Timer1_Tick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        ReadValues()
        StoreValues()
    End Sub

    Private Sub ReadValues()
        Label1.Text = EthernetIPforSLCMicro1.ReadAny("F16:0")
        Label2.Text = EthernetIPforSLCMicro1.ReadAny("F16:2")
        Label13.Text = EthernetIPforSLCMicro1.ReadAny("F15:0")
        Label14.Text = EthernetIPforSLCMicro1.ReadAny("F15:1")
        Label15.Text = EthernetIPforSLCMicro1.ReadAny("F15:2")
        Label16.Text = EthernetIPforSLCMicro1.ReadAny("F15:3")
        Label17.Text = EthernetIPforSLCMicro1.ReadAny("B13/16")
        Label18.Text = EthernetIPforSLCMicro1.ReadAny("B13/17")
        Label19.Text = EthernetIPforSLCMicro1.ReadAny("B13/18")
        Label20.Text = EthernetIPforSLCMicro1.ReadAny("B13/19")
    End Sub

    Private Sub StoreValues()
        Try
            Dim SQLStr As String

            'SQL Staments
            If PercentIncrease(Label1.Text, Label42.Text, Label42.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f16_0, t_stamp) VALUES(' ','" & Label1.Text & "','" & Now() & "')"
                UpdateDatabase(SQLStr)
                Label42.Text = Label1.Text
            End If
            If PercentIncrease(Label2.Text, Label41.Text, Label41.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f16_2, t_stamp) VALUES(' ','" & Label2.Text & "','" & Now() & "')"
                UpdateDatabase(SQLStr)
                Label41.Text = Label2.Text
            End If
            If PercentIncrease(Label13.Text, Label30.Text, Label30.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f16_2, t_stamp) VALUES(' ','" & Label13.Text & "','" & Now() & "')"
                UpdateDatabase(SQLStr)
                Label30.Text = Label3.Text
            End If
            If PercentIncrease(Label14.Text, Label29.Text, Label29.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f16_2, t_stamp) VALUES(' ','" & Label14.Text & "','" & Now() & "')"
                UpdateDatabase(SQLStr)
                Label29.Text = Label4.Text
            End If
            If PercentIncrease(Label15.Text, Label28.Text, Label28.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f16_2, t_stamp) VALUES(' ','" & Label15.Text & "','" & Now() & "')"
                UpdateDatabase(SQLStr)
                Label28.Text = Label5.Text
            End If
            If PercentIncrease(Label16.Text, Label27.Text, Label27.Text) Then
                'SQL query = myQuery = "SQL Statment"
                SQLStr = "INSERT into xxxxx(id, f16_2, t_stamp) VALUES(' ','" & Label16.Text & "','" & Now() & "')"
                UpdateDatabase(SQLStr)
                Label27.Text = Label6.Text
            End If
        Catch ex As Exception
        End Try
    End Sub

    Private Function PercentIncrease(ByVal value1 As Integer, ByVal value2 As Integer, ByVal constant As Integer) As Boolean
        Dim calc As Integer
        Dim top As Integer = 3
        Dim bottom As Integer = -3

        calc = ((value1 - value2) / constant * 100)

        If calc > bottom AndAlso calc < top Then
            Return False
        Else
            Return True
        End If
    End Function

    Private Sub UpdateDatabase(SQL As String)
        Dim MySQLConn As MySqlConnection = Nothing 'The SQL Connection
        Dim MySQLCmd As MySqlCommand = Nothing 'The SQL Command
        Try
            Dim connStr As String = "server=xxxx; user id=xxxx; password=xxxx; database=xxxx; pooling=false"

            'Write to SQL
            MySQLConn = New MySqlConnection(connStr) 'The SQL Connection
            MySQLCmd = New MySqlCommand(SQL, MySQLConn) 'The SQL Command

            MySQLConn.Open() 'Open the connection
            MySQLCmd.ExecuteNonQuery() 'Executes SQL Commands Non-Querys only
            MySQLConn.Close() 'Close the connection
        Catch ex As Exception
            If MySQLConn.State = ConnectionState.Open Then
                MySQLConn.Close()
            End If
        End Try
    End Sub
End Class

Sounds perfect! We are doing this over an OpenVPN 3G connection to a remote site and its going live in a couple of hours so i can test it then! Thanks so much for your help, i would have still been scratching my head otherwise!

Glad to hear it. :)
If this was helpful, please mark this thread as solved.

OK now i am just being a pain but if i wanted two of the values to be stored at 23:58 every night (to log the total for one day) i have code like this

Dim time1 As Date
            Dim time2 As Date
            time1 = 23:50:00
            time2 = 23:51:00
            If Label43.Text > time1 And Label43.Text < time2 Then

The code i have for Label43.Text is this

Label43.Text = TimeValue(Now)

But it thows up a syntax error on the time1 variables?? Any ideas?

Yeah. I can see that.

Do this instead.

Label43.Text = DateTime.Now.ToString("HH:mm:ss")
Dim time1 As DateTime = DateTime.Parse("1970-01-01 23:50:00")
Dim time2 As DateTime = DateTime.Parse("1970-01-01 23:51:00")

Dim compare As DateTime = DateTime.Parse("1970-01-01 " & Label43.Text)
If compare > time1 AndAlso compare < time1 Then

Once again, thanks!!!

I'll test it and make sure it all works ok and then mark it solved! :)

Good luck!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.