Device value to SQL Server

[Romac]

I'm trying to convert my HS2 script that writes the device value to a SQL Server, to HS3. In HS2 this script was working well, but I keep getting an error I cannot get rid off.

The script is as follows:

'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,location2) 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

I keep getting the following error in the log:


It looks to be related to the "SQLComm.ExecuteNonQuery()" command. Who does understand what is happening here?

[rmasonjr]

Your INSERT statement is missing the 'status' value. I think you will also need a semicolon terminator after the UPDATE command.

To get better error checking, wrap the SQL calls in a try/catch and spit the exception out to the loog...

[Romac]

Your INSERT statement is missing the 'status' value. I think you will also need a semicolon terminator after the UPDATE command.

To get better error checking, wrap the SQL calls in a try/catch and spit the exception out to the loog...

Thanks! I should have picked up on the "status" in the INSERT command.... I also found an error in a database field type. Now some devices are coming in, not all yet, but this is going in the right direction.

[travisdh]

Good to hear it is working .
I use MySQL to log data on HS2 and HS3 and find for the most part it has been working quite well.

I would also recommend try and catch blocks to get a better idea of what the error(s) are as this can be far more helpful (in my view) to troubleshooting.