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.
Aldo
'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=192.168.2.25,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 = deviceObj.name(hs)
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")
cn.Open()
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)
SQLComm.ExecuteNonQuery()
hs.WriteLog("L2DB", "Logged to Database: Address: " & dev_address & ", Device Name: " & device_name & ", New Value: " & device_value)
cn.Close()
hs.WriteLog("L2DB", "Connection is closed")
End Sub
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.
Aldo
'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=192.168.2.25,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 = deviceObj.name(hs)
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")
cn.Open()
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)
SQLComm.ExecuteNonQuery()
hs.WriteLog("L2DB", "Logged to Database: Address: " & dev_address & ", Device Name: " & device_name & ", New Value: " & device_value)
cn.Close()
hs.WriteLog("L2DB", "Connection is closed")
End Sub
Comment