Announcement

Collapse
No announcement yet.

Device value to SQL Server

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

  • Device value to SQL Server

    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:
    Code:
    '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?
    Attached Files
    Last edited by Romac; March 3rd, 2014, 03:42 PM.

  • #2
    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...
    HS3Pro Running on a Raspberry Pi3
    64 Z-Wave Nodes, 168 Events, 280 Devices
    UPB modules via OMNI plugin/panel
    Plugins: Z-Wave, BLRF, OMNI, HSTouch, weatherXML, EasyTrigger
    HSTouch Clients: 3 Android, 1 Joggler

    Comment


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

      Comment


      • #4
        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.
        HS3 PRO, Win10, WeatherXML, HSTouch, Pushover, UltraGCIR, Heaps of Jon00 Plugins, Just sold and about to move so very slim system.

        Facebook | Twitter | Flickr | Google+ | Website | YouTube

        Comment

        Working...
        X