Archive for August, 2008

Insteon’s SDM COM object is great for having a preset commands at your disposal but ive ran into issues where I dont want the program to wait for the ACK.  (In this case it will pause for the response), this can be a problem in very large environment, especially if your poll all devices at a certain time.  In my case I like to poll all the devices at least twice a day.

If this is the first article you ready please check out the articles below on how to get started.

Software: Get Insteon / Talking to your PC – Part 1

Software: Get Insteon / Talking to your PC – Part 2

For example

The SDM quick command is sm.GetOnLevelText “11.22.BB”

We will be using the InsteonRaw command Hex #19 like this
sm.SendINSTEONRaw(”00 00 00 11 22 BB 05
19 00″, 3)

Here is a debug of what is sent and what is received. I am polling device (0A.FE.3E) from my PLC (0D.51.32)

8/24/2008 11:38:32 AM sentinsteon=0D 51 32 0A FE 3E 05 19 00
8/24/2008 11:38:33 AM receiveinsteonraw=04 0A FE 3E 0D 51 32 21 00 00

Insteon responds in the same but with the device being queried first

0A FE 3E= Device responding.

OD 51 31 = My PLC, the desitation receiving the response

21 = Is a incremental # which can change among #21-2B, so you have to look for it.

00 = Some type of marker – Changes also

00 = Our value in hex, from 00= OFF to FF = Full all and everything in between

Here is another debug trace of a device query which is on;

