Announcement

Collapse
No announcement yet.

Insert into a table if DeviceRef does not already exist

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

    Insert into a table if DeviceRef does not already exist

    I have a table which has a device refid. I would like to insert new rows if they do not exist in the table. I'm googleing it for hours but I can not find the answer to it, what am I doing wrong?


    Code:
    '  *** Script goes thru all the Devices in HomeSeer and insert a record in DeviceRefTable 
    
    
    Imports MySql.Data.MySqlClient
    
    Sub Main(ByVal Parm As String)
        Const logName As String = "mySQLUpdate"
    
        Try
            ' open mySQL database
            Dim conn1 As MySqlConnection = New MySqlConnection()
            conn1.ConnectionString = "Server=192.168.0.14; port=3306; Database=homeautomationdb; User ID=xxxxxxxxx; Password=xxxxxxxx"
            conn1.Open()
    
            Dim myCommand1 As New MySqlCommand
            myCommand1.Connection = conn1
    
            ' get the device list from HomeSeer
            Dim dv As Scheduler.Classes.DeviceClass
            Dim EN As Scheduler.Classes.clsDeviceEnumeration
            EN = hs.GetDeviceEnumerator
            hs.WriteLog(logName, "Starting.")
            ' iterate thru the list
            Do
                dv = EN.GetNext
                If dv Is Nothing Then Continue Do
                Dim dvRoom As String = dv.Location(Nothing)
                Dim dvCategory As String = dv.Location2(Nothing)
                Dim dvName As String = dv.Name(Nothing)
                Dim dvRef As Integer = dv.Ref(Nothing)
    
            myCommand1.CommandText = "Insert into devicereftabletest(DeviceRefId, DeviceName, DeviceRoom, DeviceCategory) Select '" & DvRef & "','" & DvName & "','" & DvRoom & "','" & DvCategory & "' from devicereftabletest where not exists (select devicerefid from devicereftabletest where DeviceRefId =  '" & dvRef & "') "   
    
    
                myCommand1.ExecuteNonQuery()
    
            Loop Until EN.Finished
            hs.WriteLog(logName, "Finished.")
    
            conn1.Close()
            conn1.Dispose()    
    
        Catch ex As Exception : hs.WriteLogEx(logName, "Exception: " & ex.Message, "#FF0000")
        End Try
    
    End Sub

    #2
    For others with similar issue, issue resolved. I removed "from devicereftabletest" I guess by removing the from table is like having a "DUAL" temp table. See below for the correct query

    Code:
     
     myCommand1.CommandText = "Insert into devicereftabletest(DeviceRefId, DeviceName, DeviceRoom, DeviceCategory) Select '" & DvRef & "','" & DvName & "','" & DvRoom & "','" & DvCategory & "' where not exists (select devicerefid from devicereftabletest where DeviceRefId =  '" & dvRef & "') "

    Comment

    Working...
    X