I hit a wall with this

[alphatech]

Not sure if I should open another post or use this one since what I'm trying to accomplish is on the same subject. I hope not to confuse too much. I'm looking for a guidance on how you would approach it. I have a script that I use in an event which I pass few parameters. it then creates a record in a table, push notification via pushnotification and announce via Alex the alert. This new script that all of you are helping with, it trying to get a value from a temperature sensor or another other sensor for this matter, comparing it against last year and if is outside the range (This is my dilemma right now) would then do the same thing as the other script I described, i.e insert record, puschnotification and etc.. Thinking outside the box, I was wondering if there is a cleaner solution than copy everything from the other script into this one .... I'm also concern that I would maximize HS if I open too many connections to the table. (I can read more than 40 sensors a minute)

[zwolfpack]

You only need one connection to the database. You can declare a second command structure

    Dim myCommand2 As New MySqlCommand
    myCommand2.Connection = conn1

[alphatech]

I would like to do an insert to another different table if the max or min values is true. as this example below. What is the proper way to create a different connection for that table?

     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

[alphatech]

Thank you so much guys, very much appreciated the help you have given.

[zwolfpack]

Syntax error on this line

    myCommand1.CommandText = "SELECT DeviceMax, DeviceMin, DeviceAvg, DeviceRef DeviceName, DeviceCategory, DeviceRoom, YYYYMM FROM devicehistory Where DeviceRef ='" &RefIDParm  & [COLOR=#FF0000]'"[/COLOR] and [COLOR=#FF0000]'" & '"[/COLOR] YYYYMM = '" & StrYYYYMM & "' "

Quoting issues. I think you want

    myCommand1.CommandText = "SELECT DeviceMax, DeviceMin, DeviceAvg, DeviceRef DeviceName, DeviceCategory, DeviceRoom, YYYYMM FROM devicehistory Where DeviceRef =" &RefIDParm  & " and  YYYYMM = '" & StrYYYYMM & "'"

As others pointed out, you need to assign RefIDParam before you use it.

change

    Sub Main(ByVal parm As Object)

to

    Sub Main(ByVal RefIDParam As String)

and remove where you declare RefIDParam at line 14.

[alphatech]

This is what I'm getting when I run it from the event

May-08 1:26:43 PM

Error 3

Running script C:\HomeSeer3\scripts\MySQLHistoryDevicesRead.vb :Exception has been thrown by the target of an invocation.->Does entry point Main exist in script? at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Obj ect obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at Scheduler.clsRunVBNetScript.ExecuteScript()

[zwolfpack]

Visual Basic MySQL tutorial

http://zetcode.com/db/mysqlvb/

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.

I might be blind, but I do not see where you have initialized "refidparm". Did I miss it?

And it would be helpful to know how exactly is your script failing.

[AllHailJ]

I am assuming you are calling this from a HS event and not HSTouch. The coding is different for HSTouch. I don't see where you have converted the parameter from an object to an integer or string.

 
 RefIDParm = parm.ToInteger()

or Alternatively if you want a string (Which I think you do here DeviceRef ='" &RefIDParm & '" and )
)

 
 RefIDParm = parm.ToString()

[alphatech]

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.

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