8/24/2008 11:43:33 AM sentinsteon=0D 51 32 08 6B 57 05 19 00 (My requesting of status of the device calling command #19)
8/24/2008 11:43:34 AM receiveinsteonraw=04 08 6B 57 0D 51 32 26 1A FF (This one is reporting back as ON!)

Note: What is very tricky of this is that don’t always thing FF is on, I some icon dimmers that on can be ‘FE’, as a rule of thumb anything NOT 00 is consider for me as ‘ON’

Again all these codes are show in “Software: Get Insteon / Talking to your PC – Part 2“. I’ve copied the code from part #1 which will allow us to act upon the events of the SDM when text arrives back

'------- First we setup the SDM object in our code
Class  MainMenu
Friend WithEvents Sm As SDM3Server.SDM3

'-- When I load my mainmenu even I have the following

Public Sub MainMenu_Load(...... )
  Sm =New SDM3Server.SDM3
Try
      Sm.IsResponding()
    Catch
    MsgBox("SDM NOT LOADED try AGAIN")
 End Try
End Sub

Here is the TEXT event that I use for the whole program to break down and read the strings returned to me.

Public Sub sm_OnText(ByVal strInsteonStatus As String) Handles Sm.OnText

var = Split(strInsteonStatus, "=")
Select Case LCase(var(0))

	Case "setonleveltext"
		data = Split(var(1), ",")
		device = data(0)
		value = data(1)

	Case "receiveinsteonraw"
		data = var(1)
		bytes = Split(data," ")
		If LBound(bytes) = 0 And UBound(bytes) = 9 Then
			AddrFrom = bytes(1) & bytes(2) & bytes(3)
			AddrTo = bytes(4) & bytes(5) & bytes(6)
			Flags = bytes(7)
			Command1 = bytes(8)
			Command2 = bytes(9)
			End If

     '------------ GRAB RESPONSES FROM QUERYES (#19) RETURNING
                    If flags = "21" or flags = "24" Or flags = "25" Or flags = "26" Or flags = "2B" Or flags = "22" Or flags = "23" Then
                        If Cmd2 <> "00" Then Msgbox "DEVICE IS ON!!"
                        If Cmd2  = "00" Then Msgbox "Device is OFF!"
                    End If
		End If
	Case Else

End Select

For email coming in and out, I use MS outlook, which makes interfacing with VB very easy.  But if you have configured the outlook client not using exchange, you know that you must specify the time in minutes it will poll to check for email.  Lets say you want to force it to check for new-email at your will.  Well this is how its done, but for this to work you must have the outlook client open.

If you havent please jump this article which shows how to setup your references and other goodies prior to this.

Software:Controlling Insteon devices using an email account

Back here so soon, great!

What are are doing is calling the Microsoft core command bar of the application itself and ‘press’ the button we want to execute, in this case

Were creating instances of the Controls, Tools – > Send/Receive and the account we want to poll. And I don’t have to wait for the application to hit its internal timer.
Sure you can let the timer run but in my case I have my MX records pointing home and host my own pop3 server, so once I get email I notify my server program (In another article soon) to force a scan of my email and act accordingly.  This makes the response to my emails lighting fast.!

So here’s the code,

Function ForceEmailcheck() as boolean

        '------------ Set up the Variables / Objects
        Dim oCtl As Microsoft.Office.Core.CommandBarControl
        Dim oPop As Microsoft.Office.Core.CommandBarPopup
        Dim oCB As Microsoft.Office.Core.CommandBar

        'Use the Send/Receive on All Accounts action in the Tools
        'menu to send the items from the Outbox, and receive new items for my specific account
        oCB = objOutlook.ActiveExplorer.CommandBars("Menu Bar")
        oPop = oCB.Controls("Tools")
        oPop = oPop.Controls("Send/Receive")
        oCtl = oPop.Controls.Item(6)   '----- Please see below why this is a #6 (*)
        oCtl.Execute()  

        '------ return something but not needed to work
        ForceEmailCheck = True
    End Function

* Since your this calls the name of the account itself for example (1.mail.xxxxxx.com) you will need to use that name for this to work, so I ended up using the item # and its easier.
The way to find this out is after you have set up your outlook, copy the above code in your VB and

set a break point at "oPop = oPop.Controls("Send/Receive")"

Then from your ‘Immediate window’ in VB do a query of the item of that control to see where your ‘send/receive’ for your specific account is, (Most likely #6 also)

?oPop.Controls.Item(1),
?oPop.Controls.Item(2)
?oPop.Controls.Item(3)
?oPop.Controls.Item(4)... etc...

Until you find the item you wish to ‘press’ and then insert the # above.    That is how I found the item I was looking for was #6.

And your Set!!

Luis,

As previously mentioned my main program depends 100% off a SQL database, below is a dump of my “Configuration” database, that holds all the system settings.
Many are loaded into memory the first time, or when I run PollDevices query this way the information is updated when needed.  I know its a lot of information but maybe a developer can use these fields as ideas for their program.

Below are my two main functions I’ve created call (get a value or update a value).

Grabbing a value from the “Config” Database

For example this function will return the value of a specific query for example

lg_get_config("wake_up_time")

 Public Function Lg_Get_config(ByVal field As String) As String

       '------ SETUP CONNECTION TO DATABASE (CONNECTION STRING MAY VARY AMONG SYSTEM OR WHERE you have DB)
        Dim sqlConnection35 As New System.Data.SqlClient.SqlConnection(My.Settings.InsteonConnectionString)
        Dim cmd35 As New System.Data.SqlClient.SqlCommand

        '------ SETUP READER TO GRAB THE NECESSARY DATA
        Dim Reader As SqlClient.SqlDataReader
        cmd35.CommandText = "SELECT * FROM Config where config= '" + field.ToLower.Trim + "'"
        cmd35.Connection = sqlConnection35
        sqlConnection35.Open()

'------ OPEN CONNECTION AND CHECK TO MAKE SURE
        Do While sqlConnection35.State <> ConnectionState.Open
        Loop

        Reader = cmd35.ExecuteReader()

        ' --------- check for data, if not return 'FALSE' AND leave function
        If Reader.HasRows = False Then
            Lg_Get_config = False
            Exit Function
        End If
        Reader.Read()

'---------- return the value and close all connections and exit.
        Lg_Get_config = Reader.Item("value")
        Reader.Close()
        sqlConnection35.Close()

    End Function

Setting a value in the “Config” Database

The next example updates a field, and is very similar just that it uses the SQL update command

For example lets say I want to update the “wake_up_time” to something else

lg_set_config("wake_up_time","9:00:00 AM")

    Public Function Lg_Set_config(ByVal field As String, ByVal value As String) As Boolean

        '------------- SETUP CONNECTION
        Dim sqlConnection4 As New System.Data.SqlClient.SqlConnection(My.Settings.InsteonConnectionString)
        Dim cmd4 As New System.Data.SqlClient.SqlCommand
        cmd4.CommandType = System.Data.CommandType.Text
        cmd4.Connection = sqlConnection4

        '------ The Update Command to modify the field
        cmd4.CommandText = "UPDATE Config SET Value= '" + value + "' WHERE config='" + field.Trim.ToLower + "'"
        sqlConnection4.Open()

        '--------- Since we are not getting anything in return this is a NonQuery Executre
        cmd4.ExecuteNonQuery()
        Lg_Set_config = True
        sqlConnection4.Close()
        Update_TimeStamp()  ' - I update the TimeStamp Value so anytime its queried they know is been updated.

    End Function

Data Dictionary The “Config” Database

marker – Int field
config – nvarchar(128)
value – nvarchar(255)
description – nvarchar(255)

* marker field is my primary key, this is also set to “Identity Specification” which means it will autopopulate with a incremental value, so no record is the same, example (1,2,3,4,5,6 etc)

Below is a dump of all my fields and a quick explanation of what they represent.  Descriptions starting with RST are the ones that are updated by the system automatically. For example if I turn on the outside lights, I update the field at that moment to say it has been turned on.

outdoor_turn_off_time 10:32 PM When will all outdoor lights will turn off

outdoor_turnedon Y RST, If outdoor lights are on?
extractor_girls N RST, If the extractor is on or off
extractor_girls_time 10 In Minutes, Timeout for the girls extractor
timer_randomlights N If the random lights are on
random_lights_min 10 Random lights timer in minutes
time_after_sunset_to_turn_on 12 Time in minutes after sunset the lights outside will turn on
temperature 75% RST, Current temperature
temperature_last_read 6:40:29 AM RST, When was the temperature last read
temperature_text Mostly Cloudy RST, Text from NOAA.GOV
temperature_image http://rssweather.cachefly.net/images/weather-symbols/mcloudyn.png RST, Local image
SunRise 6:59:52 AM RST, SunRise in time
SunSet 7:56:07 PM RST, SunSet in Time
Outdoor_turn_on_time 8:08:07 PM RST from program, Outdoor turn on time
fan_off_low 15 Turn off Fan Threshold
fan_on_high 88 Turn on fan Threshold
voicemails N RST,If Voicemails are present?
master_closet_timer 3 Master Closet Timer in Minutes
master_closet N RST,Master Closet is on?
alarm_clock_status ON Mine, Turn on Alarm clock, M-F
poll_devices_interval 525 Minutes to poll each device
poll_devices_next_poll 8/23/2008 15:28 RST, Next time to poll
master_closet_turn_off_time 8/22/2008 22:39 RST, Master Closet
extractor_girls_turn_off_time 8/22/2008 21:46 RST,Girls Turn off Extractor time
Entrance_turnedon N RST, Is entrance on?
entrace_turn_on_time 8:01:07 PM RST, What time to turn entrance on
time_after_sunset_entrance_turn_on 5 Time to turn on Entrace after SunSet in Minutes
house_state NIGHT House State (E,O,I)
wake_up_time 6:15:00 AM Master Wake up time, M-F only
time_after_sunrise_to_turn_off 10 Time after SunRise to turn off Nook (Sat and sun Only)
incomming_call N CallerID Found? – No Longer Used
incomming_stamp 8/22/2008 17:29 RST, TimeStamp to clear call (No longer used)
incomming_name Luis Garcia RST, Caller ID
incomming_image luis.png RST, Imaged pulled from Contacts DB
incomming_date 8/22/2008 17:29 RST, Incomming Time
incomming_number xxxxxxxxxx RST, Incomming Number
entrance_turn_off_pre -5 Time in minutes before turn off time to turn off the entrance only
entrance_turn_off_pre_time 10:27:00 PM From program result of entracne_Trun_off_pre
motion_hallway N Motion is detected in hallway
extractor_master N If the extractor is on or off
extractor_master_time 10 Timeout for Extrator Master
extractor_master_turn_off_time 8/23/2008 6:18 RST,Master Turn off Extractor time
sensor_hallway N IF Hallway Sensor is on
sensor_hallway_time 6 Timeout Hallway
sensor_hallway_turn_off_time 8/23/2008 6:14 Hallway Timeout
sensor_hallway_bypass N Set to N if normal and Y to ignoreTimer
sensor_entrance_last_seen 8/23/2008 2:48 RST, Last time movement was detected
sensor_entrance_rescan_timeout 3 Minutes before the entrance sends out another signal
sensor_entrance_bypass Y If sensor entrance is bypassed (Y)=dont check (N)=yes check
sensor_master_closet_bypass N If sensor master is bypassed (Y)=dont check (N)=yes check
sensor_hallway_last_seen 8/23/2008 6:08 RST, Last time movement was detected
sensor_master_bathroom_last_seen 8/23/2008 6:09 RST,Last time movement was detected in master batrhoom
sensor_den_last_seen 8/22/2008 23:05 RST,Last time movement was detected in DEN
sensor_garage_last_seen 8/22/2008 20:59 RST,Last time movement was detected in Garage
sensor_hallway_start_time 6:00:00 PM Time that the Sensor will kick in. Usually set 3pm since its day time it wont turn on
sensor_garage_door_state CLOSED Garage door if open
temperature_last_read_wdw 6:40:29 AM Last time temperature was read for WDW
sensor_outside_bathroom_last_seen 8/23/2008 0:25 RST,Last time movement was detected in master batrhoom
sensor_outside_bathroom_timeout 25 Outside bathroom timeout
sensor_outsidebathroom_enabled Y RST, Enabled or not?
music_playing_status N RST, Is Radio Music Playing
sensor_garage_door_state_last 8/22/2008 20:58 RST, Last time Garage was open or closed
sensor_garage_door_timer 6/23/2008 20:48 RST, Time is has been opened
decorations_status OFF Status of all decorations
sensor_den_timer 8/22/2008 23:10 RST, Time the Den will turn off it no movement
sensor_den_timer_timeout 5 Minutes, Den will turn off after no activity
sensor_master_closet_last_seen 8/22/2008 22:36 RST, MasterCloset Last Seen
decorations_on 7:58:07 PM Time Decorations turn on
decorations_off 10:45:00 PM Time Decorations turn off
decorations_after_sunset_on 2 Decorations turn on after sunset
speakers_status OFF RST, if speakers are on
coffee_machine_status TRUE CoffeeMachine timer if on or OFF
coffee_machine_timer 6:20:00 AM CoffeeMachine OnTime
sensor_laundry_last_seen 8/22/2008 21:30 RSt, LAST seen activity in Laundry
sensor_laundry_timeout 3 Minutes before laundry turns off
sensor_laundry_timer 8/22/2008 21:33 RST, When will it turn off
kelly_alarm_on 6:15:00 AM Kelly On_Time
kelly_alarm_status ON IF Kellys alarm is on or off
barbie_alarm_on 7:01:00 AM Barbie OnTime
barbie_alarm_status ON Barbie on-time
back_house_lights_on 9:00:00 PM When will the backlights turn on
back_house_lights_off 9:30:00 PM When will the backlights turn off
guilda_alarm_on 6:15:00 AM Guildas Light will turn on
guilda_alarm_status ON Guildas Alarm status
kelly_alarm_song S:\Mp3\Hanna Montana6 – I Got Nerve.mp3 Kelly AlarmSong
barbie_alarm_song S:\Mp3\Abba\(1994) Thank You For The Music\Disc 1\21 – Abba – My Love, My Life.mp3 Kelly AlarmSong
kelly_artist_songs1 hanna mon Kelly selected artist for Alarm
kelly_artist_songs2 vanessa Kelly selected artist for Alarm
kelly_artist_songs3 ashley tins Kelly selected artist for Alarm
barbie_artist_songs1 cascada Barbie Selected artist for Alarm
barbie_artist_songs2 Abba Barbie Selected artist for Alarm
barbie_artist_songs3 Rick Springfield Barbie Selected artist for Alarm
kelly_alarm_song_title Hanna Montana – I Got Nerve Last song Played
barbie_alarm_song_title ABBA – My Love, My Life Last song Played

One of the questions ive been asked is how to I put my flash script to grab data from the SQL database and provide updates if the device is on or off etc.

Well the awnser is, I dont. Like mentioned before to have your flash talk directly to a sql database you will need additional components which I dont want to install $$$, such as cold fusion or some advanced Flash stuff.  Again I want speed and functionality, especially if its only for a few users.

So what I did was use a internal web server which is installed with VB 2008, to do all the talking for me, more like a middle man.  for example my flash program talks to this web page using the ‘load’ function which sends data using the POST commands (example http://192.168.0.101/finddata.aspx&IN=14) and the code runs and returns the values needed in a specific variable in this case ‘VALUE’. you can also use the LoadVariables command which explain that in this article.

Flash:Loading Variables from files into Flash

So here is a high level of what is being done in the background.

All all times the Insteon Server is updating the SQL Server with the status of the devices as they are turned on or off, so we dont need to connect there. Also note that the Local .net, SQL database and insteon server are all on the same box!

For example lets say I want to pull the status of device #14. (Fan in master bedroom)  This is what I would do from the Flash Program

Example in FLASH CLIENT;

Prepare: In Flash you will need to create a empty movie clip called ‘fan_master’.  Also have images already in your webpage in the location mentioned below. I call it right of the same page you can use your local library if you like.

Here are my images I use in my Flash program,  you may use them if you like (Just right click on them and use SAVE AS)

Light_bulb_off.png

Light_bulb_on.png

Off Button (small_button_on.png)

On Button (small_button_off.png)

In my actionScript, I would put on one of the frames;

//--------------------- SETUP A NEW VARIABLE TO GRAB MY DATA THIS FUNCTION WILL RUN WHEN THE BELOW SITE IS OPENED
var mfan:LoadVars = new LoadVars();
    mfan.onLoad = function() {

//------ Here I check the return value that is returned back as 'value' if its is ON then replace the fan_master 'movie' to the bulb on image
          if (mfan.value=="ON"){
            loadMovie("http://192.168.0.101/images/small_button_on.png", fan_master);
        } else {
            loadMovie("http://192.168.0.101/images/small_button_off.png", fan_master);
        }
    };
//---------------- do the actual opening of the webpage and pass the variable IN and the device im asking for
mfan.load("http://192.168.0.101/finddata.aspx?in=14");
stop();

BLUE BOX (SERVER .NET SIDE)

A description of my Devices Database is found here

‘————————— VB 2008 / WEBPAGE filename finddata.aspx

On my Server side I would grab the value on “IN” using the

 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
      '------- Device will hold the value of in 'IN' which is #14
      device = Context.Request.Params("IN")
      value = Lg_Get_Device_Status(device)
      Response.Write("value=" + value)
 End Sub

This will return to my program the following text
“value=ON” or “value=OFF”

‘——– Function to grab the device data by the device #

    Public Function Lg_Get_Device_Status(ByVal Marker As String) As String

        Dim sqlConnection10 As New System.Data.SqlClient.SqlConnection(ConnectionSTring) ' Set your connectionstring accordingly.
        Dim Cmd10 As New System.Data.SqlClient.SqlCommand

        '-------- We'll use the SqlDataReader to get the values of the table 
        Dim Reader As SqlClient.SqlDataReader

        Cmd10.CommandText = "SELECT * FROM Devices where Marker= '" + Marker + "'"
        Cmd10.Connection = sqlConnection10
        sqlConnection10.Open()
        Reader = Cmd10.ExecuteReader()

        ' check for data, if not return 'FALSE'
        If Reader.HasRows = False Then
            Lg_Get_Device_Status = False
            Exit Function
        End If
        Reader.Read()
        '-------- RETURN THE VALUE FROM THE DATABASE SUCH AS ON OR OFF OF %
        Lg_Get_Device_Status = Reader.Item("status")
        sqlConnection10.Close()

    End Function

And there you have it, I do use the same file (finddata.aspx) for other stuff and you can easily query what is being passed and eventually act on it like this,

For example if I pass(showall=1) it would run another function, but that is another article.

        '------------------- SHOW ALL DEVICES
        If Context.Request.Params("showall") <> "" Then Show_insteon_devices() : Exit Sub

        '------------------- SHOW ALL DEVICES by MAC address
        If Context.Request.Params("showmacs") <> "" Then Show_insteon_devices_mac() : Exit Sub

        '------------------- SHOW ALL CONFIGS
        If Context.Request.Params("show_all_configs") <> "" Then Show_all_configs() : Exit Sub

Here’s a little thing we recently got, its a night light / flashlight.  Best part Flash light lights up when the power goes out also a big plus is the device uses LED’s.

Since its LED technology, the little sucker will run upto 8 hours ON, and up to 28 hours on Standby. And even water resistant.  I got this off smarthome for $ 24 and worth ever dollar.

Here are some more images of mine.

Yep, its over, sadly Smart-home has decided to close their auction site?.  They claim they weren’t giving the correct buying experience.

I guess it was that they noticed they could be making more $$ for the devices via other outlets, or maybe resellers not being able to compete?

Many of their auctions started at $14. And in my case I never paid more for a Icon devices $20. (OK I admit maybe once or twice).  But now its either off their site which start at $34 and in Fee-Bay you have to keep your eye open Ive got my eye on one, I guess I will pay around $25 plus shipping, and you know how that is you don’t get the warranty as right off the site 😦

Oh well at least I was able to milk the cow for a couple of months and 90% of the devices I have.  Really what i am missing is two night lights switch (Icon’s) , (3)-inline fan relays (on/off only) and a regular switch for the laundry room since everyone was the Cat faceplate so im stuck paying that one at full price anyway.    So im not that far back.

It was good while it lasted…

so long my friend.. so long….

The “Devices” data dictionary

I’m always talking about the database this, database that,

So here is the layout of what I have.  This main database ‘Devices table’ that holds all the mac address of each device I have in my home. This makes easy to call devices by number and not by mac.

As mentioned before its a SQL database, you can use what ever you want, This is included in the free version of VB 2008 express. I recommend you download it.

Here is what each field means,

Marker=  is setup as a sequential number which is automatically set so it increments automatically, this is the # of my device, since there can only be one.

Name= The name of device, for example I try to use a standard name, this makes it easier if there is more than one device in a room, for example “Kitchen MainLamp” or “Kitchen Sink lamp”

Mac = The Insteon address, including the dots, for example “01.41.5F” all in caps

Type = “L” for lamp, “F” for fixed I dont know why but I dont use this yet.

Protocol= “I” for Insteon or “X” for X10

Dimmable=”True or False”

Status = Current status of device, if its ON, or OFF, and even % of device such as 50%

Poll = True or false, when I want to check the status of all devices, setting this to True will include it.  For example I don’t want to poll a device which isn’t on line since it will eventually retry 3 times and time out the others.  This is specifically for Christmas decorations.

Last_seen = Last time any change was done to the device, on,off, etc.

randompoll = Sometimes some devices can get pesky so I poll a device every couple of minutes, but only one devices, that is where setting this to True means it will include this on the mix

That’s it, that’s my ‘devices’ database… once they are loaded into memory I reference them by number!

Loading the Insteon Database into Memory

In my code I reference them by using VB structures like this, below is the definition which is defined as public so its available to everyone.

   '----------------------------------- maint structure for insteon devices
    Public Structure Insteon_Device
        Dim MAC As String
        Dim MacSP As String
        Dim MacNP As String
        Dim Name As String
        Dim Type As String
        Dim Protocol As String
        Dim Dimmable As String
    End Structure

Then I call it as a type of variable like this;

'---- I set this to 40 to make space for more. This is set way on top of the program as public so its accessible in all the program.
Public InD(40) As Insteon_Device

This allows me to call the variables like this

debug.print (Ind(1).Mac) ' This will give me the mac of the device by #.. Neat!

Since i come from VB6 Im used to the old classic SQL connection definitions, like all my code there is many ways of doing this so yes you can use the table-adapters and other .NET goodies since I only want Raw power not any fancy tables or colors, I got with my trusty code.

Here is how I load them into the variables itself calling my public function.

Public Function Load_Insteon_devices()
        Dim marker As Integer
        Dim TotalDevices As Integer

        '----------------- SETUP THE SQL CONNECTION AND DATA-READER COMPONENTS
        Dim sqlConnection2 As New System.Data.SqlClient.SqlConnection(My.Settings.InsteonConnectionString)
        Dim cmd2 As New System.Data.SqlClient.SqlCommand
        Dim counter As Integer
        Dim Reader As SqlClient.SqlDataReader

        '---------------- FIRST QUERY CHECKS THE AMOUNT OF DEVICES I HAVE
        cmd2.CommandType = System.Data.CommandType.Text
        cmd2.Connection = sqlConnection2
        cmd2.CommandText = "SELECT COUNT(*) AS CNT FROM Devices"

        sqlConnection2.Open() : Reader = cmd2.ExecuteReader() : Reader.Read()
        TotalDevices = Reader.Item("CNT") '---- TotalDevices will grab to # of devices I have :)
        sqlConnection2.Close()

        '-------------------- NOW THAT I GOT THE AMOUNT GO AHEAD AND READ ALL THE FIELDS
        cmd2.CommandText = "SELECT * FROM Devices"
        sqlConnection2.Open() : Reader = cmd2.ExecuteReader() : Reader.Read()

        '------------------ SCROLL THRU THE DEVICES AND UPDATED EACH STRUCTURE PROPERTY 
        For marker = 1 To TotalDevices
            counter = Reader.Item("Marker")
            InD(counter).MAC = Reader.Item("MAC")

            '-------------- THE VARIABLES MACSP IS THE SAME AS THE MAC ONLY REPLACING THE DOTS FOR SPACES
            InD(counter).MacSP = Trim(InD(counter).MAC.Replace(".", " "))  

            '-------------- THE VARIABLE MAPNP IS THE SMAE AS THE MAC ONLY THAT THERE IS NO SPACES
            InD(counter).MacNP = Trim(InD(counter).MAC.Replace(".", ""))
            InD(counter).Name = Reader.Item("Name")
            InD(counter).Protocol = Reader.Item("Protocol")
            InD(counter).Type = Reader.Item("Type")
            InD(counter).Dimmable = Reader.Item("Dimmable")
            InD(counter).Mastermapping = Reader.Item("Mastermapping")
            Reader.Read()
        Next
        DEBUG.PRINT ("FINISHED LOADING DEVICES")
    End Function

How I update my Devices Database via code

For my own fancy, Here is the code I use to update a device by #.  Like mentioned before when you call a INSTEON group which affects more than one device you dont get individual response from each item so I like to update the DB manually.

So I call another custom function that will update the device using the mac address, and pass the status, such as on or off. For example;

Lg_Update_Device(InD(20).MacNP, "OFF")

Public Function Lg_Update_Device(ByVal ID As String, ByVal value As String) As Boolean

        '------------------------ SETUP CONNECTION AND OTHER GOODIES BEFORE UPDATING ANYTHING
        Dim sqlConnection34 As New System.Data.SqlClient.SqlConnection(My.Settings.InsteonConnectionString)
        Dim cmd34 As New System.Data.SqlClient.SqlCommand
        cmd34.CommandType = System.Data.CommandType.Text
        cmd34.Connection = sqlConnection34

        '------ THIS IS THE ACTUAL SQL COMMAND TO UPDATE THE DEVICE USING THE MAC AS  THE KEY.      

        cmd34.CommandText = "UPDATE Devices SET status= '" + value + "', last_seen='" + Now().ToString + "' WHERE MAC='" + ID + "'"
        If sqlConnection34.State = ConnectionState.Open Then sqlConnection34.Close()
        sqlConnection34.Open()
        cmd34.ExecuteNonQuery()

        '---------------- CLOSE CONNECTION AND EXIT FUNCTION
        sqlConnection34.Close()
        Update_TimeStamp()

    End Function

My Sql database runs on the same box as the ‘server’ and is really fast, SQL caches information in memory so the more you have the better.  It looks like a lot of code but it flyes!.  Hopefully this example will help you on creating your own DB.

Cheers,