No announcement yet.

SQL Server Script - Please help understanding it

  • Filter
  • Time
  • Show
Clear All
new posts

    SQL Server Script - Please help understanding it

    I found this script in one of the posts that I would like to implement on my system. I'm really bad in scripting, I can make small modifications and I'm able to read them but unfortunately not this one.
    My confusions are as follow:
    It looks like it would read from Parms but from my understanding
    1)it runs from start.vb and I do not see the author passing the parameters to it.
    2)I do not see a sequence in the parms as of parm1, parm2 and parm3, it is skipping parm3
    3) can I run it from an event instead of within the startup.vb and if is yes, how do I define the parms.
    4) it looks like it is missing a field in the sql statement but I think I have it figured out. Right now, I get an error when I run it from the event, not sure what this means
    Apr-24 8:43:04 AM Error 1 Running script C:\HomeSeer3\scripts\L2DB.vb :Exception has been thrown by the target of an invocation.Object variable or With block variable not set.
    Any help would be greatly appreciated.


    'VB.Net script to write Homeseer 3 values to SQL Server Express

    'Add this line to Homeseer\Config\Settings.ini
    ' ScriptingReferences=System.Data;System.Data.Dll

    ' Add this line to Homeseer\Scipts\Startup.ini
    ' hs.RegisterStatusChangeCB("L2DB.vb","Main")

    Imports System.Data.SqlClient

    Sub Main(ByVal Parms As Object)

    Dim device_name, device_id, dv, device_location, device_location2, deviceObj, device_type
    Dim hc, dc, dev_address, dn As String
    Dim device_value As Double
    Dim devRef As Integer
    Dim sdate, stime As String
    Dim cn As New SqlConnection("Server=,1433;Database=Home;User ID=xx;Password=xxxxxx;Trusted_Connection=False;")

    hs.WriteLog("L2DB", "Script running")
    'hdc = Parms(0) 'address of device
    dev_address = Parms(1) 'address of device
    device_value = Parms(2) 'new value of device
    devRef = Parms(4) 'Device reference of the device

    'Device_id = hs.GetDeviceRef(hdc)
    deviceObj = hs.GetDeviceByRef(devRef)

    device_name =
    device_location = deviceObj.location(hs)
    device_location2 = deviceObj.location2(hs)
    device_type = deviceObj.Device_Type_String(hs)
    'device_status = hs.DeviceStatus(devRef)
    sdate = format(Now, "dd-MM-yyyy")
    stime = format(Now, "HH:mm:ss")

    hs.WriteLog("L2DB", "Connection is open")
    Dim SQLComm As New SqlCommand("UPDATE devices SET date ='" & sdate & "',time='" & stime & "',type='" & device_type & "',name='" & device_name & "',value='" & device_value & "',location='" & device_location & "',location2='" & device_location2 & "' WHERE code='" & dev_address & "' " & _
    "IF @@ROWCOUNT=0 " & _
    "INSERT INTO devices(date,time,code,type,status,name,value,location,locat ion2) VALUES('" & sdate & "','" & stime & "','" & dev_address & "','" & device_type & "','" & device_name & "','" & device_value & "','" & device_location & "','" & device_location2 & "')", cn)
    hs.WriteLog("L2DB", "Logged to Database: Address: " & dev_address & ", Device Name: " & device_name & ", New Value: " & device_value)
    hs.WriteLog("L2DB", "Connection is closed")
    End Sub

    This script didn't work for me initially. I had to do some modifications. I can post my script and you can try if you like. I'm not that great at scripts either, but I get by.

    Do you have the DB setup to receive the data already?


      Thanks, that would be great, I tried everything I could. I have SQL already installed, if you could print the table structure that would be fantastic.

      Sent from my SM-G935V using Tapatalk


        I implemented this script long time ago with help of this group. I would like to exclude selective devices to be appended in the database. As I mentioned, i can hack a script but not sure how I start with it. I was thinking along the line of using a Case statement, what is your opinion? If agreed, where would you include it in the script? Any help would be appreciated.



          mcsMQTT write any HS device value change to SQL Server (mySQL or InfluxDB). The user interface being a table of HS devices where a checkbox is used to select it for inclusion. Seems like an easier approach than mucking with a script.


            Thank you Michael.

            Sent from my SM-S906U using Tapatalk