Announcement
Collapse
No announcement yet.
Reading from SQL Database to display contents of wine cellar.
Collapse
X
-
The last part is to add a bottle of wine to the database.
The code to do this is given below:
Code:'AddBottleRowCol.vb - Used to push wine information to database and populate the Wine inventory screen 'There are no parameters. All dvRefs are hard coded. '5/20/2020 - JLG IMPORTS System.Data.SQLite IMPORTS System.Core IMPORTS System.IO IMPORTS System.Net IMPORTS System.Data.Objects Sub Main(ByVal parms As Object) ' ' cs is the connection string that points to the desired database. ' Dim cs As String = "Data Source=/opt/HomeSeer/Data/WineCellar.db;Version=3;" Dim RackCol, RackRow As String Dim Wine As String Dim sWinery, sWineName, sVarietal, sYear, sCountry, sRegion, sReview As String ' 'Initialize all the data from the devices. ' sWinery = hs.DeviceString(792) sWineName = hs.DeviceString(793) sVarietal = hs.DeviceString(794) sYear = hs.DeviceString(795) sCountry = hs.DeviceString(796) sRegion = hs.DeviceString(797) sReview = hs.DeviceString(798) RackCol = hs.DeviceString(799) RackRow = hs.DeviceString(800) Wine = "UPDATE CellarContents SET Winery = '" & sWinery & "', WineName = '" & sWineName Wine = Wine & "', Varietal = '" & sVarietal & "', Year = " & sYear & ", Country = '" & sCountry Wine = Wine & "', Region = '" & sRegion & "', Review = '" & sReview Wine = Wine & "' WHERE Column = " & RackCol & " AND Row = " & RackRow hs.WriteLog("AddBtl", "RackCol = " & RackCol & " RackRow = " & RackRow & " SQL = " & Wine) Try 'Absolutely critical to use or debug messages will be cryptic Using con As New SqliteConnection(cs) If con.State = ConnectionState.Closed Then con.Open() 'open the database End if Using cmd As New SqliteCommand(con) 'create command cmd.CommandText = Wine cmd.ExecuteNonQuery() End Using con.Close() End Using Catch ex As Exception 'Error trapping hs.WriteLogEx("RowCol", ex.Message, "#ff0000") End Try End Sub
Code:'SetdbFields.vb 'The purpose of this routine is to move data from Keyboard Stack device to Specific Field Device in HSTouch 'The Routine is expecting one parameters with two pieces of data. The first is the field device, the second is the Source Device '5/20/2020 JLG IMPORTS System.Core IMPORTS System.IO IMPORTS System.Net IMPORTS System.Object Sub Main(ByVal parms As Object) Dim parm As String Dim sParm() As String Dim KbdStack As String Dim dvRefTgt As Integer Dim dvRefSrc As Integer If parms.GetType().ToString = "System.String" Then 'Called from an Event parm = parms.ToString() Else ' Called from HSTouch parm = parms(0).ToString() End If 'Now use parm as your input variable... sParm = Split(parm, "?", -1, 1) 'Split into array of parameters using "?" as the delimeter dvRefTgt = CInt(sParm(0)) 'Target Device dvRefSrc = CInt(sParm(1)) 'Source Device hs.WriteLog("SetdbF", parm) KbdStack = hs.DeviceString(dvRefSrc) 'Retrieve the Data off the stack device hs.SetDeviceString(dvRefTgt, KbdStack, True) 'Set the Device String End Sub
The following is a screenshot of how I programmed the elements. The first is to populate an invisible element with transparent text to hold the text to pass to a script. This was done to have only one invisible element.
The next step is to load the data to the device. This takes the Display text from above to have element text as a parameter. Note that HoldDevNo must have null text for this to work.
The last is to refresh the screen so all data appears.
I have been adding wine to the database and using the tablet to input data is relatively fast and effective - better than lugging a laptop around. Not to mention the heat buildup in the cellar.. To make finding a specific bin easier I am considering putting metal tags every 5 rows and columns. Always another project.
I hope others find value in this.
Leave a comment:
-
I have some more updates on the system. I added removal and addition of single bottles to allow better managment. I built a keyboard for hstouch and you can find it in the how section at this link.
https://forums.homeseer.com/forum/ho...rd-for-hstouch
Version 2 handles punctuation but not all. I still have to finish.
I ended up with more virtual devices than I like but so be it. I can view, remove and add bottles to the db from HSTouch. It also copies all removed bottles to a consumed table. I need to add a pictures blog so I can show pics of the wine as well. Software is never done.
Here is a screenshot for Adding and Removing Bottles of Wine.
Here is the code to change the column and row respectively
Code:'05/14/2020 ColumnCounter.vb 'Pass three parameters, The addition or subtraction, the refid to increase the value, and whether to print to HS Log IMPORTS System.IO IMPORTS System.Core IMPORTS System.Net Sub Main(ByVal parms As Object) 'There are 0 parameters Dim sParm() As String Dim parm, rName As String Dim dvRef, Inc As Integer Dim CntrValue As Integer Dim Debug As Boolean If parms.GetType().ToString = "System.String" Then 'Called from an Event parm = parms.ToString() Else ' Called from HSTouch parm = parms(0).ToString() & "|" & parms(1).ToString() & "|" & parms(2).ToString() End If 'Now use parm as your input variable... sParm = Split(parm, "|", -1, 1) Inc = CInt(sParm(0)) If Inc = 2 Then Inc = -1 End If dvRef = CInt(sParm(1)) Debug = sParm(1) rName = "CtlCntr" If Debug Then hs.WriteLog(rName, parm) End If CntrValue = hs.DeviceValueEx(dvRef) CntrValue = CntrValue + Inc If CntrValue > 31 Then CntrValue = 1 End If If CntrValue <= 0 Then CntrValue = 31 End If hs.SetDeviceValueByRef(dvRef, CntrValue, True) End Sub
Code:'05/18/2020 RowCounter.vb 'Pass three parameters, The addition or subtraction, the refid to increase the value, and whether to print to HS Log IMPORTS System.IO IMPORTS System.Core IMPORTS System.Net Sub Main(ByVal parms As Object) 'There are 0 parameters Dim sParm() As String Dim parm, rName As String Dim dvRef, Inc As Integer Dim RowValue As Integer Dim Debug As Boolean If parms.GetType().ToString = "System.String" Then 'Called from an Event parm = parms.ToString() Else ' Called from HSTouch parm = parms(0).ToString() & "|" & parms(1).ToString() & "|" & parms(2).ToString() End If 'Now use parm as your input variable... sParm = Split(parm, "|", -1, 1) Inc = CInt(sParm(0)) If Inc = 2 Then Inc = -1 End If dvRef = CInt(sParm(1)) Debug = sParm(1) rName = "RowCntr" If Debug Then hs.WriteLog(rName, parm) End If RowValue = hs.DeviceValueEx(dvRef) RowValue = RowValue + Inc If RowValue > 23 Then RowValue = 1 End If If RowValue <= 0 Then RowValue = 23 End If hs.SetDeviceValueByRef(dvRef, RowValue, True) End Sub
The up and down arrows also call the following script to populate the display string.
Code:'PopulateWineRowCol.vb - Used to pull wine information from database and populate the Removal screen '5/15/2020 - JLG IMPORTS System.Data.SQLite IMPORTS System.Core IMPORTS System.IO IMPORTS System.Net IMPORTS System.Data.Objects Sub Main(ByVal parms As Object) ' ' cs is the connection String that points to the desired database. ' Dim cs As String = "Data Source=/opt/HomeSeer/Data/WineCellar.db;Version=3;" Dim RackCol, RackRow As String Dim rdr As SqliteDataReader Dim i As Integer Dim Wine As String Dim sWinery As String ' 'Get the Column and Row number in rack. ' RackCol = CStr(hs.DeviceValue(787)) RackRow = CStr(hs.DeviceValue(789)) 'hs.WriteLog("RowCol", "RackCol = " & RackCol & " RackRow = " & RackRow) Try 'Absolutely critical to use or debug messages will be cryptic Using con As New SqliteConnection(cs) if con.State = ConnectionState.Closed Then con.Open() 'open the database End if Using cmd As New SqliteCommand(con) 'create command cmd.CommandText = "SELECT * FROM CellarContents WHERE Column = " & RackCol & " AND Row = " & RackRow & " ORDER BY Row ASC" 'hs.WriteLog("RackCol", cmd.CommandText) rdr = cmd.ExecuteReader() Using rdr While (rdr.Read()) 'Must use the while even though it is a single record. sWinery = rdr("Winery") If sWinery = "N/A" Then Wine = "." ElseIf sWinery = "Empty" Then Wine = "Available" Else Wine = sWinery & " " & CStr(rdr("Year")) & " " & rdr("WineName") & " " & rdr("Varietal") & " " & rdr("Country") & " " & rdr("Region") End If End While End Using End Using con.Close() End Using Catch ex As Exception 'Error trapping hs.WriteLogEx("RowCol", ex.Message, "#ff0000") End Try hs.SetDeviceString(790, Wine, True) End Sub
The code to remove the selected bottle is:
Code:'PopulateWineConsumed.vb - Used to pull wine information from database and populate the Wine inventory screen '5/15/2020 - JLG IMPORTS System.Data.SQLite IMPORTS System.Core IMPORTS System.IO IMPORTS System.Net IMPORTS System.Data.Objects Sub Main(ByVal parms As Object) ' ' cs is the connection String that points to the desired database. ' Dim cs As String = "Data Source=/opt/HomeSeer/Data/WineCellar.db;Version=3;" Dim RackCol, RackRow As String Dim rdr As SqliteDataReader Dim i, iYear As Integer Dim Wine As String Dim sWinery,sWineName,sVarietal,sCountry,sRegion,sReview As String Dim sp As String = " " Dim myNow As DateTime = DateTime.Now Dim TimeFmt As String = "MM/dd/yyyy" ' 'Get the Column and Row number in rack. ' RackCol = CStr(hs.DeviceValue(787)) RackRow = CStr(hs.DeviceValue(789)) 'hs.WriteLog("Consum", "RackCol = " & RackCol & " RackRow = " & RackRow) Try 'Absolutely critical to use or debug messages will be cryptic Using con As New SqliteConnection(cs) if con.State = ConnectionState.Closed Then con.Open() 'open the database End if ' 'Retrieve the data for the row and column ' Using cmd As New SqliteCommand(con) 'create command cmd.CommandText = "SELECT * FROM CellarContents WHERE Column = " & RackCol & " AND Row = " & RackRow & " ORDER BY Row ASC" 'hs.WriteLog("RackCol", cmd.CommandText) rdr = cmd.ExecuteReader() Using rdr While (rdr.Read()) 'Must use the while even though it is a single record. 'hs.WriteLog("RackCol", "Inside While") sWinery = rdr("Winery") sWineName = rdr("WineName") sVarietal = rdr("Varietal") iYear = rdr("Year") sCountry = rdr("Country") sRegion = rdr("Region") sReview = rdr("Review") 'hs.WriteLog("RackCol", sWinery & sp & sWineName & sp & sVarietal & sp & cstr(iYear) & sp & sCountry & sp & sRegion & sp & sReview) End While End Using 'rdr End Using 'cmd as new ' 'Write the data for the row and column ' Using cmd1 As New SqliteCommand(con) 'create command cmd1.CommandText = "UPDATE CellarContents SET Winery = 'Empty', WineName = '.', Varietal = '.', Year = 0, Country = '.', Region = '.', Review = 'Review' WHERE Column = " & RackCol & " AND Row = " & RackRow 'hs.WriteLog("RackCol", cmd1.CommandText) cmd1.ExecuteNonQuery() End Using 'cmd1 As New ' 'Write the data for the row and column ' Using cmd2 As New SqliteCommand(con) 'create command cmd2.CommandText = "INSERT INTO WineConsumed (cDate, cWinery, cWineName, cVarietal, cYear, cCountry, cRegion, cReview) VALUES ('" & myNow.ToString(TimeFmt) & "','" & sWinery & "','" & sWineName & "','" & sVarietal & "','" & Cstr(iYear) & "','" & sCountry & "','" & sRegion & "','" & sReview & "')" 'hs.WriteLog("RackCol", cmd2.CommandText) cmd2.ExecuteNonQuery() End Using 'cmd2 As New con.Close() End Using 'con as new sqlite Catch ex As Exception 'Error trapping hs.WriteLogEx("RowCol", ex.Message, "#ff0000") End Try If sWinery = "N/A" Then Wine = "." ElseIf sWinery = "Empty" Then Wine = "Available" Else Wine = sWinery & sp & CStr(iYear) & sp & sWineName & sp & sVarietal & sp & sCountry & sp & sRegion End If hs.SetDeviceString(790, Wine, True) End Sub
I will discuss the add in the next post.
Regards
Leave a comment:
-
Very nice!! Just spent the last hour picking our where to build by cellar and reviewing online review/inventory sites. Another rabbit hole to go down...
John
Leave a comment:
-
My apologies, I forgot to thank Jon00 and Zwolfpack for all of their help.
Leave a comment:
-
Reading from SQL Database to display contents of wine cellar.
I said I would post when I had the system cleaner and working. My cellar is set up with 31 bin columns and 23 rows. Here is a partial picture. To answer the question - Yes I built and installed everything in the room with the help of my wife. The walls are 2x6 and are high density spray foam. The cellar will hold 689 bottles maximum. It is in the basement and it is not maintained by AC. Humidity is manual as well but terracotta pots hold the humidity to 60-70% with little work.
There are step downs in a couple areas to fit under a beam. These areas are represent by "." Empty bins are shown as available. This is still a work in progress as I have to finish populating the database and then do remove and add bottles screen. I also have to copy reviews into the database. I will post to this thread as I progress.
The code to change the bin column and row follow. First to select the column.
Code:05/14/2020 ControlCounter.vb 'Pass three parameters, The addition or subtraction, the refid to increase the value, and weather to print to HS Log IMPORTS System.IO IMPORTS System.Core IMPORTS System.Net Sub Main(ByVal parms As Object) 'There are 0 parameters Dim sParm() As String Dim parm, rName As String Dim dvRef, Inc As Integer Dim CntrValue As Integer Dim Debug As Boolean If parms.GetType().ToString = "System.String" Then 'Called from an Event parm = parms.ToString() Else ' Called from HSTouch parm = parms(0).ToString() & "|" & parms(1).ToString() & "|" & parms(2).ToString() End If 'Now use parm as your input variable... sParm = Split(parm, "|", -1, 1) Inc = CInt(sParm(0)) If Inc = 2 Then Inc = -1 End If dvRef = CInt(sParm(1)) Debug = sParm(1) rName = "CtlCntr" If Debug Then hs.WriteLog(rName, parm) End If CntrValue = hs.DeviceValueEx(dvRef) CntrValue = CntrValue + Inc If CntrValue > 31 Then CntrValue = 1 End If If CntrValue <= 0 Then CntrValue = 31 End If hs.SetDeviceValueByRef(dvRef, CntrValue, True) End Sub
Code:'PopulateWineCellar.vb - Used to pull wine information from database and populate the Wine inventory screen '5/15/2020 - JLG IMPORTS System.Data.SQLite IMPORTS System.Core IMPORTS System.IO IMPORTS System.Net IMPORTS System.Data.Objects Sub Main(ByVal parms As Object) ' ' cs is the connection String that points to the desired database. ' Dim cs As String = "Data Source=/opt/HomeSeer/Data/WineCellar.db;Version=3;" Dim RackCol As String Dim parm As String Dim Debug As Boolean Dim rdr As SqliteDataReader Dim dvRef(23), i As Integer Dim Wine(23) As String Dim sWinery As String ' 'Set up the 23 rows Device Refernce ID ' dvRef(0) = 787 'Device which holds the column count. ' 'I created all of my devices that hold the row in the wine rack at the same time and I luckly got them continuous. ' For i = 1 to 23 dvRef(i) = 763 + i Next ' 'Get the column number in rack. ' RackCol = CStr(hs.DeviceValue(dvRef(0))) Try 'Absolutely critical to use or debug messages will be cryptic Using con As New SqliteConnection(cs) if con.State = ConnectionState.Closed Then con.Open() 'open the database End if Using cmd As New SqliteCommand(con) 'create command cmd.CommandText = "SELECT * FROM CellarContents WHERE Column = " & RackCol & " ORDER BY Row ASC" rdr = cmd.ExecuteReader() i = 0 Using rdr While (rdr.Read()) 'Concatenate the fields into a single string i = i + 1 sWinery = rdr("Winery") If sWinery = "N/A" Then Wine(i) = "." ElseIf sWinery = "Empty" Then Wine(i) = "Available " Else Wine(i)= sWinery & " " & CStr(rdr("Year")) & " " & rdr("WineName") & " " & rdr("Varietal") & " " & rdr("Country") & " " & rdr("Region") End If End While End Using End Using con.Close() End Using Catch ex As Exception 'Error trapping hs.WriteLogEx("Wine", ex.Message, "#ff0000") End Try For i = 1 to 23 hs.SetDeviceString(dvRef(i), Wine(i), True) Next End Sub
[ATTACH]n1386286[/ATTACH]
Here is what the HSTouch Screen looks like
The up and down buttons do the heavy lifting. They the two scripts above. I will ultimately be able to push the bin number and a proper review will be placed where it now says review.
I hope this helps.
Tags: None
- Likes 1
Leave a comment: