Simple Sorting Of Values

[mrhappy]

All,

I have what I thought would be a simple sort operation, i've done them and used them before but cannot for the life of me work out why this one does not work

Here is the code;

        Dim MinVal4 As Decimal = 15
        Dim MaxVal4 As Decimal = 15

        While SQLreader.Read()
            
            hs.writelog("Test", SQLreader(0))

            If MinVal4 > CDec(SQLreader(0)) Then MinVal4 = CDec(SQLreader(0))
            If MaxVal4 < CDec(SQLreader(0)) Then MaxVal4 = CDec(SQLreader(0))

        End While

        Call WriteMessage("Sensor 4 Min: " & MinVal4, MSG_DEBUG)
        Call WriteMessage("Sensor 4 Max: " & MaxVal4, MSG_DEBUG)

Here is the result;

29/04/2011 21:08:19  Test  21.12 
29/04/2011 21:08:19  Test  21.12 
29/04/2011 21:08:19  Test  21.12 
29/04/2011 21:08:19  Test  21.12 
29/04/2011 21:08:19  Test  21.25 
29/04/2011 21:08:19  Test  21.25 
29/04/2011 21:08:19  Test  21.18 
29/04/2011 21:08:19  Test  21.31 
29/04/2011 21:08:19  Test  21.43 
29/04/2011 21:08:19  Test  21.5 
29/04/2011 21:08:19  Test  21.37 
29/04/2011 21:08:19  Test  21.56 
29/04/2011 21:08:19  Test  21.5 
29/04/2011 21:08:19  Test  21.43 
29/04/2011 21:08:19  Test  21.37 
29/04/2011 21:08:19  Test  21.37 
29/04/2011 21:08:19  Test  21.37 
29/04/2011 21:08:19  Test  21.18 
29/04/2011 21:08:19  Test  21.06 
29/04/2011 21:08:19  Test  21.12 
29/04/2011 21:08:19  Test  21.18 
29/04/2011 21:08:19  Test  21.06 
29/04/2011 21:08:19  Test  21.12 
29/04/2011 21:08:19  Test  21.18 
29/04/2011 21:08:19  Test  21 
29/04/2011 21:08:19  DS1820 (3P) Debug  Sensor 4 Min: 15 
29/04/2011 21:08:19  DS1820 (3P) Debug  Sensor 4 Max: 21.56 

No matter what I try I cannot get the lowest value out of the list - highest value not a problem, but cannot get the lowest (should be 21 in this example), I set the values for MinVal4 and MaxVal4 just to test it and if I change MinVal4 in the declarations then it appears in the debug statements - its like its not even checking through the records for the smallest. If I change it to 0 in the declarations then I get 0 for the minimum.

Any help gratefully received...

[rmasonjr]

Can you add an "order by" on your SQL statement?

[mrhappy]

I would normally, but this is to eventually show the series of values in a graph of temp vs. time so the order is quite important. I really can't work out why its doing this as I have virtually exactly the same code (just the query is different) in another plugin and it works fine.

[rmasonjr]

I'm wondering - is the column returned from the database a true decimal number or is it just a string? The reason I ask, if you are using a SqlDataReader, then there is a method called getDecimal() which will return that type from the database.

[mrhappy]

I'm wondering - is the column returned from the database a true decimal number or is it just a string? The reason I ask, if you are using a SqlDataReader, then there is a method called getDecimal() which will return that type from the database.

It's SQLite which only has a couple of data types, the one i'm using is 'REAL' which i'm not sure there is a vb.net equivalent. You may be onto something though thanks, previously when I used that code I was'nt working with decimals, probably long if not integers...i'll change the data type and see if that changes anything as i've noticed that there is also a type in SQLite of 'Numeric'.

[rmasonjr]

Cool - I've been wanting to mess around with SQLite since I heard the next version of HS would be using it.

[Michael McSharry]

I suspect that you need to convert the database data to a calendar date using the functions available with SQLite. This was the biggest change I had to deal with when putting SQLite into mcsSprinklers. Google is your buddy for stuff like this.

Never mind on the above as I see you just working with numbers and not dates. In your case you need to initialize your min value to something larger than your database values. You currently have it initialized to a value lower than any database entry.

[mrhappy]

I suspect that you need to convert the database data to a calendar date using the functions available with SQLite. This was the biggest change I had to deal with when putting SQLite into mcsSprinklers. Google is your buddy for stuff like this.

Never mind on the above as I see you just working with numbers and not dates. In your case you need to initialize your min value to something larger than your database values. You currently have it initialized to a value lower than any database entry.

That solved it thanks, set minval to 1000 and it works as it should...

Cool - I've been wanting to mess around with SQLite since I heard the next version of HS would be using it.

Personally found it very easy to use and simple enough for me to not get confused by it, none of the messing round with connection strings, datasets, dataadapters etc for access databases...highly recommend it.

[tenholde]

none of the messing round with connection strings, datasets, dataadapters etc for access databases...highly recommend it.

There is a very good .net module that let you use the breadth of .net capability with SqLIte (which means datasets, dataadapters, etc.)

tenholde

[jasv]

There is a very good .net module that let you use the breadth of .net capability with SqLIte (which means datasets, dataadapters, etc.)

tenholde

Would you mind posting where you found the module? Or is there only one? I googled and thought I saw 2 versions of the system.data.SQLite dll. I was confused because I thought one of the examples showed connections strings, etc.

[Michael McSharry]

You still need sqlite3, but you can put a .net wrapper around it. In my case I use mono.data.sqlite so I can work in mono as well as .net. I dont recall where I got mine, but it is in the installer for mcsSprinklers V2.11.

[mrhappy]

I think this one here http://sqlite.phxsoftware.com/ was the one I used, then placed the system.data.sqlite.dll file in the main HomeSeer directory and it was good to go, the basic vb.net stuff was from here http://www.kirupa.com/net/sqllite_vb_pg1.htm

[jasv]

Thank you both.

[drozwood90]

For the amounts and types of data we collect with our home automation systems, what is the advantage to going through the extra effort to getting SQL installed? Is it really worth it?

I'm guessing if I ever get to putting in all the 1-wire sensors I want it will make more sense? That is log each room once every 5 min each day. Then once a month take that data and make it a once every 1 hour sample or something like that...?

Do you track door status...light level, etc.? Or is it just easier to do graphs straight from sql?

--Dan

[jasv]

For me it would be a replacement of MS access in my plugins. SQLite is faster and more flexible for the future.

And if I understand SQLite there is no installation. You would just include a dll with the plugin.

My plugins track alarm faults, motion, and other info for history, research etc. Not so much for graphing.