Announcement

Collapse
No announcement yet.

I hit a wall with this

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    I hit a wall with this

    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.

    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
    Attached Files

    #2
    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.

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

    Code:
     
     RefIDParm = parm.ToString()

    Comment


      #3
      Originally posted by alphatech View Post
      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.

      Comment


        #4
        Visual Basic MySQL tutorial

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

        Comment


          #5
          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()
          Attached Files

          Comment


            #6
            Syntax error on this line

            Code:
                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
            Code:
                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
            Code:
                Sub Main(ByVal parm As Object)
            to
            Code:
                Sub Main(ByVal RefIDParam As String)
            and remove where you declare RefIDParam at line 14.

            Comment


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

              Comment


                #8
                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?

                Code:
                     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

                Comment


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

                  Code:
                      Dim myCommand2 As New MySqlCommand
                      myCommand2.Connection = conn1

                  Comment


                    #10
                    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)

                    Comment

                    Working...
                    X