I'm struggling with this for days now, ready to give up :-( I would like to pass a parameter RefID from an event which then check the table for last month value and if is outside the ranges, right now will provide a log. The major stumble are, passing an integer as parm and structuring the select query right.
Below is a simple table that I'm trying to read values from.
Please do not get confused by this code and the garbage is in, I'm trying my best to code it from other scripts.
Below is a simple table that I'm trying to read values from.
Please do not get confused by this code and the garbage is in, I'm trying my best to code it from other scripts.
Code:
Imports MySql.Data.MySqlClient Imports Newtonsoft.Json Imports Newtonsoft.Json.Linq ' *** ' *** Reads the parameter value of a device refid from the event and compare if the device current value is max or min from last year values ' *** The script is called by an event ' *** Sub Main(ByVal parm As Object) Dim conn1 As MySqlConnection Dim myCommand1 As New MySqlCommand Dim Debug=1 Dim strNow, strDD, strMM, strYYYY, strYYYYMM, lastyear, nextmonth, lastmonth Dim Dev_DeviceValue, RefIDParm ' Below calculate last month lastmonth = DateSerial(Year(Now()), Month(Now()),0) lastyear = dateadd("YYYY",0, lastmonth) strYYYY = DatePart("yyyy",lastyear) nextmonth = dateadd("m", 0, lastmonth) strMM = Right("0" & DatePart("m",nextmonth),2) strDD = Right("0" & DatePart("d",lastmonth),2) strYYYYMM = strYYYY & "-" & strMM conn1 = New MySqlConnection() conn1.ConnectionString = "Server=192.168.0.14; port=3306; Database=HomeAutomationDB; User ID=aaaaaa; Password=aaaaaaaa" myCommand1.CommandText = "SELECT DeviceMax, DeviceMin, DeviceAvg, DeviceRef DeviceName, DeviceCategory, DeviceRoom, YYYYMM FROM devicehistory Where DeviceRef ='" &RefIDParm & '" and '" & '" YYYYMM = '" & StrYYYYMM & "' " myCommand1.Connection = conn1 Try conn1.Open() myCommand1.ExecuteNonQuery() Dim myAdapter1 As New MySqlDataAdapter myAdapter1.SelectCommand = myCommand1 Dim myData As MySqlDataReader myData = myCommand1.ExecuteReader() If myData.HasRows Then While myData.Read Dim DeviceMax_read = MyData("DeviceMax") Dim DeviceMin_read = MyData("DeviceMin") Dim DeviceAvg_read = MyData("DeviceAvg") If Dev_DeviceValue > DeviceMax_read then hs.writelog ("mySQLRead", "Value is great than MAX") end if If Dev_DeviceValue < DeviceMin_read then hs.writelog ("mySQLRead", "Value is less than MIN") end if if Dev_DeviceValue < DeviceMax_read and Dev_DeviceValue > DeviceMin_read then hs.writelog ("mySQLRead", "Value is in the limit") end if 'hs.SetDeviceString(2654, DeviceAlert_read, true) 'hs.SetDeviceValueByRef(2654, DeviceRef_Read, true) ' Update the device status with refid so the update script can read it to update table 'hs.SetDeviceString(1894, "No Errors", true) End While else hs.SetDeviceString(2654, "No Record", false) hs.SetDeviceValueByRef(2654, 0, false) ' Update the device status with refid so the update script can read it to update table end if conn1.Close() conn1.Dispose() Catch myerror As MySqlException hs.writelog ("MySQLRead", "Error Connecting to Database automation: " & myerror.Message) hs.SetDeviceString(1894, myerror.Message, true) conn1.Close() conn1.Dispose() End Try '//////////////- BEGIN DEBUG //////////////////////- conn1.Close() conn1.Dispose() End Sub
Comment