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
Comment